Rolling 7-Day Average Daily Sales
SQL coding challenge · Difficulty: medium · Topic: Window Functions · +50 XP
Table: sales
+--------------+---------------+
| Column | Type |
+--------------+---------------+
| sale_id | INT | | sale_date | DATE | | sales_amount | DECIMAL(10,2) |
+--------------+---------------+
sale_id is the primary key. Each row is one day's sales total.
Problem
-------
A retail team wants to smooth out day-to-day noise in their sales figures.
For each day, compute the average sales_amount over a 7-day window: that day
and the 6 days before it.
Report a day ONLY when all 7 days of its window exist. The first 6 days have
fewer than 6 days behind them, so they are not part of the answer.
Return: sale_date, rolling_7day_avg
Order: sale_date ASC
Requirements
------------
1. Column names must be exactly sale_date and rolling_7day_avg. The header row
is compared, so a different alias will not match.
2. Round the average to 2 decimal places.
3. Frame the window with ROWS, not RANGE:
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
ROWS counts rows; RANGE counts by value, and over a DATE column MySQL
rejects it outright with "Window with RANGE frame has ORDER BY expression
of numeric type, ORDER BY expression of DATE type is not allowed".
Example Input (table: sales)
----------------------------
+---------+------------+--------------+
| sale_id | sale_date | sales_amount |
+---------+------------+--------------+
| 1 | 2024-01-01 | 150.00 | | 2 | 2024-01-02 | 180.00 | | 3 | 2024-01-03 | 200.00 | | 4 | 2024-01-04 | 220.00 | | 5 | 2024-01-05 | 250.00 | | 6 | 2024-01-06 | 300.00 | | 7 | 2024-01-07 | 350.00 | | 8 | 2024-01-08 | 400.00 |
+---------+------------+--------------+
Expected Output
---------------
+------------+------------------+
| sale_date | rolling_7day_avg |
+------------+------------------+
| 2024-01-07 | 235.71 | | 2024-01-08 | 271.43 |
+------------+------------------+
Explanation
-----------
2024-01-01 .. 2024-01-06
Each of these days has fewer than 6 days before it, so its 7-day window is
incomplete. Excluded from the result.
2024-01-07 window = 01-01 .. 01-07 (7 days, complete)
150 + 180 + 200 + 220 + 250 + 300 + 350 = 1650
1650 / 7 = 235.7142... -> 235.71
2024-01-08 window = 01-02 .. 01-08 (7 days, complete)
180 + 200 + 220 + 250 + 300 + 350 + 400 = 1900
1900 / 7 = 271.4285... -> 271.43
Only 2 of the 8 days qualify, so the result has 2 rows.
What this SQL challenge teaches you
“Rolling 7-Day Average Daily Sales” is a medium-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with window functions, rolling average, date — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.
Concepts covered
- window functions
- rolling average
- date
How to approach it
If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:
- 1 ≤ number of rows ≤ 100,000
- sale_date values are valid dates in YYYY-MM-DD format
- sales_amount are positive decimal values
- Return columns: sale_date (DATE), rolling_avg_7_days (DECIMAL(10,2)), ordered by sale_date ASC
- Compute the average and a row number in one pass, then filter: SELECT sale_date, AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg7, ROW_NUMBER() OVER (ORDER BY sale_date) AS rn FROM sales A window function cannot be used in WHERE, so wrap this in a subquery or CTE and filter the outer query with rn >= 7. Round avg7 and alias it rolling_7day_avg.
How to practise it on PySpark.in
Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.
Related SQL challenges
- Top 3 Products per Category
- Running Total Revenue
- Median Salary per Department
- Latest Order Per Customer
- 3-Day Rolling Sum of Sales
- 7-Day Rolling Purchase Amount by Customer
Frequently asked questions
Do I need to install Spark or a database to solve this?
No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.
Is this challenge free?
Yes - the problem, the sample dataset, the hints and unlimited test runs are free.
What level is it?
It is rated medium and covers Window Functions.