Running Deposit Totals by User | Dataford Interview Questions

Your question is Running Deposit Totals by User. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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'.

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for the user
user_name	VARCHAR(100)	Full name of the user
signup_date	DATE	Date the user signed up
status	VARCHAR(20)	Current account status

transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for the transaction
user_id	INT	User who made the transaction
transaction_date	DATE	Date of the transaction
transaction_type	VARCHAR(20)	Transaction type such as deposit or withdrawal
amount	DECIMAL(10,2)	Transaction amount
channel	VARCHAR(20)	Channel used for the transaction