Your question is Rolling Average Prescription Volumes. 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.
Genentech's commercial analytics team monitors monthly Ocrevus prescription volume across regional territories. The team needs a smoothed trend that reduces the impact of short-term monthly variation.
Write a PostgreSQL query that calculates the rolling three-month average of Ocrevus prescriptions for each matched regional territory from January through April 2025.
| Column | Type | Description |
|---|---|---|
| territory_idPK | INTEGER | Unique regional territory identifier |
| territory_name | VARCHAR(50) | Name of the regional territory |
| region_manager | VARCHAR(100) | Manager assigned to the territory |
| Column | Type | Description |
|---|---|---|
| prescription_idPK | INTEGER | Unique prescription volume record identifier |
| territory_id | INTEGER | Referenced regional territory identifier |
| product_name | VARCHAR(100) | Product associated with the prescription record |
| prescription_month | DATE | Date representing the prescription month |
| prescription_volume | INTEGER | Number of prescriptions recorded |