Running Total Over 7 Days | Dataford Interview Questions

Your question is Running Total Over 7 Days. 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

Accenture SynOps uses an operations dashboard to monitor daily transaction revenue. Write a PostgreSQL query that calculates the revenue for each calendar date and the rolling revenue total for that date and the six preceding calendar days.

Requirements

Aggregate transaction amounts by calendar date.
Include dates with no transactions, showing daily revenue as 0.
Calculate a seven-calendar-day rolling total using a window function.
Return results in chronological order for all dates in reporting_calendar.

A transaction with a NULL amount contributes no revenue. Transactions without a valid calendar date are outside the reporting calendar and should not appear in the result.

Schema

reporting_calendar

Column	Type	Description
calendar_datePK	DATE	Calendar date included in reporting

synops_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(10,2)	Transaction revenue
channel	VARCHAR(30)	Source channel for the transaction