Solve SQL Modeling Tasks | Dataford Interview Questions

Explain how you would write SQL to compute a rolling sum, flatten nested JSON into relational tables, and handle the edge cases you expect in real event data.

Use the Coalition event and account data provided. Include accepted events only, ignore malformed or incomplete measurements, and calculate a seven-day rolling sum per account and metric.

Output

One row per account, metric, and event date with columns account_name, metric_name, event_date, daily_amount, and rolling_7_day_amount.
Ignore rows without a valid measurements array, metric, or numeric amount.
Order by account_name, metric_name, and event_date ascending.

Schema

coalition_accounts

Column	Type	Description
account_idPK	INT	Coalition account identifier
account_name	VARCHAR(100)	Display name of the account

coalition_events

Column	Type	Description
event_idPK	INT	Unique event identifier
account_id	INT	Referenced Coalition account
occurred_at	TIMESTAMP	Event occurrence timestamp
event_type	VARCHAR(50)	Type of event received
ingestion_status	VARCHAR(30)	Event ingestion state
payload	JSONB	Nested event measurements

Your question is Solve SQL Modeling Tasks. 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