Follow-Up Airpod Percentage
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.
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%.
This is a complex problem with a lot of components. Let's break down all the steps:
From now on, let's refer to users who bought AirPods directly after iPhones as "interested users" since they're the users we are interested in.
Before anything else, we need to be able to visualize all the cases where AirPods were bought directly after iPhones. We can accomplish this by using the window function to create a new column which will show us the previous product bought. This way, we'll be able to see when AirPods were bought directly after an iPhone, which is exactly the case we're looking for!
We'll be careful to each customer and the timestamp. This way, we'll make sure that we show each individual customer's previous purchase, and not just the most recent purchase overall. Don't forget to the non-aggregated columns at the end.
The query will look something like this:
Click here to learn more about window functions in PostgreSQL.
3 rows of the output:
As you can see, the first and last rows are , but the middle row shows that User 101's latest purchase before the AirPods was an iPhone! This is exactly what we need :) Let's keep going...
We'll wrap the above query in a Common Table Expression (CTE) called so that we can reuse the output later. Now, it's time to document all the interested users. We'll select all the unique users who meet two conditions:
We'll use to pull unique users and for the 2 filters. Let's also include the best practice of using the function to neutralize the letter case– we wouldn't want an extra capital letter in our data to mess up the result.
The query will look like this:
Congratulations – you've arrived at the final step! Let's wrap the above query in another CTE called so we can use it again.
First, we need to the CTE with the table so that all of our data is in one place. Then, we'll need to perform the actual division. This is a special operation called a ; click here to learn more.
All we have to do here is divide the count of interested users by the count of total users and call it a day, right? Not quite.
In order to divide integers in PostgreSQL, we need to convert the to a numeric type like ; otherwise, PostgreSQL will simply return 0. Our basic percentage calculation, including the double-colon conversions, looks like this (the CTE uses the alias ): .
For the final step, let's wrap the above calculation inside a function and specify 0 decimal places:
That's it, you did it! Great job persevering on this one :)