Sales by Day Window | Dataford Interview Questions

Compute a rolling mean per product using SQL window functions.

This was asked in the SQL deep dive stage. The candidate used UNBOUNDED PRECEDING.

Output

Return one row per product sale with product_id, sale_date, sale_id, sale_amount, and rolling_mean.
Include rows with NULL sale amounts. The rolling mean should reflect all preceding and current rows for that product, ordered by date and then sale_id.
Order the result by product_id, sale_date, and sale_id.

product_sales

Column	Type	Description
sale_idPK	INT	Unique identifier for the sale record
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
sale_date	DATE	Date of the sale
sale_amount	NUMERIC(10,2)	Sale amount used in the rolling mean

Your question is Sales by Day Window. Start with the requirements and the one table 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.

Compute a rolling mean per product using SQL window functions.

This was asked in the SQL deep dive stage. The candidate used UNBOUNDED PRECEDING.

Return one row per product sale with product_id, sale_date, sale_id, sale_amount, and rolling_mean.
Include rows with NULL sale amounts. The rolling mean should reflect all preceding and current rows for that product, ordered by date and then sale_id.
Order the result by product_id, sale_date, and sale_id.

product_sales

Column	Type	Description
sale_idPK	INT	Unique identifier for the sale record
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
sale_date	DATE	Date of the sale
sale_amount	NUMERIC(10,2)	Sale amount used in the rolling mean

Your question is Sales by Day Window. Start with the requirements and the one table 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.