Rolling Window Genomic Orders | Dataford Interview Questions

Your question is Rolling Window Genomic Orders. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Tempus AI uses genomic test order data to monitor patient testing activity over time. Write a PostgreSQL query that calculates the rolling three-month average of completed genomic test orders for every patient.

Requirements

Aggregate completed genomic test orders by patient and calendar month.
Include months with zero completed orders, including patients with no orders.
Use a window function partitioned by patient and ordered by month. For the first two months, average the available months.
Return patient_id, month_start, monthly_orders, and rolling_three_month_avg, ordered by patient and month.

Schema

patients

Column	Type	Description
patient_idPK	INTEGER	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name

genomic_test_orders

Column	Type	Description
order_idPK	INTEGER	Unique genomic test order identifier
patient_id	INTEGER	Patient associated with the order
ordered_at	TIMESTAMP	Timestamp when the order was created
order_status	VARCHAR(20)	Current order status