119. Top 2 countries by bookings

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    | 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,
            ROW_NUMBER() OVER (PARTITION BY date ORDER BY bookings DESC) AS ranking
     FROM daily_bookings_by_country
) X
WHERE ranking <=2 ;


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

More Airbnb questions

ID Title Level FTPR
118 Daily bookings in the US 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