Bird
Raised Fist0
SQLquery~5 mins

Moving averages with window frames in SQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction

Moving averages help you see trends by smoothing out data over time. Window frames let you control which rows to include in the average.

To find the average sales over the last 3 days for each day in a sales report.
To smooth out stock prices to see general trends without daily ups and downs.
To calculate the average temperature over the past week for each day in weather data.
To analyze average customer ratings over recent reviews in an app.
To monitor average website visits over the last 7 days for each day.
Syntax
SQL
SELECT column1, column2, 
       AVG(target_column) OVER (ORDER BY order_column ROWS BETWEEN frame_start AND frame_end) AS moving_average
FROM table_name;

ORDER BY inside OVER() defines the order of rows for the moving average.

ROWS BETWEEN defines the window frame, like how many rows before and after to include.

Examples
This calculates the average sales for the current day and the two days before it, making a 3-day moving average.
SQL
SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_days
FROM sales_data;
This calculates the average sales for the 4 days before yesterday, excluding the current day.
SQL
SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS moving_avg_prev_4_days
FROM sales_data;
This calculates the average sales from the first day up to the current day (cumulative average).
SQL
SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg
FROM sales_data;
Sample Program

This example creates a simple sales table with 5 days of sales. It then calculates a 3-day moving average for each day, including the current day and the two days before.

SQL
CREATE TABLE sales_data (
  day INT,
  sales INT
);

INSERT INTO sales_data (day, sales) VALUES
(1, 100),
(2, 150),
(3, 200),
(4, 130),
(5, 170);

SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_days
FROM sales_data
ORDER BY day;
OutputSuccess
Important Notes

If there are fewer rows than the frame size at the start, the average uses only available rows.

You can use RANGE instead of ROWS for value-based frames, but ROWS is simpler for beginners.

Always include ORDER BY inside the window function to define the sequence.

Summary

Moving averages smooth data by averaging values over a sliding window.

Window frames define which rows to include in the average relative to the current row.

Use ROWS BETWEEN with PRECEDING and CURRENT ROW to control the frame size.

Practice

(1/5)
1. What does the SQL clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW do in a window function?
easy
A. It calculates the function over the current row and the two rows before it.
B. It calculates the function over the current row and the next two rows.
C. It calculates the function over all rows in the table.
D. It calculates the function only on the current row.

Solution

  1. Step 1: Understand the window frame boundaries

    The clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the frame includes the current row and the two rows before it.

  2. Step 2: Apply to window function calculation

    The window function (like AVG) will compute its result using values from these three rows.

  3. Final Answer:

    It calculates the function over the current row and the two rows before it. -> Option A

  4. Quick Check:

    Window frame includes current + 2 preceding rows = C [OK]
Hint: Preceding means rows before current, including current [OK]
Common Mistakes:
  • Confusing PRECEDING with FOLLOWING
  • Thinking it includes future rows
  • Assuming it covers entire table
