Airbnb: Friday and Saturday Check-Ins per City

Easy
553.

Airbnb: Friday and Saturday Check-Ins per City

Airbnb's local operations team schedules check-in support by day of the week, and wants to know how weekend-heavy each city is.

Considering only reservations that were not cancelled, return for every city city, check_ins (its number of such reservations) and weekend_check_ins (how many of them check in on a Friday or a Saturday). Cities with no non-cancelled reservation do not appear. Return the rows in any order.

A practice scenario set at Airbnb: the tables and data are invented, and this is not a question from, or endorsed by, Airbnb.

Tables

Table: Reservation

ColumnTypeValues
reservation_idint
cityvarchar
check_indate
nightsint
statusenumconfirmed, completed, cancelled

Primary key: reservation_id.

One row per reservation; city is the listing's city.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Reservation

reservation_idcitycheck_innightsstatus
1Lisbon2025-05-022completed
2Lisbon2025-05-031completed
3Lisbon2025-05-043confirmed
4Kyoto2025-05-094cancelled
5Kyoto2025-05-082confirmed
6Goa2025-05-105cancelled

Output

citycheck_insweekend_check_ins
Kyoto10
Lisbon32
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Airbnb: Friday and Saturday Check-Ins per City — SQL Problem

Airbnb's local operations team schedules check-in support by day of the week, and wants to know how weekend-heavy each city is.

  • Difficulty: Easy
  • Topics: Dates, Conditional Logic, Aggregation
  • Dialect: MySQL
  • Problem: #553

Problem statement

Airbnb's local operations team schedules check-in support by day of the week, and wants to know how weekend-heavy each city is.

Considering only reservations that were not cancelled, return for every city city, check_ins (its number of such reservations) and weekend_check_ins (how many of them check in on a Friday or a Saturday). Cities with no non-cancelled reservation do not appear. Return the rows in any order.

A practice scenario set at Airbnb: the tables and data are invented, and this is not a question from, or endorsed by, Airbnb.

Tables

Table: Reservation

ColumnType
reservation_idint
cityvarchar
check_indate
nightsint
statusenum(confirmed, completed, cancelled)

Primary key: reservation_id.

One row per reservation; city is the listing's city.

Examples

Example 1

Reservation

reservation_idcitycheck_innightsstatus
1Lisbon2025-05-022completed
2Lisbon2025-05-031completed
3Lisbon2025-05-043confirmed
4Kyoto2025-05-094cancelled
5Kyoto2025-05-082confirmed
6Goa2025-05-105cancelled

Output

citycheck_insweekend_check_ins
Kyoto10
Lisbon32

How to solve Airbnb: Friday and Saturday Check-Ins per City

This is conditional aggregation: one group per city, two counts per group. COUNT(*) counts every non-cancelled reservation; the weekend count adds 1 for each row whose check-in falls on a Friday or Saturday and 0 otherwise.

The day test is where people slip. MySQL's DAYOFWEEK runs Sunday = 1 … Saturday = 7, so Friday and Saturday are 6 and 7. WEEKDAY runs Monday = 0 … Sunday = 6, so the same days are 4 and 5 — one alternative uses that, summing the boolean directly since MySQL treats a true comparison as 1. DAYNAME gives the English name, which reads most clearly. Mixing up the two numbering schemes is the usual bug.

Cancelled reservations are removed before grouping, so they affect neither count, and a city left with nothing never forms a group. A city with check-ins but none at the weekend still appears, with weekend_check_ins 0 — the ELSE 0 guarantees a number rather than NULL. One scan of the table.

Reference solution (MySQL)

SELECT city,
       COUNT(*) AS check_ins,
       SUM(CASE WHEN DAYOFWEEK(check_in) IN (6, 7) THEN 1 ELSE 0 END) AS weekend_check_ins
FROM Reservation
WHERE status <> 'cancelled'
GROUP BY city

Another way

SELECT city, COUNT(*) AS check_ins, SUM(WEEKDAY(check_in) IN (4, 5)) AS weekend_check_ins FROM Reservation WHERE status IN ('confirmed', 'completed') GROUP BY city

Another way

SELECT city, COUNT(*) AS check_ins, COUNT(CASE WHEN DAYNAME(check_in) IN ('Friday', 'Saturday') THEN 1 END) AS weekend_check_ins FROM Reservation WHERE status <> 'cancelled' GROUP BY city

← Airbnb: Experience Bookings and Guests per Category · Airbnb: Host Payout for Each Completed Stay →