Moving averages with window frames in SQL - Deep Dive

Overview - Moving averages with window frames

What is it?

Moving averages with window frames in SQL calculate the average of a set of values within a specific range or window around each row. This range can be defined by the number of rows before and after the current row or by a range of values like dates. It helps analyze trends over time or sequences without collapsing the data into groups.

Why it matters

Without moving averages, it is hard to see smooth trends in data that changes over time because raw data can be noisy or jumpy. Moving averages help reveal underlying patterns by averaging nearby values. This is useful in finance, sales, weather data, and many other fields where understanding trends is important.

Where it fits

Before learning moving averages with window frames, you should understand basic SQL SELECT queries, aggregate functions like AVG(), and the concept of window functions. After this, you can explore more advanced window functions, performance optimization, and time series analysis techniques.

Mental Model

Core Idea

A moving average with window frames calculates the average of values in a flexible, sliding range around each row without grouping the data.

Think of it like...

Imagine you are walking along a path and looking at the average height of the trees around you within a certain distance. As you move, the group of trees you consider shifts, giving you a smooth sense of how tree height changes along the path.

Row Number: 1  2  3  4  5  6  7
Values:     10 20 30 40 50 60 70
Window for row 4: [2,3,4,5,6]
Moving Avg:  (20+30+40+50+60)/5 = 40

Build-Up - 7 Steps

1

Foundation

Understanding basic SQL averages

Concept: Learn how the AVG() function calculates the average of a column over all rows.

The AVG() function adds all values in a column and divides by the number of rows. For example, SELECT AVG(sales) FROM orders; returns the average sales value across all orders.

Result

A single number representing the average of the selected column.

Understanding AVG() is essential because moving averages build on this concept but apply it to subsets of data dynamically.

2

Foundation

Introduction to window functions

Concept: Window functions perform calculations across a set of rows related to the current row without collapsing the result into one row.

Using OVER() with functions like ROW_NUMBER() or AVG() lets you calculate values per row considering other rows. For example, SELECT sales, AVG(sales) OVER () FROM orders; shows each sale and the overall average repeated for each row.

Result

Each row shows its value plus the overall average repeated.

Window functions let you keep all rows while calculating aggregates, which is the foundation for moving averages.

3

Intermediate

Defining window frames with ROWS

🤔Before reading on: Do you think ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING includes the current row? Commit to yes or no.

Concept: Window frames specify which rows around the current row to include in the calculation using ROWS BETWEEN clauses.

For example, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) calculates the average of the current row, one row before, and one row after, ordered by date.

Result

Each row's average includes itself and its immediate neighbors, smoothing the data.

Knowing how to define window frames precisely controls the range of data included, which is key to customizing moving averages.

4

Intermediate

Using RANGE frames for value-based windows

🤔Before reading on: Does RANGE BETWEEN 5 PRECEDING AND CURRENT ROW include rows with the same ordering value? Commit to yes or no.

Concept: RANGE frames define the window based on value ranges in the ORDER BY column, not just row counts.

For example, AVG(sales) OVER (ORDER BY date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) averages sales from the last 7 days up to the current date.

Result

Each row's average covers a time-based window, useful for irregular time intervals.

RANGE frames let you create windows based on actual data values, which is powerful for time series and uneven data.

5

Intermediate

Combining ORDER BY with window frames

Concept: ORDER BY inside OVER() defines the sequence for window frames, affecting which rows are included.

Without ORDER BY, window frames have no direction. For example, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) calculates a moving average over the last 3 rows by date.

Result

Moving averages follow the order of dates, showing trends over time.

ORDER BY is crucial because it sets the context for the window frame, making moving averages meaningful.

6

Advanced

Handling edge cases at data boundaries

🤔Before reading on: Do you think moving averages include fewer rows at the start or end of data? Commit to yes or no.

Concept: At the start or end of data, window frames may have fewer rows, affecting the average calculation.

For example, the first row with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW only includes itself because there are no preceding rows. This can cause averages to be based on fewer values.

Result

Moving averages near data edges may be less smooth or based on smaller samples.

Understanding boundary behavior helps interpret moving averages correctly and avoid misleading conclusions.

7

Expert

Performance considerations and optimization

🤔Before reading on: Do you think moving averages with large windows always run fast? Commit to yes or no.

Concept: Large window frames can slow queries because more rows are processed per calculation; indexing and partitioning help optimize performance.

Using PARTITION BY to limit data scope and indexes on ORDER BY columns speeds up moving average queries. Also, some databases optimize RANGE frames differently than ROWS frames.

Result

Well-optimized queries run faster and scale better on large datasets.

Knowing how databases execute window functions guides writing efficient moving average queries in production.

Under the Hood

Window functions compute results by scanning the dataset ordered as specified, then for each row, they identify the frame of rows defined by ROWS or RANGE clauses. The aggregate function (like AVG) is applied only to that frame. Internally, the database engine uses indexes and sorting to efficiently find frame boundaries and calculate aggregates without scanning the entire table repeatedly.

Why designed this way?

