Moving averages with window frames in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate 3-day moving average using ROWS frame

Given a sales table with columns sale_date and amount, what is the output of this query?

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

SQL

CREATE TABLE sales (sale_date DATE, amount INT);
INSERT INTO sales VALUES
('2024-01-01', 100),
('2024-01-02', 200),
('2024-01-03', 300),
('2024-01-04', 400),
('2024-01-05', 500);

A [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": 100.0}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": 150.0}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 200.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 300.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 400.0}]

B [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": 100.0}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": 200.0}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 300.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 400.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 500.0}]

C [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": 100.0}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": 150.0}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 300.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 400.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 500.0}]

D [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": null}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": null}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 200.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 300.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 400.0}]

💡 Hint

Think about how ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes the current row and two before it.

The window frame includes the current row and the two previous rows, so the average is calculated over up to 3 rows. For the first row, only one row exists, so average is 100. For the second row, average of 100 and 200 is 150, and so on.

❓ query_result

intermediate

Calculate 3-day moving average using RANGE frame

Given the same sales table, what is the output of this query?

SELECT sale_date, amount, 
       AVG(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales
ORDER BY sale_date;

SQL

CREATE TABLE sales (sale_date DATE, amount INT);
INSERT INTO sales VALUES
('2024-01-01', 100),
('2024-01-02', 200),
('2024-01-03', 300),
('2024-01-04', 400),
('2024-01-05', 500);

A [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": null}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": null}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 200.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 300.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 400.0}]

B [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": 150.0}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": 150.0}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 300.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 400.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 500.0}]

C [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": 100.0}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": 200.0}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 300.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 400.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 500.0}]

D [{"sale_date": "2024-01-01", "amount": 100, "moving_avg": 100.0}, {"sale_date": "2024-01-02", "amount": 200, "moving_avg": 150.0}, {"sale_date": "2024-01-03", "amount": 300, "moving_avg": 200.0}, {"sale_date": "2024-01-04", "amount": 400, "moving_avg": 300.0}, {"sale_date": "2024-01-05", "amount": 500, "moving_avg": 400.0}]

💡 Hint

RANGE frame includes rows within 2 days before the current sale_date.

The RANGE frame includes all rows with sale_date within 2 days before and including the current row's date. Since dates are consecutive, this matches the last 3 days for each row, similar to ROWS frame here.

📝 Syntax

advanced

Identify the syntax error in window frame clause

Which option contains a syntax error in the window frame clause of this query?

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

A ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

B ROWS BETWEEN CURRENT ROW AND 2 PRECEDING

C ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

D ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

💡 Hint

The frame must start with a lower boundary and end with a higher boundary.

The syntax requires the frame to be defined from an earlier row to a later row. 'CURRENT ROW AND 2 PRECEDING' reverses the order and is invalid.

❓ optimization

advanced

Optimize moving average calculation for large dataset

You have a large sales table with millions of rows. Which approach is most efficient to calculate a 7-day moving average of amount ordered by sale_date?

A Use AVG(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW)

B Use AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

C Use a correlated subquery to calculate average for each row

D Use a self join on sale_date between current date and 6 days before and aggregate

💡 Hint

Consider how the data is distributed and the type of frame that matches date intervals.

RANGE frame with INTERVAL '6' DAY PRECEDING matches the date range exactly and can use indexes efficiently. ROWS frame counts rows, which may not correspond to days if dates are missing. Correlated subqueries and self joins are slower for large data.

🧠 Conceptual

expert

Understanding difference between ROWS and RANGE frames

Which statement correctly explains the difference between ROWS and RANGE window frames in moving average calculations?

A ROWS frame can only be used with numeric ORDER BY; RANGE frame only with date ORDER BY.

B ROWS frame includes all rows with values within a specified range; RANGE frame counts physical rows before the current row.

C ROWS frame counts physical rows before the current row; RANGE frame includes all rows with values within a specified range of the ORDER BY expression.

D ROWS and RANGE frames behave identically when ORDER BY is on a numeric column.

💡 Hint

Think about how each frame defines the window based on position vs value.

ROWS frame defines the window by counting a fixed number of rows before and after the current row. RANGE frame defines the window by including all rows with ORDER BY values within a specified range relative to the current row's value.