- *****************************************120.
- ***********************************************************************************************
- *********************************************************************************************************************************************************************************
- *******************************************
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 | wow_change
------------+------------------------
2021-08-01 | NULL
2021-08-02 | NULL
2021-08-03 | NULL
2021-08-04 | NULL
2021-08-05 | NULL
2021-08-06 | NULL
2021-08-07 | NULL
2021-08-08 | -32.7586206896551724
2021-08-09 | -18.1818181818181818
2021-08-10 | 0.00000000000000000000
2021-08-11 | -29.6296296296296296
2021-08-12 | 71.4285714285714286
Solution 1: postgres
WITH nth_bookings AS (
SELECT D.date, booking_id, ROW_NUMBER() 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
),
daily_first_booking AS (
SELECT date, COUNT(*) AS first_bookings
FROM first_booking
GROUP BY date
),
wow_change AS (
SELECT date, (first_bookings - LAG(first_bookings, 7) OVER()) * 100.0/ (LAG(first_bookings, 7) OVER()) AS wow_change
FROM daily_first_booking
)
SELECT * FROM wow_change
WHERE date >= '2021-08-01'
AND date <= '2021-08-31'
ORDER BY date
;
Explanation
This query is designed to calculate the week-over-week change in the number of first bookings for a set of listings. It does so by first creating a temporary table called "nth_bookings" that assigns a sequential number to each booking for each listing based on the date of the booking. It then selects the first booking for each listing using a temporary table called "first_booking." Using the "first_booking" table, it creates another temporary table called "daily_first_booking" that counts the number of first bookings for each date. Finally, it calculates the week-over-week change in first bookings using a temporary table called "wow_change," which uses the LAG function to calculate the difference between the current day's first bookings and the first bookings from 7 days ago, divided by the first bookings from 7 days ago. The query then selects the results from the "wow_change" table for the month of August 2021 and orders them by date.
Solution 2: postgres
-- SELF JOIN solution, which might be easier to read
WITH nth_bookings AS (
SELECT date, listing_id, booking_id, ROW_NUMBER() OVER (PARTITION BY listing_id ORDER BY date) AS nth_booking
FROM bookings
),
first_booking AS (
SELECT * FROM nth_bookings
WHERE nth_booking = 1
),
daily_first_booking AS (
SELECT date, COUNT(DISTINCT listing_id) AS first_bookings
FROM first_booking
GROUP BY date
),
wow_growth AS (
SELECT B1.date, B1.first_bookings * 100.0/B2.first_bookings - 100.0 AS wow_growth
FROM daily_first_booking B1
INNER JOIN daily_first_booking B2
ON B1.date = B2.date + interval '7' day
ORDER BY 1
)
SELECT * FROM wow_growth
WHERE date >= '2021-08-01'
AND date <= '2021-08-31'
ORDER BY 1;
Explanation
This query is used to calculate the week-over-week growth rate of the number of first bookings made on a platform. It does this by first creating a CTE (common table expression) called "nth_bookings" which assigns a row number to each booking made for each listing, ordered by date.
The next CTE, "first_booking", selects only the first booking made for each listing.
The third CTE, "daily_first_booking", aggregates the number of first bookings made each day across all listings.
Finally, the "wow_growth" CTE calculates the week-over-week growth rate by joining the "daily_first_booking" CTE to itself on dates that are one week apart, and calculating the percentage change in the number of first bookings.
The SELECT statement at the end of the query selects only the rows where the date falls within a certain range, and orders the results by date.
Expected results