- *******************************************************************
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 | country | bookings | ranking
------------+---------+----------+---------
2021-08-01 | US | 125 | 1
2021-08-01 | UK | 50 | 2
2021-08-02 | US | 43 | 1
2021-08-02 | UK | 22 | 2
Solution postgres
WITH daily_bookings_by_country AS (
SELECT D.date, L.country, COUNT(B.booking_id) AS bookings
FROM dates D
LEFT JOIN bookings B
ON B.date = D.date
LEFT JOIN listings L
ON L.listing_id = B.listing_id
WHERE D.date >= '2021-08-01'
AND D.date < '2021-09-01'
GROUP BY D.date, L.country
)
SELECT date, country, bookings, ranking FROM (
SELECT date,
country,
bookings,
ROW_NUMBER() OVER (PARTITION BY date ORDER BY bookings DESC) AS ranking
FROM daily_bookings_by_country
) X
WHERE ranking <=2 ;
Explanation
This query is used to retrieve the daily bookings made by country, and ranks the countries by the number of bookings made. It starts by creating a common table expression (CTE) called daily_bookings_by_country which joins the bookings and listings tables with the dates table on the date field. It then groups the results by date and country, and counts the number of bookings made on each date and country combination.
The second part of the query selects the date, country, bookings, and ranking from the daily_bookings_by_country table. It uses the ROW_NUMBER() function to assign a rank to each country within each date based on the number of bookings made. The PARTITION BY clause tells the function to rank the countries separately for each date, and the ORDER BY clause specifies that the rankings should be based on the number of bookings in descending order.
Finally, the outer query filters the results to only show the top two ranked countries for each date. This is done by selecting only the rows where the ranking is less than or equal to 2.
Expected results