- ***********************************************************
- ***************************************************************************************************
- ***********************************************************************
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
Sample results
date | count
------------+-------
2021-08-01 | 297
2021-08-02 | 63
2021-08-03 | 59
2021-08-04 | 87
2021-08-05 | 48
2021-08-06 | 53
Solution postgres
WITH nth_bookings AS (
SELECT
D.date,
booking_id,
listing_id,
RANK() OVER (PARTITION BY listing_id ORDER BY D.date) AS nth_booking
FROM dates D
LEFT JOIN bookings B
ON B.date = D.date
),
first_booking AS (
SELECT * FROM nth_bookings
WHERE nth_booking = 1
AND date >= '2021-08-01'
AND date < '2021-09-01'
)
SELECT date, COUNT(DISTINCT listing_id)
FROM first_booking
GROUP BY date
;
Explanation
This query is designed to count the number of unique listings that received their first booking within a certain time period.
The query starts by creating a temporary table called "nth_bookings" that joins the "dates" and "bookings" tables on the date field. It uses the RANK() function to assign a rank to each booking for each listing based on the date of the booking.
Next, the query creates another temporary table called "first_booking" that filters the results of "nth_bookings" to only include the first booking for each listing that falls within the specified date range.
Finally, the query selects the date and counts the number of unique listing_ids for each date in "first_booking" and groups the results by date.
In summary, this query is useful for analyzing the number of unique listings that received their first booking within a certain time period.
Expected results