SQL Rolling Window Analytics | Dataford Interview Questions

Your question is SQL Rolling Window Analytics. 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

The U.S. Food and Drug Administration uses the FDA Adverse Event Reporting System, or FAERS, to monitor reported adverse events. Write a PostgreSQL query that summarizes oncology-related reports by month for calendar year 2024.

Requirements

Join FAERS reports to their reported drug products and retain only products whose therapeutic_area is Oncology.
Count adverse event reports for each calendar month from January through December 2024.
Calculate a running total of reports ordered chronologically.
Calculate a twelve-month moving average using the current month and up to the previous eleven monthly observations. Return results in chronological order.

Schema

faers_reports

Column	Type	Description
report_idPK	BIGINT	Unique FDA FAERS report identifier
drug_product_id	INT	Referenced drug product identifier
received_date	DATE	Date FDA received the report
report_source	VARCHAR(40)	Source of the adverse event report

faers_drug_products

Column	Type	Description
drug_product_idPK	INT	Unique reported drug product identifier
product_name	VARCHAR(120)	Reported drug product name
therapeutic_area	VARCHAR(60)	Therapeutic area associated with the product