123. Top listing in the United States, United Kingdom and Canada

medium 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: 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

 country | listing_id | ranking
 CA      |    1005000 |       1
 US      |    1000083 |       1
 UK      |    1002985 |       1

Solution postgres

WITH bookings_country AS (
    SELECT country, L.listing_id,   COUNT(booking_id) num_bookings
    FROM bookings B
    INNER JOIN listings L
    ON L.listing_id = B.listing_id
    WHERE B.date  >= '2021-08-01'
    AND   B.date  <= '2021-08-07'
    GROUP BY country, L.listing_id

SELECT country, listing_id, ranking
    SELECT country, listing_id, num_bookings, ROW_NUMBER() OVER(PARTITION BY country ORDER BY num_bookings DESC) ranking
    FROM bookings_country
) X
WHERE country in ('CA', 'UK', 'US')
AND X.ranking =1
ORDER BY country, ranking;


This query is designed to retrieve information on the top-performing listings in specific countries for a given time period.

The first part of the query creates a temporary table called "bookings_country" which combines data from the "bookings" and "listings" tables, joining them on the "listing_id" column. It then filters the results to only include bookings made between August 1st and August 7th of 2021. The table is grouped by country and listing_id, and a count of the number of bookings for each listing in each country is calculated and labeled "num_bookings".

In the second part of the query, the temporary table is used to generate a new table called "X". This table includes the country, listing_id, num_bookings, and a ranking based on the number of bookings for each listing within its respective country. The "ROW_NUMBER()" function is used to assign a rank to each listing based on the number of bookings it received within its country, with the highest performing listing receiving rank 1.

Finally, the results are filtered to only include listings from the countries specified in the "WHERE" clause (Canada, the United Kingdom, and the United States) and where the ranking is equal to 1. The

Expected results

More Airbnb questions

ID Title Level FTPR
118 Daily bookings in the US airbnb hard
119 Top 2 countries by bookings 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