Window frames were designed to allow flexible, row-by-row aggregate calculations without collapsing rows, enabling trend analysis and complex analytics in SQL. Earlier SQL versions only supported GROUP BY aggregates, which lose row-level detail. Window frames balance expressiveness and performance by letting users define precise data subsets dynamically.

┌───────────────┐
│   Table Rows  │
├───────────────┤
│ Row 1         │
│ Row 2         │
│ Row 3         │
│ Row 4 (current)│
│ Row 5         │
│ Row 6         │
└───────────────┘
       ↓
Apply ORDER BY
       ↓
Identify window frame (e.g., ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
       ↓
Calculate AVG() over frame
       ↓
Return result for Row 4

Myth Busters - 4 Common Misconceptions

Quick: Does AVG() OVER() without ORDER BY produce a moving average? Commit to yes or no.

Common Belief: AVG() OVER() without ORDER BY gives a moving average over rows.

Reality: Without ORDER BY, AVG() OVER() calculates the average over the entire partition or table, not a moving average.

Why it matters: Misunderstanding this leads to wrong results because the order defines the moving window; missing it means no sliding effect.

Quick: Does RANGE frame always include the same number of rows as ROWS frame? Commit to yes or no.

Common Belief: RANGE and ROWS frames always cover the same number of rows.

Reality: RANGE frames include rows based on value ranges and can include more or fewer rows than ROWS frames, which count rows strictly.

Why it matters: Confusing these causes unexpected window sizes and incorrect moving averages, especially with duplicate or irregular data.

Quick: Can moving averages with window frames be used on unordered data? Commit to yes or no.

Common Belief: You can calculate moving averages without ordering the data.

Reality: Ordering is essential; without it, the concept of 'moving' or 'sliding' window has no meaning.

Why it matters: Ignoring order leads to meaningless averages that don't reflect trends or sequences.

Quick: Does the window frame always include the same number of rows at data edges? Commit to yes or no.

Common Belief: Window frames always include the full number of rows even at the start or end of data.

Reality: At data edges, window frames include fewer rows because preceding or following rows may not exist.

Why it matters: Not knowing this causes misinterpretation of moving averages near boundaries as less reliable or inconsistent.

Expert Zone

1

Some databases optimize ROWS frames better than RANGE frames, so choosing frame type affects performance.

2

When ORDER BY column has duplicates, RANGE frames may include unexpected rows, affecting moving average results subtly.

3

Partitioning data with PARTITION BY can isolate moving averages per group, but improper partitioning can cause incorrect calculations.

When NOT to use

Moving averages with window frames are not suitable when data is unordered or when you need aggregates that collapse rows, such as total sums per group. In those cases, GROUP BY or other aggregation methods are better. Also, for very large datasets with complex windows, specialized time series databases or pre-aggregated tables may be more efficient.

Production Patterns

In production, moving averages are often used with PARTITION BY to calculate per-category trends, combined with indexes on ORDER BY columns for speed. They are also used in dashboards for real-time trend visualization and in financial systems for smoothing price data. Query tuning and caching are common to handle large volumes.

Connections

Time Series Analysis

Moving averages with window frames implement a core technique used in time series analysis to smooth data and identify trends.

Understanding SQL window frames helps grasp how moving averages work in broader time series contexts, bridging database queries and statistical methods.

Signal Processing

Moving averages act like a low-pass filter in signal processing, smoothing out noise from data signals.

Recognizing this connection reveals that moving averages reduce short-term fluctuations, a principle shared across data fields.

Sliding Window Algorithms (Computer Science)

Moving averages with window frames are a form of sliding window algorithm that processes data in overlapping chunks.

Knowing this links SQL window functions to algorithmic patterns, highlighting efficiency and incremental computation concepts.

Common Pitfalls

#1 Using AVG() OVER() without ORDER BY for moving averages

Wrong approach:

SELECT date, sales, AVG(sales) OVER () AS moving_avg FROM sales_data;

Correct approach:

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

Root cause:

Not specifying ORDER BY means no defined order, so the window covers all rows, not a moving window.

#2 Confusing ROWS and RANGE frames leading to unexpected window sizes

Wrong approach:

SELECT date, sales, AVG(sales) OVER (ORDER BY date RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) FROM sales_data;

Correct approach:

SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM sales_data;

Root cause:

Using RANGE with non-numeric or non-unique ORDER BY values can include more rows than intended.

#3 Ignoring edge effects causing smaller windows at data start

Wrong approach:

SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM sales_data;

Correct approach:

SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM sales_data;

Root cause:

Not accounting for fewer preceding rows at the start leads to averages over fewer data points.

Key Takeaways

Moving averages with window frames calculate averages over a sliding range of rows defined dynamically per row.

ORDER BY inside the window function is essential to define the sequence for moving averages.

ROWS frames count rows strictly, while RANGE frames include rows based on value ranges, affecting window size.

Edge rows have smaller windows because preceding or following rows may not exist, impacting averages.

Performance depends on window size, indexing, and partitioning; understanding internals helps write efficient queries.

Practice

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