Rolling 7-Day Revenue in SQL | Dataford Interview Questions

Your question is Rolling 7-Day Revenue in SQL. 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

Publicis Sapient is analyzing daily revenue trends for an active-user reporting dashboard. Write a PostgreSQL query that calculates a rolling seven-day average of daily revenue for each active user from January 1 through January 7, 2024.

Requirements

Aggregate multiple revenue events for the same user and calendar date into one daily revenue value.
Include dates with no revenue as zero, so the rolling calculation represents calendar days rather than only transaction days.
Use a window function partitioned by user and ordered by date. The window should include the current date and up to the six preceding calendar dates.
Return active users, the date, daily revenue, and the rolling average, ordered by user and date.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	User display name
is_active	BOOLEAN	Whether the user is active

revenue_events

Column	Type	Description
event_idPK	INTEGER	Unique revenue event identifier
user_id	INTEGER	User associated with the revenue event
event_date	DATE	Calendar date of the event
event_type	VARCHAR(30)	Revenue event category
amount	NUMERIC(12,2)	Revenue amount