Calculate a 7-Observed-Day Moving Average
Calculate daily revenue and its moving average across the current and six preceding observed order dates.
- Window functions
- Subqueries
- Aggregation
- Date analysis
- Numeric functions
Exercise brief
Understand the request
Business analytics lead An analyst wants a smoothed trend across the most recent seven populated order dates.
An analyst wants a smoothed trend across the most recent seven populated order dates. Calculate daily revenue and its moving average across the current and six preceding observed order dates.
Return
- Return order_date, daily_revenue, rolling_avg_7days in this exact left-to-right order.
Constraints
- Aggregate to one row per observed order_date first.
- Use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
- Do not describe the result as seven consecutive calendar days.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERorder_dateDATEorder_totalDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A moving average needs an explicit ROWS frame: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
Hint 2
Without the frame, the default RANGE may average ties together and ignore your window size.
Hint 3
This is a 7-ROW average (current + 6 prior data rows), not a 7-calendar-day average.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH daily_sales AS (SELECT order_date, SUM(order_total) AS daily_revenue FROM orders WHERE strftime('%Y', order_date) = '2024' GROUP BY order_date) SELECT order_date, daily_revenue, ROUND(AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS rolling_avg_7days FROM daily_sales ORDER BY order_date;Why this works
Moving averages demand an explicit ROWS frame; the default frame (RANGE UNBOUNDED PRECEDING) computes a running total, not a window. Note the subtlety: '7-row' averages the last 7 rows that have data, which equals '7 calendar days' only when every day has a sale. For a true calendar window, use RANGE BETWEEN INTERVAL '6 days' PRECEDING on engines that support it.
Success check
The window contains at most seven populated date rows and remains deterministic on sparse data.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_date | daily_revenue | rolling_avg_7days |
|---|---|---|
| 2024-01-15 | 250 | 250 |
| 2024-01-20 | 180 | 215 |
| 2024-02-10 | 320 | 250 |
| 2024-02-25 | 150 | 225 |
| 2024-03-05 | 420 | 264 |
| 2024-03-15 | 280 | 266.67 |
| 2024-04-01 | 190 | 255.71 |
| 2024-04-10 | 350 | 270 |
| 2024-05-05 | 220 | 275.71 |
| 2024-05-20 | 380 | 284.29 |
Previewing 10 of 18 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.