SQLPad
Sign In

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
FROM (
    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;

Explanation

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
5%
119 Top 2 countries by bookings airbnb hard
10%
120 First ever booking airbnb hard
10%
121 Week over week change of first ever bookings airbnb hard
14%
122 Top country by wow growth airbnb hard
13%