Moving Average with SQL Windows | Dataford Interview Questions

Veracyte uses Afirma molecular testing to support thyroid nodule diagnosis. Write a PostgreSQL query that calculates monthly diagnostic accuracy for the Afirma GSC test and smooths the trend with a three-month moving average.

Requirements

Join test_results to test_catalog and include only completed Afirma GSC results.
Exclude results where either the predicted or reference diagnosis is NULL.
Calculate monthly accuracy as correct diagnoses divided by evaluated results, expressed as a percentage.
Use a window function ordered by month to calculate the average accuracy for the current month and preceding two available reporting months. Return the month, evaluated result count, monthly accuracy, and moving average, rounded to two decimal places.

Schema

test_catalog

Column	Type	Description
test_idPK	INT	Unique test identifier
test_name	VARCHAR(100)	Name of the clinical test
platform	VARCHAR(100)	Technology platform used by the test

test_results

Column	Type	Description
result_idPK	INT	Unique test result identifier
test_id	INT	References test_catalog.test_id
result_date	DATE	Date the result was issued
predicted_diagnosis	VARCHAR(50)	Diagnosis predicted by the test
reference_diagnosis	VARCHAR(50)	Confirmed reference diagnosis
status	VARCHAR(30)	Processing status of the result

Your question is Moving Average with SQL Windows. 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