logo

Back to questions

Rolling 3-Day Earnings

Hard

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 NameType
transaction_idinteger
user_idinteger
amountdecimal
transaction_datedatetime

Example Input:

transaction_iduser_idamounttransaction_date
1354423240.1301/01/2022 12:00:00
1223525124.2001/01/2022 12:00:00
1732525324.0001/02/2022 12:00:00
1352423234.1401/02/2022 12:00:00
1141525123.0001/03/2022 12:00:00

Example Output:

user_idtransaction_daterolling_earnings_3d
42301/01/2022 12:00:00240.13
42301/03/2022 12:00:00474.27
52501/01/2022 12:00:00124.20
52501/02/2022 12:00:00448.20
52501/03/2022 12:00:00571.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).

PostgreSQL 14

Refer friends to get bonus content & cool prizes.