2. Which of the following is the correct syntax to calculate a 3-row moving average of the column sales ordered by date?
easy
A. SELECT date, AVG(sales) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;
B. SELECT date, AVG(sales) OVER (PARTITION BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;
C. SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS moving_avg FROM sales_data;
D. SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

Solution

  1. Step 1: Identify correct window function syntax

    The correct syntax requires OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to define ordering and frame.

  2. Step 2: Check each option

    SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; uses ORDER BY with correct frame. SELECT date, AVG(sales) OVER (PARTITION BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; wrongly uses PARTITION BY instead of ORDER BY. SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS moving_avg FROM sales_data; uses FOLLOWING which looks ahead, not behind. SELECT date, AVG(sales) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; misses ORDER BY clause.

  3. Final Answer:

    SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; -> Option D

  4. Quick Check:

    ORDER BY + ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = D [OK]
Hint: Use ORDER BY with ROWS BETWEEN for moving averages [OK]
Common Mistakes:
  • Using PARTITION BY instead of ORDER BY for moving average
  • Omitting ORDER BY clause
  • Using FOLLOWING instead of PRECEDING
3. Given the table sales_data with columns date and amount, what is the output of this query?
SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data ORDER BY date;

Assume data:
date | amount
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 300
medium
A. [{"date": "2024-01-01", "amount": 100, "moving_avg": 150}, {"date": "2024-01-02", "amount": 200, "moving_avg": 200}, {"date": "2024-01-03", "amount": 300, "moving_avg": 300}]
B. [{"date": "2024-01-01", "amount": 100, "moving_avg": 100}, {"date": "2024-01-02", "amount": 200, "moving_avg": 150}, {"date": "2024-01-03", "amount": 300, "moving_avg": 250}]
C. [{"date": "2024-01-01", "amount": 100, "moving_avg": 100}, {"date": "2024-01-02", "amount": 200, "moving_avg": 200}, {"date": "2024-01-03", "amount": 300, "moving_avg": 300}]
D. [{"date": "2024-01-01", "amount": 100, "moving_avg": null}, {"date": "2024-01-02", "amount": 200, "moving_avg": 150}, {"date": "2024-01-03", "amount": 300, "moving_avg": 250}]

Solution

  1. Step 1: Understand the window frame

    The frame includes the current row and 1 preceding row, so for each row, average the amount of that row and the previous one.

  2. Step 2: Calculate moving averages row by row

    Row 1 (2024-01-01): only itself, avg = 100.
    Row 2 (2024-01-02): rows 1 and 2, avg = (100+200)/2 = 150.
    Row 3 (2024-01-03): rows 2 and 3, avg = (200+300)/2 = 250.

  3. Final Answer:

    [{"date": "2024-01-01", "amount": 100, "moving_avg": 100}, {"date": "2024-01-02", "amount": 200, "moving_avg": 150}, {"date": "2024-01-03", "amount": 300, "moving_avg": 250}] -> Option B

  4. Quick Check:

    Moving average includes current + 1 preceding row = A [OK]
Hint: Average current and previous row amounts for moving average [OK]
Common Mistakes:
  • Including future rows instead of preceding
  • Calculating average without window frame
  • Assuming null for first row moving average
4. Identify the error in this SQL query for calculating a moving average:
SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) AS moving_avg FROM sales_data;
medium
A. AVG() cannot be used as a window function.
B. The ORDER BY clause is missing.
C. The window frame boundaries are in the wrong order; PRECEDING must come before CURRENT ROW.
D. The query is correct and will run without errors.

Solution

  1. Step 1: Check window frame syntax

    The frame clause ROWS BETWEEN CURRENT ROW AND 2 PRECEDING is invalid because the start boundary must be before the end boundary.

  2. Step 2: Correct the order of boundaries

    The correct syntax is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, with the smaller offset first.

  3. Final Answer:

    The window frame boundaries are in the wrong order; PRECEDING must come before CURRENT ROW. -> Option C

  4. Quick Check:

    Window frame start must be before end = A [OK]
Hint: Start frame boundary must be before end boundary [OK]
Common Mistakes:
  • Reversing PRECEDING and CURRENT ROW order
  • Thinking AVG() can't be window function
  • Ignoring window frame syntax rules
5. You want to calculate a 5-day moving average of daily sales, but only include days where sales were above 100. Which SQL query correctly applies this condition inside the moving average calculation?
hard
A. SELECT date, sales, AVG(CASE WHEN sales > 100 THEN sales ELSE NULL END) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;
B. SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) WHERE sales > 100 FROM sales_data;
C. SELECT date, sales, AVG(sales) FILTER (WHERE sales > 100) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;
D. SELECT date, sales, AVG(sales) OVER (PARTITION BY sales > 100 ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

Solution

  1. Step 1: Understand filtering inside window functions

    To include only sales > 100 in the average, use a CASE expression inside AVG to nullify unwanted rows.

  2. Step 2: Evaluate each option

    SELECT date, sales, AVG(CASE WHEN sales > 100 THEN sales ELSE NULL END) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; uses CASE to include sales > 100, replacing others with NULL (ignored by AVG). SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) WHERE sales > 100 FROM sales_data; filters rows after window function (invalid syntax). SELECT date, sales, AVG(sales) FILTER (WHERE sales > 100) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; uses FILTER clause which is not standard in all SQL versions. SELECT date, sales, AVG(sales) OVER (PARTITION BY sales > 100 ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; partitions by boolean condition, which changes grouping incorrectly.

  3. Final Answer:

    SELECT date, sales, AVG(CASE WHEN sales > 100 THEN sales ELSE NULL END) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; -> Option A

  4. Quick Check:

    Use CASE inside AVG to filter rows in window [OK]
Hint: Use CASE inside AVG to filter values in window frame [OK]
Common Mistakes:
  • Filtering rows outside window function
  • Using PARTITION BY for filtering condition
  • Assuming FILTER clause always works
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard