122. Top country by wow growth

hard airbnb

  • *****************************************121.
  • **********************************************************************************************************************************************************************************************.
  • ******************************************************************************************************************************************************.
  • **************** ***************, ***************, ..., ***************.

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 
 2021-08-01 | DE
 2021-08-02 | CA
 2021-08-03 | ES

Solution postgres

WITH nth_bookings AS (
	SELECT B.date, L.listing_id, B.booking_id, country, ROW_NUMBER() OVER (PARTITION BY B.listing_id  ORDER BY B.date) AS nth_booking
	FROM listings L
	INNER JOIN bookings B
	ON L.listing_id = B.listing_id
   first_bookings AS (
    SELECT * FROM nth_bookings
    WHERE nth_booking = 1

  first_bookings_by_country AS (
	SELECT date, country, COUNT(DISTINCT listing_id) AS weekly_first_bookings
	FROM first_bookings
	WHERE date >= '2021-07-25'
	AND date <= '2021-08-07'
wow_growth AS (
	SELECT B1.date, B1.country, B1.weekly_first_bookings * 100.0/ B2.weekly_first_bookings - 100.0 AS wow_growth
	FROM first_bookings_by_country B1
	INNER JOIN first_bookings_by_country B2
	ON B1.country= B2.country
	AND B1.date = B2.date  + interval '7' day 
growth_ranking AS (
	SELECT date, country, RANK()OVER(PARTITION BY date ORDER BY wow_growth DESC) ranking
	FROM wow_growth
SELECT date, country
FROM growth_ranking
WHERE ranking =1;


This query is calculating the week-over-week growth rate of the number of first bookings made on each listing in a particular country. It does this by first joining the listings and bookings tables to create a table of all bookings for each listing, with a row number indicating the order in which they were made. It then selects only the first booking for each listing and creates a table counting the number of first bookings made each week for each country. It then calculates the week-over-week growth rate for each country, and ranks them by the highest growth rate each week. Finally, it selects only the countries with the highest growth rate each week.

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
123 Top listing in the United States, United Kingdom and Canada airbnb medium