Interview Guides

Running Deposit Totals by User | Dataford Interview Questions - Dataford - Ace your Interview

Running Deposit Totals by User

Medium

SQL & Data Manipulation

Asked at 3 companies3Window FunctionsRunning TotalsDate Functions

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"users":[[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"],[6,"Farah Khan","2024-02-18",null],[4,"Derek Moss","2024-03-05","active"],[7,"Grace Park","2024-03-12","closed"],[8,"Hugo Silva","2024-03-18","active"],["1","Alice Chen","2024-01-15","suspended"],["2","Derek Moss","2024-02-13","standard"],["3","Zane Cooper","2024-02-10","vip"],["4","Grace Park","2024-03-17","null"],["5","Liam T

Output

[["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"],["4","Derek Moss","2024-03-12","60.00","60.00"],["5","Ethan Brooks","2024-03-30","-10.00","-10.00"],["6","Farah Khan","2024-03-18","125.00","125.00"],["6","Farah Khan","2024-03-29","75.00","200.00"],["8","

Problem

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

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

Sample Data

Example 1

Input

{"users":[[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"],[6,"Farah Khan","2024-02-18",null],[4,"Derek Moss","2024-03-05","active"],[7,"Grace Park","2024-03-12","closed"],[8,"Hugo Silva","2024-03-18","active"],["1","Alice Chen","2024-01-15","suspended"],["2","Derek Moss","2024-02-13","standard"],["3","Zane Cooper","2024-02-10","vip"],["4","Grace Park","2024-03-17","null"],["5","Liam T

Output

[["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"],["4","Derek Moss","2024-03-12","60.00","60.00"],["5","Ethan Brooks","2024-03-30","-10.00","-10.00"],["6","Farah Khan","2024-03-18","125.00","125.00"],["6","Farah Khan","2024-03-29","75.00","200.00"],["8","

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

7-Day Rolling Active UsersMedium

First Deposit After SignupMedium

Compare Weekly User Activity TrendsMedium

Next question

Running Deposit Totals by User

Medium

SQL & Data Manipulation

Asked at 3 companies3Window FunctionsRunning TotalsDate Functions

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"users":[[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"],[6,"Farah Khan","2024-02-18",null],[4,"Derek Moss","2024-03-05","active"],[7,"Grace Park","2024-03-12","closed"],[8,"Hugo Silva","2024-03-18","active"],["1","Alice Chen","2024-01-15","suspended"],["2","Derek Moss","2024-02-13","standard"],["3","Zane Cooper","2024-02-10","vip"],["4","Grace Park","2024-03-17","null"],["5","Liam T

Output

[["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"],["4","Derek Moss","2024-03-12","60.00","60.00"],["5","Ethan Brooks","2024-03-30","-10.00","-10.00"],["6","Farah Khan","2024-03-18","125.00","125.00"],["6","Farah Khan","2024-03-29","75.00","200.00"],["8","

Problem

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

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

Sample Data

Example 1

Input

{"users":[[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"],[6,"Farah Khan","2024-02-18",null],[4,"Derek Moss","2024-03-05","active"],[7,"Grace Park","2024-03-12","closed"],[8,"Hugo Silva","2024-03-18","active"],["1","Alice Chen","2024-01-15","suspended"],["2","Derek Moss","2024-02-13","standard"],["3","Zane Cooper","2024-02-10","vip"],["4","Grace Park","2024-03-17","null"],["5","Liam T

Output

[["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"],["4","Derek Moss","2024-03-12","60.00","60.00"],["5","Ethan Brooks","2024-03-30","-10.00","-10.00"],["6","Farah Khan","2024-03-18","125.00","125.00"],["6","Farah Khan","2024-03-29","75.00","200.00"],["8","

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

7-Day Rolling Active UsersMedium

First Deposit After SignupMedium

Compare Weekly User Activity TrendsMedium

Next question