Back to questions
3-Day Rolling Earnings [Stripe SQL Interview Question]
This is the same question as problem #33 in the SQL Chapter of Ace the Data Science Interview!
Assume you are given the table below containing information on user transactions on Stripe. Write a query to obtain all of the users' rolling 3-day earnings. Output the user ID, transaction date and rolling 3-day earnings.
Assumption:
- The table is complete and there are no missing days in between.
- Each row in the table represents one day.
Table:
| Column Name | Type |
|---|---|
| transaction_id | integer |
| user_id | integer |
| amount | decimal |
| transaction_date | datetime |
Example Input:
| transaction_id | user_id | amount | transaction_date |
|---|---|---|---|
| 1354 | 423 | 240.13 | 01/01/2022 12:00:00 |
| 1223 | 525 | 124.20 | 01/01/2022 12:00:00 |
| 1732 | 525 | 324.00 | 01/02/2022 12:00:00 |
| 1352 | 423 | 234.14 | 01/02/2022 12:00:00 |
| 1141 | 525 | 123.00 | 01/03/2022 12:00:00 |
Example Output:
| user_id | transaction_date | rolling_earnings_3d |
|---|---|---|
| 423 | 01/01/2022 12:00:00 | 240.13 |
| 423 | 01/03/2022 12:00:00 | 474.27 |
| 525 | 01/01/2022 12:00:00 | 124.20 |
| 525 | 01/02/2022 12:00:00 | 448.20 |
| 525 | 01/03/2022 12:00:00 | 571.20 |
User 525's rolling 3-day earning of 571.20 is a cumulative total of the 2 previous days (124.20 + 324.00) and current day (123.00).
The dataset you are querying against may have different input & output - this is just an example!
Solution
If this is your first time with the rolling sum, don't worry – we'll take you through the solution step-by-step!
Objective: Find the rolling 3-day earnings for each user by transaction dates.
2-step approach:
- Find the total earnings by user and transaction date.
- Get the rolling sum of earnings of the preceding (previous) 2 days and the current day.
Step 1
The table is recorded by individual transactions and not grouped by the date, so we have to sum the earnings by user for each date.
The query should look something like this:
The output for user 423:
| user_id | transaction_date | total_earnings |
|---|---|---|
| 423 | 01/01/2022 12:00:00 | 240.13 |
| 423 | 01/03/2022 12:00:00 | 401.38 |
| 423 | 01/04/2022 12:00:00 | 123.45 |
| 423 | 01/06/2022 12:00:00 | 82.90 |
Keep in mind that you do not need to order the results using at this stage, because the above query will be converted into a Common Table Expression (CTE).
Step 2
Next, we'll wrap the query into a CTE called to reuse the output; then, we can calculate rolling 3-day earnings using the field (see below).
Full Solution:
Let's break down the rolling sum calculation, where is essentially used as a window function.
Taken alone, the block looks like this:
Steps within the clause:
- : SQL adds the total earnings
- : The operation is grouped by the user and sorted by transaction date in ascending order
- : specifies that we only want SQL to add the total earnings from the previous 2 days (not rows) and the current day".
Now, if you're familiar with using the window function, I'm sure you've heard of as well. Do you know why we're using the former instead of the latter?
Have a run with this query and we'll explain the differences to you.
Output for user 423 only:
| user_id | transaction_date | total_earnings | rolling_3d_earnings_range | rolling_3d_earnings_rows |
|---|---|---|---|---|
| 423 | 01/01/2022 12:00:00 | 240.13 | 240.13 | 240.13 |
| 423 | 01/03/2022 12:00:00 | 401.38 | 641.51 | 641.51 |
| 423 | 01/04/2022 12:00:00 | 123.45 | 524.83 | 764.96 |
| 423 | 01/06/2022 12:00:00 | 82.90 | 206.35 | 607.73 |
field is the rolling 3-day earnings using (as used in the solution) whereas field uses .
Let's focus on the 3rd row, 01/04/2022. Using RANGE, we're accounting for the previous 2 days, 01/03/2022 401.38 and 01/04/2022 123.45 and the current day's earnings, 01/03/2022 401.38 = 524.83. The days are taken into account and not the rows.
If we used ROWS, we'd have to consider the previous 2 rows and the current row's earnings which are 01/01/2022 240.13, 01/03/2022 401.38 and 01/04/2022 123.45 = 764.96. Rows don't directly correspond to days, so it doesn't make sense to pull 3 rows and assume that they represent 3 distinct days.
As the question is asking for a 3-day rolling sum, it's more appropriate to use RANGE, not ROWS.
We hope you learned something new in this question, and that you had as much fun as we did!
PostgreSQL 14