Aggregate Completed JSON Events by Action | Dataford Interview Questions

How do you optimize a script that is performing slowly on a large JSON dataset?

Write a PostgreSQL query that reports completed events from production batches in the requested date range. Extract only the required JSON attributes and exclude batches outside the range or from other source systems.

Output

One row per batch date and event action.
Columns: batch_date, action, event_count, and avg_duration_ms.
Include completed events from production batches dated from 2026-01-01 through 2026-01-04, inclusive.
Order by batch_date, then action.

Output details

Filter batches whose source_system equals prod.
Order both batch_date and action ascending.
Round avg_duration_ms to exactly 2 decimal places.
Ignore NULL duration_ms values when averaging, returning NULL when every value is NULL.
Include zero-valued durations in the average.

Schema

event_batches

Column	Type	Description
batch_idPK	INT	Unique identifier for an ingestion batch
batch_date	DATE	Date associated with the batch
source_system	VARCHAR(50)	System that produced the batch

event_records

Column	Type	Description
record_idPK	INT	Unique identifier for an event record
batch_id	INT	Batch containing the event record
payload	VARCHAR(2000)	JSON event payload stored as text

Your question is Aggregate Completed JSON Events by Action. 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