Rolling 7-Day Spending SQL | Dataford Interview Questions

Given a database schema with user actions and transaction histories, write a SQL query to calculate the rolling 7-day average of user spending for Garner health analytics.

Use completed transactions linked to spending actions. Treat dates without spending as zero and return results only for users with at least one qualifying transaction.

Output

One row per user and spending date
Columns: user_id, spending_date, daily_spending, rolling_7_day_avg
Include dates from each user's first qualifying transaction through their last
Order by user_id, then spending_date

Schema

users

Column	Type	Description
user_idPK	INT	Unique Garner user identifier
user_name	VARCHAR(100)	User display name

user_actions

Column	Type	Description
action_idPK	INT	Unique user action identifier
user_id	INT	User associated with the action
action_type	VARCHAR(30)	Action classification
action_date	DATE	Date the action occurred

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User who made the transaction
action_id	INT	Linked user action
transaction_date	DATE	Date the transaction was recorded
amount	NUMERIC(12,2)	Transaction amount in dollars
status	VARCHAR(20)	Transaction processing status

Your question is Rolling 7-Day Spending SQL. Start with the requirements and the three tables in the Question tab.

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

Problem