Back to questions
Same Week Purchases Etsy SQL Interview Question
Same Week Purchases
Etsy SQL Interview Question
This is the same question as problem #29 in the SQL Chapter of Ace the Data Science Interview!
You are given the two tables containing information on Etsy’s user signups and purchases. Write a query to obtain the percentage of users who signed up and made a purchase within 7 days of signing up. The result should be rounded to the nearest 2 decimal places.
Assumptions:
- Signups who didn't buy any products yet should also count as part of the percentage of users who signed up and made a purchase within 7 days of signing up
- If the signup date is on 06/21/2022 and the purchase date on 06/26/2022, then the user makes up part of the percentage of users who signed up and purchased within the 7 days of signing up.
Table:
| Column Name | Type |
|---|---|
| user_id | integer |
| signup_date | datetime |
Example Input:
| user_id | signup_date |
|---|---|
| 445 | 06/21/2022 12:00:00 |
| 742 | 06/19/2022 12:00:00 |
| 648 | 06/24/2022 12:00:00 |
| 789 | 06/27/2022 12:00:00 |
| 123 | 06/27/2022 12:00:00 |
Table:
| Column Name | Type |
|---|---|
| user_id | integer |
| product_id | integer |
| purchase_amount | decimal |
| purchase_date | datetime |
Example Input:
| user_id | product_id | purchase_amount | purchase_date |
|---|---|---|---|
| 244 | 7575 | 45.00 | 06/22/2022 12:00:00 |
| 742 | 1241 | 50.00 | 06/28/2022 12:00:00 |
| 648 | 3632 | 55.50 | 06/25/2022 12:00:00 |
| 123 | 8475 | 67.30 | 06/29/2022 12:00:00 |
| 244 | 2341 | 74.10 | 06/30/2022 12:00:00 |
Example Output:
| single_purchase_pct |
|---|
| 40.00 |
Explanation: The only users who purchased within 7 days of signing up are users 648 and 123. The total count of given signups is 5, resulting in a percentage of 2/5 = 40%.
The dataset you are querying against may have different input & output - this is just an example!
Solution
First, we have to understand the formula and identify the elements to solve this question.
Percentage of users who purchased within 7 days of signing up = Number of users who purchased within 7 days of signing up / Number of users who sign-up
There are 2 elements that we will need before calculating the percentage:
- Users who sign-up, and
- Users who purchased within 7 days of signing up
By using the on the table, we can return all of the users that have signed up in addition to those that made a purchase within the same week. This fulfils the first element that we need to find which is the users who sign-up.
Additionally, we include additional filters
- --> Include users who have not made any purchase
- --> Fulfils the users who purchased within 7 days of signing up
- Simply means that we want the users who purchased between the day of sign-up (Day 1) and the 7th day from the date of sign-up (Day 7).
- is used to add 7 days week to the . Neat trick, isn't it?
Run this code and study the output for yourself
Subsequently, we take the count of distinct users from the table to obtain the number of users who made a purchase and divide it by the count of distinct users from the table to obtain the number of users who signup.
Then, we multiply the results by 100.0 instead of 100 to obtain the results in a percentage format. Finally, we use the function will allow us to display the results with 2 decimal places.