SQL Rolling 7-Day Metric | Dataford Interview Questions

How would you design a SQL query using window functions to calculate a rolling 7-day metric for each customer at Konfío?

Assume the metric is the total completed transaction amount, with the current date included and the prior six calendar days included. Return only dates with qualifying transactions.

Output

One row per customer and transaction date
Columns: customer_id, customer_name, transaction_date, daily_amount, rolling_7_day_amount
Include completed transactions only, ordered by customer_id, transaction_date

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_date	DATE	Calendar date of the transaction
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is SQL Rolling 7-Day Metric. Start with the requirements and the two 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