Named windows with WINDOW clause in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a named window called w1 that orders rows by the column 'date'.

PostgreSQL

SELECT *, ROW_NUMBER() OVER w1 FROM sales [1];

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

AWINDOW w1 AS (ORDER BY date)

BWINDOW w1 PARTITION BY date

CORDER BY w1

DPARTITION BY w1

Attempts:

3 left

💡 Hint

Common Mistakes

Using PARTITION BY instead of ORDER BY in the WINDOW clause.

Trying to use WINDOW clause inside OVER without AS keyword.

The WINDOW clause defines a named window 'w1' with ordering by 'date'. This allows reuse of the window specification.

2fill in blank

medium

Complete the code to use the named window 'w1' in the ROW_NUMBER() function.

PostgreSQL

SELECT *, ROW_NUMBER() OVER [1] FROM sales WINDOW w1 AS (ORDER BY date);

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

A(PARTITION BY w1)

B(WINDOW w1)

C(ORDER BY w1)

D(w1)

Attempts:

3 left

💡 Hint

Common Mistakes

Adding PARTITION BY or WINDOW keywords inside OVER() when using a named window.

Using ORDER BY inside OVER() when the window is already defined.

To use a named window, just put its name inside the OVER() clause without extra keywords.

3fill in blank

hard

Fix the error in the WINDOW clause that tries to define a window named w2 partitioned by 'region' and ordered by 'sales'.

PostgreSQL

SELECT *, SUM(sales) OVER w2 FROM sales WINDOW w2 AS [1];

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

A(ORDER BY region PARTITION BY sales)

B(PARTITION region BY sales)

C(PARTITION BY region ORDER BY sales)

D(PARTITION BY sales ORDER BY region)

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the BY keyword after PARTITION.

Swapping the order of PARTITION BY and ORDER BY clauses.

The correct syntax for partitioning is PARTITION BY column_name. The original code missed the BY keyword.

4fill in blank

hard

Fill both blanks to define two named windows: w1 partitions by 'category', w2 orders by 'date'.

PostgreSQL

SELECT *, SUM(amount) OVER w1, ROW_NUMBER() OVER w2 FROM transactions WINDOW w1 AS ([1]), w2 AS ([2]);

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

APARTITION BY category

BORDER BY category

CORDER BY date

DPARTITION BY date

Attempts:

3 left

💡 Hint

Common Mistakes

Using ORDER BY in w1 instead of PARTITION BY.

Using PARTITION BY in w2 instead of ORDER BY.

w1 partitions rows by 'category', so it uses PARTITION BY category. w2 orders rows by 'date', so it uses ORDER BY date.

5fill in blank

hard

Fill all three blanks to define a named window w3 that partitions by 'department', orders by 'salary', and uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

PostgreSQL

SELECT *, SUM(salary) OVER w3 FROM employees WINDOW w3 AS ([1] [2] [3]);

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

APARTITION BY department

BORDER BY salary

CROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

DRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE instead of ROWS in the frame clause.

Omitting PARTITION BY or ORDER BY clauses.

Placing frame clause before ORDER BY.

The window w3 partitions by 'department', orders by 'salary', and defines the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for cumulative sum.