- ******************************************************************************************.
- *********************************
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 (
SELECT
B.date,
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
)
SELECT
D.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;
Explanation
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