7-Day Rolling Average Excluding Weekends

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is 7-Day Rolling Average Excluding Weekends. Start with the requirements and the one table 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.

You need to log in / sign up to run or submit.

Problem

We have a table of daily sales data. Write a query to calculate the 7-day rolling average revenue for each product, but exclude weekends from the calculation and handle gaps in the data.

Use a seven-calendar-day window ending on each recorded sale date. Missing dates must not be treated as zero, and multiple records for the same product and date should contribute to that day's revenue.

Output

One row per product and recorded weekday with sales data
Columns: product_id, product_name, sale_date, daily_revenue, and rolling_7_day_avg
Exclude weekend dates, round the rolling average to two decimal places, and order by product_id, then sale_date

Schema

daily_sales

Column	Type	Description
sale_idPK	INT	Unique sales record identifier
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
sale_date	DATE	Date on which the revenue was recorded
revenue	NUMERIC(12,2)	Revenue recorded for the product on the sale date

Tablesdaily_sales

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results