logo

Back to questions

Follow-Up Airpod Percentage

Hard

The Apple retention team needs your help to investigate buying patterns. Write a query to determine the percentage of buyers who bought AirPods directly after they bought iPhones. Round your answer to a percentage (i.e. 20 for 20%, 50 for 50) with no decimals.

Clarifications:

  • The users were interested in buying iPhones and then AirPods, with no intermediate purchases in between.
  • Users who buy iPhones and AirPods at the same time, with the iPhone logged first, can still be counted.

Tip:

  • Multiply by 100 before you perform the rounding to make sure you get the same answer we did :)

Table:

Column NameType
transaction_idinteger
customer_idinteger
product_namevarchar
transaction_timestampdatetime

Example Input:

transaction_idcustomer_idproduct_nametransaction_timestamp
1101iPhone08/08/2022 00:00:00
2101AirPods08/08/2022 00:00:00
5301iPhone09/05/2022 00:00:00
6301iPad09/06/2022 00:00:00
7301AirPods09/07/2022 00:00:00

Example Output:

follow_up_percentage
50

Of the two users, only user 101 bought AirPods after buying an iPhone.

Note that we still count user 101, even though they bought both an iPhone and AirPods in the same transaction. We can't count customer 301 since they bought an iPad in between their iPhone and AirPods.

Therefore, 1 out of 2 users fit the problem's criteria. For this example, the follow-up percentage would be 50%.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.