118. Daily bookings in the US

hard airbnb

  • ******************************************************************************************.
  • *********************************

Table 1: bookings

When someone makes a reservation on Airbnb.

  col_name    | col_type
date          | date
booking_id    | bigint
listing_id    | bigint

Table 2: dates

Calendar dates from 01/01/2019 to 12/31/2025.

 col_name | col_type
 year     | smallint
 month    | smallint
 date     | date

Table 3: listings

When a new Airbnb listing is created in a country ('US', 'UK', 'JP', 'CA', 'AU', 'DE')

  col_name    | col_type
listing_id    | bigint
country       | varchar(2)
created_dt    | date

Sample results

    date    | count
 2021-07-01 |    1
 2021-07-02 |    2
 2021-07-03 |    3
 2021-07-04 |    0
 2021-07-05 |    5

Solution postgres

WITH us_bookings AS (
        COUNT(booking_id) AS num_bookings
    FROM bookings B	
    INNER JOIN listings L
    ON L.listing_id = B.listing_id
    AND L.country = 'US'	
    GROUP BY date	
    CASE WHEN B.num_bookings IS NULL THEN 0 ELSE B.num_bookings END AS num_bookings
FROM dates D
LEFT JOIN us_bookings B
ON B.date = D.date
WHERE D.date >= '2021-07-01'
AND D.date < '2021-08-01'
ORDER BY D.date;


This query is designed to count the number of bookings made in US listings for each date within the specified time range. It accomplishes this by first selecting all bookings made in US listings and grouping them by date. This subquery is named "us_bookings".

The main query then selects all dates within the specified time range and left joins them with the "us_bookings" subquery on the date field. This is done to ensure that all dates within the specified range are included in the result, even if there were no bookings made on that date.

The final select statement returns the date and the number of bookings made on that date, with a case statement to handle cases where there were no bookings made on a particular date.

The result is ordered by date.

Expected results

More Airbnb questions

ID Title Level FTPR
119 Top 2 countries by bookings airbnb hard
120 First ever booking airbnb hard
121 Week over week change of first ever bookings airbnb hard
122 Top country by wow growth airbnb hard
123 Top listing in the United States, United Kingdom and Canada airbnb medium