Back to questions
Booking Referral Source Airbnb SQL Interview Question
Booking Referral Source
Airbnb SQL Interview Question
The Airbnb marketing analytics team is trying to understand what are the most common marketing channels that lead users to book their first rental on Airbnb.
Write a query to find the top marketing channel and percentage of first rental bookings from the aforementioned marketing channel. Round the percentage to the closest integer. Assume there are no ties.
Assumptions:
- Marketing with null values should be incorporated in the percentage of first bookings calculation, but the top channel should not be a null value. Meaning, we cannot have null as the top marketing channel.
- To avoid integer division, multiple the percentage with 100.0 and not 100.
Table:
| Column Name | Type |
|---|---|
| booking_id | integer |
| user_id | integer |
| booking_date | datetime |
Example Input:
| booking_id | user_id | booking_date |
|---|---|---|
| 1 | 1 | 01/01/2022 00:00:00 |
| 2 | 1 | 01/06/2022 00:00:00 |
| 6 | 2 | 01/06/2022 00:00:00 |
| 8 | 3 | 01/06/2022 00:00:00 |
Table:
| Column Name | Type |
|---|---|
| booking_id | integer |
| channel | string |
Example Input:
| booking_id | channel |
|---|---|
| 1 | organic search |
| 2 | |
| 3 | organic search |
| 4 | referral |
| 5 | |
| 6 | organic search |
| 7 | paid search |
| 8 | |
| 9 | paid search |
| 10 | paid search |
Example Output:
| channel | first_booking_pct |
|---|---|
| organic search | 67 |
Explanation:
We know that user 1's first booking was organic search, user 2's was organic search, and user 3's was null. Thus, 2 bookings via organic search / 3 total bookings = 67%.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1
As the first step, let's gather all of the information into one dataset by joining the and tables on the field.
Furthermore, since the question is asking for the percentage of first bookings, we will need to identify each user's first booking. We can do so by creating a new column and using the window function while ordering by sequence using in ascending order.
Showing the partial output:
| user_id | booking_id | booking_date | booking_no | channel |
|---|---|---|---|---|
| 1 | 1 | 01/01/2022 00:00:00 | 1 | direct |
| 1 | 2 | 01/06/2022 00:00:00 | 2 | |
| 2 | 6 | 01/06/2022 00:00:00 | 1 | organic search |
| 3 | 8 | 01/06/2022 00:00:00 | 1 |
User 1 has two bookings on 01/01/2022 and 01/06/2022 respectively with the earlier booking being assigned as 1 and the subsequent booking assigned as 2. We only want the booking_no 1 which will be filtered in the next step.
Note that and fields are not required in the solution and we're only including them in the query to explain the output to you.
Step 2
After we're done with the essential data preprocessing, we convert the query into CTE and write another query that keeps only the first bookings, and then count those bookings by channel.
Output is showing the number of bookings for each marketing channel:
| channel | channel_booking |
|---|---|
| direct | 2 |
| organic search | 1 |
| paid search | 1 |
| 1 |
You might be wondering:
- "What's the last row with a of 1?"
- "Why can't we calculate the percentage of first rental bookings in this query?"
Good question! (Interviewers love it when you think outside the box and ask questions like this!)
One of the question's assumptions says " with null values should be incorporated in the percentage of first bookings calculation, but the output of top channel should not be a null value.".
With that said, if we were to calculate the percentage of the first booking AND exclude the channel with null values in the same query, then we would not be fulfilling the " with null values should be incorporated in the percentage of first bookings calculation" part.
That's why we have to calculate the number of bookings associated with each channel INCLUDING bookings with the null channel which is presented in the last row with a of 1.
Step 3
Finally, we can proceed with the calculation of percentage of first rental bookings.
Percentage of first rental bookings = Number of bookings from channels that are not null / Total number of bookings including null channels
To divide the number of bookings in each channel by the total number of bookings, we will need to use a subquery inside the SELECT statement i.e.
.
This enables us to include the null channel into the calculation, even when we are filtering out the null values from the query in the clause. Don't forget to round the result to the closest integer!
Lastly, to show only the top marketing channel, order the results by the count of bookings in descending order, and limit the rows to the first row only.
That's it, good job!