Task
FinFlow wants to monitor recent deposit activity by user. Write a SQL query to calculate each user's cumulative sum of deposits over the last 30 days.
Requirements
- Return one row per user and deposit date for deposits made in the last 30 days.
- Include only transactions where
transaction_type = 'deposit'.
- Sum multiple deposits on the same day into a single
daily_deposit_amount.
- Calculate a running total per user ordered by deposit date.
- Return
user_id, user_name, deposit_date, daily_deposit_amount, and cumulative_deposit_amount.
- Sort the final output by
user_id and deposit_date.
Assume the query is run with a reference date of 2024-03-31, so the last 30 days means transaction_date >= DATE '2024-03-02'.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for each user |
| user_name | VARCHAR(100) | User's full name |
| signup_date | DATE | Date the user registered |
| status | VARCHAR(20) | Account status |
transactions
| column | type | description |
|---|
| transaction_id | INT | Primary key for each transaction |
| user_id | INT | References users.user_id |
| transaction_date | DATE | Date of the transaction |
| transaction_type | VARCHAR(20) | Type such as deposit or withdrawal |
| amount | DECIMAL(10,2) | Transaction amount |
| channel | VARCHAR(20) | Deposit channel |
Sample Data
users
| user_id | user_name | signup_date | status |
|---|
| 3 | Carla Gomez | 2024-01-20 | active |
| 1 | Alice Chen | 2024-01-05 | active |
| 5 | Ethan Brooks | 2024-03-01 | suspended |
| 2 | Brian Lee | 2024-02-10 | active |
transactions
| transaction_id | user_id | transaction_date | transaction_type | amount | channel |
|---|
| 101 | 1 | 2024-03-05 | deposit | 100.00 | bank_transfer |
| 102 | 1 | 2024-03-05 | deposit | 50.00 | card |
| 103 | 1 | 2024-03-10 | withdrawal | 20.00 | atm |
| 104 | 1 | 2024-03-20 | deposit | 200.00 | bank_transfer |
| 105 | 2 | 2024-03-02 | deposit | 300.00 | card |
| 106 | 2 | 2024-03-15 | deposit | 100.00 | null |
| 107 | 2 | 2024-02-28 | deposit | 500.00 | card |
| 108 | 3 | 2024-03-25 | deposit | 0.00 | promo_credit |
| 109 | 3 | 2024-03-28 | deposit | 75.00 | bank_transfer |
| 110 | 4 | 2024-03-12 | deposit | 60.00 | card |
Expected Output
| user_id | user_name | deposit_date | daily_deposit_amount | cumulative_deposit_amount |
|---|
| 1 | Alice Chen | 2024-03-05 | 150.00 | 150.00 |
| 1 | Alice Chen | 2024-03-20 | 200.00 | 350.00 |
| 2 | Brian Lee | 2024-03-02 | 300.00 | 300.00 |
| 2 | Brian Lee | 2024-03-15 | 100.00 | 400.00 |
| 3 | Carla Gomez | 2024-03-25 | 0.00 | 0.00 |
| 3 | Carla Gomez | 2024-03-28 | 75.00 | 75.00 |
