logo

Back to questions

Average Vacant Days

Hard

The strategy team in Airbnb is trying to analyze the impact of Covid-19 during 2021. To do so, they need you to write a query that outputs the average vacant days across the AirBnbs in 2021. Some properties have gone out of business, so you should only analyze rentals that are currently active. Round the results to a whole number.

Assumptions:

  1. field equals to 1 when the property is active, and 0 otherwise.
  2. In cases where the check-in or check-out date is in another year other than 2021, limit the calculation to the beginning or end of the year 2021 respectively.
  3. Listing can be active even if there are no bookings throughout the year.

Table:

Column NameType
listing_idinteger
checkin_datedate
checkout_datedate

Example Input:

listing_idcheckin_datecheckout_date
108/17/2021 00:00:0008/19/2021 00:00:00
108/19/2021 00:00:0008/25/2021 00:00:00
208/19/2021 00:00:0009/22/2021 00:00:00
312/23/2021 00:00:0001/05/2022 00:00:00

Table:

Column NameType
listing_idinteger
is_activeinteger

Example Input:

listing_idis_active
11
20
31

Example Output:

avg_vacant_days
357

Explanation:

  1. Property 1 was rented for 8 days, thus the property has 365 - 8 = 357 vacant days.
  2. Property 2 is excluded as it is not active.
  3. Property 3 was rented out for 12 days, thus the property as 365 - 12 = 353 vacant days.

Average vacant days are 355 days. (357 + 353 / 2)

PostgreSQL 14

Refer friends to get bonus content & cool prizes.