Average Vacant Days
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.
|1||08/17/2021 00:00:00||08/19/2021 00:00:00|
|1||08/19/2021 00:00:00||08/25/2021 00:00:00|
|2||08/19/2021 00:00:00||09/22/2021 00:00:00|
|3||12/23/2021 00:00:00||01/05/2022 00:00:00|
Average vacant days are 355 days. (357 + 353 / 2)
Step 1 As the first step, let's only look at the properties that are currently active. To do so, join the table with the table and filter rows where the field equals 1. We use as there might be cases where property exists but there are no rental transactions.
Limit the check-in and check-out dates to the year 2021. This can be achieved using two statements.
Combine the two statements into the query in Step 1 and calculate the days whereby the property was rented out in each transaction by subtracting the check-in date from the check-in date.
Since we need the result on a property level, let's aggregate the results by summing the individual differences between check-out and check-in dates.
Showing you the complete output:
To obtain the vacancies for each property, we need to do two steps.
Wrap the query in Step 4 in a CTE and we are ready for the last and the easiest step: just use the function to obtain the average vacant days across all the properties, and then round the results into a whole number.