Rolling Averages Across Batches | Dataford Interview Questions

Recursion Pharmaceuticals needs to monitor metric stability across distinct laboratory batches in its high-throughput screening pipeline. Write a PostgreSQL query that calculates rolling 30-day averages for completed batches.

Requirements

Join laboratory_batches to screening_metrics using batch_id.
Include only completed batches and cell_viability measurements with non-null values.
Aggregate multiple measurements from the same batch and date before calculating the rolling average.
Calculate a calendar-based 30-day average per batch, including the current date and preceding 29 days. Return results ordered by batch and measurement date.

Schema

laboratory_batches

Column	Type	Description
batch_idPK	VARCHAR(20)	Unique laboratory batch identifier
batch_label	VARCHAR(100)	Human-readable batch label
experiment_name	VARCHAR(120)	Screening experiment name
batch_status	VARCHAR(20)	Batch lifecycle status

screening_metrics

Column	Type	Description
metric_idPK	INT	Unique metric observation identifier
batch_id	VARCHAR(20)	Laboratory batch associated with the observation
measurement_date	DATE	Date of metric measurement
metric_name	VARCHAR(60)	Screening metric name
metric_value	DECIMAL(10,2)	Observed metric value

Your question is Rolling Averages Across Batches. 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