Window frame specification (ROWS BETWEEN) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to specify the window frame to include the current row and the two preceding rows.

SQL

SELECT sales, SUM(sales) OVER (ORDER BY date ROWS BETWEEN [1] PRECEDING AND CURRENT ROW) AS running_total FROM sales_data;

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

A3

B1

C2

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using '1 PRECEDING' which includes only one row before the current row.

Using '0 PRECEDING' which includes only the current row.

The window frame specifies to include the current row and the two rows before it, so '2 PRECEDING' is correct.

2fill in blank

medium

Complete the code to specify the window frame to include the current row and the next three rows.

SQL

SELECT sales, SUM(sales) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND [1] FOLLOWING) AS future_total FROM sales_data;

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

A3

B1

C2

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using '2 FOLLOWING' which includes only two rows after the current row.

Using '4 FOLLOWING' which includes more rows than needed.

The window frame includes the current row and the next three rows, so '3 FOLLOWING' is correct.

3fill in blank

hard

Fix the error in the window frame clause to correctly specify the frame from two rows before to one row after the current row.

SQL

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

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

A1

B3

C2

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using '1 PRECEDING' which includes only one row before the current row.

Using '0 PRECEDING' which includes only the current row.

Using '3 FOLLOWING' which includes more rows after than needed.

The frame should start two rows before ('2 PRECEDING', C) and end one row after the current row ('1 FOLLOWING', A).

4fill in blank

hard

Fill in the blank to specify a window frame that includes the current row and the five preceding rows.

SQL

SELECT sales, SUM(sales) OVER (ORDER BY date ROWS BETWEEN [1] PRECEDING AND CURRENT ROW) AS total FROM sales_data;

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

A5

B4

C6

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using a smaller number like 3 which includes fewer rows.

Using a larger number like 6 which includes more rows than needed.

The frame starts 5 rows before and ends at the current row, so '5 PRECEDING' is correct.

5fill in blank

hard

Fill all three blanks to create a window frame that starts three rows before, ends two rows after the current row, and orders by the 'transaction_date' column.

SQL

SELECT sales, SUM(sales) OVER (ORDER BY [1] ROWS BETWEEN [2] PRECEDING AND [3] FOLLOWING) AS total_sales FROM sales_data;

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

Atransaction_date

B3

C2

Dsales_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong column name like 'sales_date'.

Swapping the numbers for PRECEDING and FOLLOWING.

The ORDER BY column is 'transaction_date', the frame starts 3 rows before and ends 2 rows after the current row.