Modeling for New Analytical Use Case | Dataford Interview Questions

Describe your process for modeling data for a new analytical use case.

Use source_accounts and source_events to produce a reusable account-level analytical result. Include accounts without qualifying events, exclude events outside January 2025 or without a timestamp, and preserve null source attributes.

Output

One row per account, ordered by account_id ascending.
Columns: account_id, account_name, region, event_count, successful_event_count, total_value, and first_event_at.
Counts and total value must be zero when an account has no qualifying events; first_event_at must remain NULL.

Schema

source_accounts

Column	Type	Description
account_idPK	INT	Stable source identifier for an account
account_name	VARCHAR(100)	Account display name
region	VARCHAR(50)	Optional account region
created_at	DATE	Date the account was created

source_events

Column	Type	Description
event_idPK	INT	Unique source event identifier
account_id	INT	Account associated with the event
event_ts	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(40)	Source event classification
outcome	VARCHAR(20)	Result of the event
value	NUMERIC(12,2)	Optional numeric event value

Your question is Modeling for New Analytical Use Case. 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