Moving averages with window frames in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a moving average in SQL?

A moving average calculates the average of a set of values over a specific range or window that moves through the data, helping to smooth out short-term fluctuations.

beginner

What does the OVER() clause do in a moving average query?

The OVER() clause defines the window or set of rows the function operates on, allowing calculation of moving averages over a specific range of rows.

intermediate

Explain the difference between ROWS BETWEEN and RANGE BETWEEN in window frames.

ROWS BETWEEN counts physical rows before and after the current row, while RANGE BETWEEN counts rows based on value ranges in the ordering column.

intermediate

Write a simple SQL query to calculate a 3-day moving average of sales ordered by date.

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

intermediate

Why use moving averages with window frames instead of grouping data?

Moving averages with window frames keep all rows and calculate averages dynamically over a sliding window, unlike grouping which aggregates rows into fewer results.

Which SQL clause defines the window for a moving average calculation?

A HAVING

B GROUP BY

C WHERE

D OVER()

The OVER() clause specifies the window or range of rows for window functions like moving averages.

What does ROWS BETWEEN 2 PRECEDING AND CURRENT ROW mean in a window frame?

A Include only the current row

B Include the current row and the two rows after it

C Include the current row and the two rows before it

D Include all rows in the table

It means the window includes the current row plus the two rows before it.

Which function is commonly used to calculate moving averages in SQL?

A SUM()

B AVG()

C COUNT()

D MAX()

AVG() calculates the average, which is used for moving averages.

What is the main benefit of using window functions for moving averages?

A They allow calculations without collapsing rows

B They reduce the number of rows returned

C They sort the data automatically

D They filter rows based on conditions

Window functions calculate values over a set of rows but keep all original rows intact.

If you want a moving average over the last 7 days including today, which window frame would you use?

A ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

B ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING

C RANGE BETWEEN 6 PRECEDING AND CURRENT ROW

D GROUP BY date

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current day and six days before, totaling 7 days.

Describe how to calculate a moving average using SQL window functions.

Think about how to tell SQL which rows to include for each average.

Explain the difference between ROWS and RANGE in window frames for moving averages.

Consider how the window moves when data has duplicates or gaps.