SQL Rolling 30-Day Average Dosage | Dataford Interview Questions

Write a SQL query to find the average chemical dosage per well over a rolling 30-day window.

Use the wells and chemical_dosages tables. Include dosage records associated with a known well, and exclude records with no matching well.

Output

One row per dosage record associated with a known well
Columns: well_name, dosed_at, and rolling_avg_dosage
The average includes non-null dosage values from the current record and the preceding 29 calendar days for the same well
Round rolling_avg_dosage to two decimal places and order by well_name, dosed_at, and dosage_id

wells

Column	Type	Description
well_idPK	INT	Unique identifier for the well
well_name	VARCHAR(100)	Operational name of the well
field_name	VARCHAR(100)	Oilfield containing the well
basin	VARCHAR(100)	Production basin

chemical_dosages

Column	Type	Description
dosage_idPK	INT	Unique identifier for a dosage measurement
well_id	INT	Well receiving the chemical dosage
dosed_at	TIMESTAMP	Date and time of the dosage measurement
chemical_name	VARCHAR(100)	Chemical product used
dosage_liters	NUMERIC(10,2)	Chemical dosage amount in liters

Your question is SQL Rolling 30-Day Average Dosage. 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.