Compute 7-Day Rolling Revenue | Dataford Interview Questions

Your question is Compute 7-Day Rolling Revenue. Start with the requirements and the one table 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

Lynx Analytics needs a time-based revenue feature for user behavior analysis. Write a PostgreSQL query that aggregates event revenue by user and calendar day, then calculates each user's trailing 7-day revenue for every day on which that user has an event.

The window must include the current calendar day and the six preceding calendar days. Treat a NULL revenue value as zero, and retain events whose user_id is NULL as a separate partition.

Requirements

Aggregate multiple events for the same user and calendar day before applying the rolling calculation.
Return user_id, event_day, daily_revenue, and rolling_7_day_revenue.
Use a time-based window frame rather than a fixed number of rows, because users may have gaps between active days.
Order the final results by user_id ascending with NULL users first, then by event_day ascending.

Schema

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
event_time	TIMESTAMPTZ	UTC timestamp when the event occurred
revenue	NUMERIC(12,2)	Revenue attributed to the event

Your question is Compute 7-Day Rolling Revenue. Start with the requirements and the one table 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.

The window must include the current calendar day and the six preceding calendar days. Treat a NULL revenue value as zero, and retain events whose user_id is NULL as a separate partition.

Requirements

Aggregate multiple events for the same user and calendar day before applying the rolling calculation.
Return user_id, event_day, daily_revenue, and rolling_7_day_revenue.
Use a time-based window frame rather than a fixed number of rows, because users may have gaps between active days.
Order the final results by user_id ascending with NULL users first, then by event_day ascending.

Schema

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
event_time	TIMESTAMPTZ	UTC timestamp when the event occurred
revenue	NUMERIC(12,2)	Revenue attributed to the event