Moving averages with window frames in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate a simple moving average of sales over the last 3 rows.

SQL

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

Drag options to blanks, or click blank then click option'

ABETWEEN

B3

CUNBOUNDED

DCURRENT ROW

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number instead of CURRENT ROW for the frame end.

Omitting the frame clause entirely.

The window frame clause uses CURRENT ROW to specify the current row as the end of the frame for the moving average.

2fill in blank

medium

Complete the code to calculate a moving average over the last 5 rows including the current row.

SQL

SELECT sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND [1]) AS moving_avg FROM sales_data;

Drag options to blanks, or click blank then click option'

ABETWEEN

BUNBOUNDED

CCURRENT ROW

DEXCLUDE

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNBOUNDED instead of CURRENT ROW for the frame end.

Using EXCLUDE which is not valid here.

The frame ends at CURRENT ROW and starts 4 rows before, covering 5 rows total.

3fill in blank

hard

Fix the error in the window frame clause to calculate a moving average over the last 3 rows.

SQL

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

Drag options to blanks, or click blank then click option'

ACURRENT ROW

B1 FOLLOWING

CUNBOUNDED PRECEDING

D3 PRECEDING

Attempts:

3 left

💡 Hint

Common Mistakes

Using 3 PRECEDING as the frame end.

Using UNBOUNDED PRECEDING which expands the frame too far.

The frame should end at CURRENT ROW to include the current row and the two preceding rows.

4fill in blank

hard

Fill both blanks to calculate a moving average over the current row and the 2 preceding rows.

SQL

SELECT sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN [1] AND [2]) AS moving_avg FROM sales_data;

Drag options to blanks, or click blank then click option'

A2 PRECEDING

BCURRENT ROW

C1 PRECEDING

DUNBOUNDED PRECEDING

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing the frame start and end.

Using UNBOUNDED PRECEDING as the frame end.

The frame starts 2 rows before and ends at the current row to cover 3 rows total.

5fill in blank

hard

Fill all three blanks to calculate a moving average over the current row and the 4 preceding rows, excluding the current row.

SQL

SELECT sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN [1] AND [2] EXCLUDE [3]) AS moving_avg FROM sales_data;

Drag options to blanks, or click blank then click option'

A4 PRECEDING

BCURRENT ROW

CNO OTHERS

DGROUP

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXCLUDE GROUP which excludes more rows.

Not excluding the current row when required.

The frame covers 4 rows before the current row and excludes the current row itself using EXCLUDE CURRENT ROW.