Window Functions for Throughput Trends | Dataford Interview Questions

MSD's research operations team needs a monthly view of completed experimental throughput for active programs. Write a PostgreSQL query that aggregates throughput by experiment date, then calculates cumulative and short-term throughput trends within each calendar month.

Requirements

Join throughput_events to experiments and include only active experiments, completed runs, and nonnegative or NULL throughput values from January and February 2025.
Aggregate throughput by event date and month, treating NULL throughput as zero.
Use window functions partitioned by month to calculate a running total and a three-reported-day moving average, ordered chronologically.
Return one row per reported date, ordered by month and event date.

Schema

experiments

Column	Type	Description
experiment_idPK	INT	Unique experiment identifier
experiment_name	VARCHAR(100)	Name of the experiment
therapeutic_area	VARCHAR(80)	Therapeutic research area
status	VARCHAR(20)	Current experiment program status

throughput_events

Column	Type	Description
event_idPK	INT	Unique throughput event identifier
experiment_id	INT	Experiment associated with the event
event_date	DATE	Date throughput was recorded
throughput_samples	INT	Number of processed samples
run_status	VARCHAR(20)	Execution status of the run

Your question is Window Functions for Throughput Trends. 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