Window frame (ROWS BETWEEN, RANGE BETWEEN) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate a running total of sales ordered by date using ROWS BETWEEN.

PostgreSQL

SELECT date, sales, SUM(sales) OVER (ORDER BY date [1] UNBOUNDED PRECEDING) AS running_total FROM sales_data;

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

AGROUP

BRANGE

CROWS

DPARTITION

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE instead of ROWS causes unexpected results for running totals.

Omitting the frame clause leads to default framing which may not be cumulative.

The ROWS keyword specifies the window frame in terms of physical rows, which is suitable for running totals ordered by date.

2fill in blank

medium

Complete the code to calculate the average sales over the current row and the two preceding rows using ROWS BETWEEN.

PostgreSQL

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

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

ARANGE BETWEEN

BROWS BETWEEN

CGROUP BY

DPARTITION BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE BETWEEN 2 PRECEDING AND CURRENT ROW which behaves differently.

Forgetting to specify BETWEEN and AND keywords.

The ROWS BETWEEN clause defines the window frame as the current row and two rows before it, which is needed for this moving average.

3fill in blank

hard

Fix the error in the window frame clause to correctly calculate the sum over the last 7 days using RANGE BETWEEN.

PostgreSQL

SELECT date, sales, SUM(sales) OVER (ORDER BY date [1] INTERVAL '7 days' PRECEDING AND CURRENT ROW) AS weekly_sum FROM sales_data;

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

ARANGE BETWEEN

BROWS BETWEEN

CGROUP BY

DPARTITION BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROWS BETWEEN with INTERVAL causes syntax errors.

Omitting BETWEEN and AND keywords.

The RANGE BETWEEN clause is used with intervals like '7 days' to define a frame based on value ranges, suitable for date intervals.

4fill in blank

hard

Fill both blanks to calculate the cumulative sum of sales partitioned by region and ordered by date using ROWS BETWEEN.

PostgreSQL

SELECT region, date, sales, SUM(sales) OVER (PARTITION BY region ORDER BY date [1] BETWEEN UNBOUNDED PRECEDING [2] CURRENT ROW) AS cum_sum FROM sales_data;

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

AROWS

BRANGE

CAND

DOR

Attempts:

3 left

💡 Hint

Common Mistakes

Using OR instead of AND between frame boundaries.

Using RANGE instead of ROWS for physical row framing.

The window frame uses ROWS to specify physical rows and AND to separate the frame boundaries.

5fill in blank

hard

Fill all three blanks to calculate the moving average of sales over the current row and the previous 3 days using RANGE BETWEEN.

PostgreSQL

SELECT date, sales, AVG(sales) OVER (ORDER BY date [1] BETWEEN INTERVAL '3 days' PRECEDING [2] [3]) AS moving_avg FROM sales_data;

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

AROWS

BAND

CCURRENT ROW

DRANGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROWS with INTERVAL values causes errors.

Forgetting to use AND between frame boundaries.

Omitting CURRENT ROW as the upper frame boundary.

The window frame uses RANGE to specify value-based framing, AND to separate boundaries, and CURRENT ROW as the upper boundary.