Back to questions
User Shopping Sprees Amazon SQL Interview Question
User Shopping Sprees
Amazon SQL Interview Question
In an effort to identify high-value customers, Amazon asked for your help to obtain data about users who go on shopping sprees. A shopping spree occurs when a user makes purchases on 3 or more consecutive days.
List the user IDs who have gone on at least 1 shopping spree in ascending order.
Table:
| Column Name | Type |
|---|---|
| user_id | integer |
| amount | float |
| transaction_date | timestamp |
Example Input:
| user_id | amount | transaction_date |
|---|---|---|
| 1 | 9.99 | 08/01/2022 10:00:00 |
| 1 | 55 | 08/17/2022 10:00:00 |
| 2 | 149.5 | 08/05/2022 10:00:00 |
| 2 | 4.89 | 08/06/2022 10:00:00 |
| 2 | 34 | 08/07/2022 10:00:00 |
Example Output:
| user_id |
|---|
| 2 |
Explanation
In this example, 2 is the only one who has gone on a shopping spree.
The dataset you are querying against may have different input & output - this is just an example!
Solution
This is a tough task, so we'd like to thank our user Rithu Soumyaj for creating a super-concise solution!
The simplest way to do this problem is by performing two s on the transactions table. We're looking for users who make purchases on 3 or more consecutive days. Click here to learn more about s in PostgreSQL.
To implement this, we'll refer to the first version of the table as . We can join another version of the table as . If the in is one day after the in , we have purchased over two consecutive days.
The would look something like this:
All we have to do is implement this logic one more time to log 2 days after the in , and we'll have all the information we need to find our answer. The combined would be:
Now, let's discuss the main . All we need to do is pull the unique users from who are featured in this joined dataset:
The problem also specified that the user IDs should be sorted in ascending order, so we'll add at the end.
That's it! Out-of-the-box solutions like this are a great way to practice using SQL skills in creative ways :)
Solution: