logo

Back to questions

Booking Referral Source

Medium

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 NameType
booking_idinteger
user_idinteger
booking_datedatetime

Example Input:

booking_iduser_idbooking_date
1101/01/2022 00:00:00
2101/06/2022 00:00:00
6201/06/2022 00:00:00
8301/06/2022 00:00:00

Table:

Column NameType
booking_idinteger
channelstring

Example Input:

booking_idchannel
1organic search
2
3organic search
4referral
5email
6organic search
7paid search
8
9paid search
10paid search

Example Output:

channelfirst_booking_pct
organic search67

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%.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.