Why window functions are powerful in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the running total of sales.

PostgreSQL

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

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

AWHERE sales > 0

BGROUP BY date

CROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

DPARTITION BY date

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside the OVER clause.

Trying to filter rows with WHERE inside the window function.

The clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines the window frame for the running total.

2fill in blank

medium

Complete the code to rank employees by their sales within each department.

PostgreSQL

SELECT employee, department, sales, RANK() OVER ([1] department ORDER BY sales DESC) AS sales_rank FROM employee_sales;

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

APARTITION BY

BORDER BY

CGROUP BY

DWHERE

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY instead of PARTITION BY inside the OVER clause.

Placing ORDER BY inside the OVER clause without PARTITION BY.

PARTITION BY divides the data into groups (departments) for ranking.

3fill in blank

hard

Fix the error in the code to calculate the difference between current and previous sales.

PostgreSQL

SELECT date, sales, sales - LAG(sales) OVER (ORDER BY [1]) AS sales_diff FROM sales_data;

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

Adate

Bdepartment

Cemployee

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by sales instead of date.

Using a column unrelated to the sequence of rows.

The ORDER BY inside the window function should use the date to get the previous row correctly.

4fill in blank

hard

Fill both blanks to calculate the average sales per department and show the difference from each sale.

PostgreSQL

SELECT employee, department, sales, AVG(sales) OVER ([1] department [2]) AS avg_dept_sales FROM employee_sales;

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

APARTITION BY

BORDER BY sales

CORDER BY date

DROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Attempts:

3 left

💡 Hint

Common Mistakes

Using ORDER BY sales instead of a window frame.

Omitting the PARTITION BY clause.

PARTITION BY department groups sales by department, and the window frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING defines the range for averaging.

5fill in blank

hard

Fill all three blanks to calculate cumulative sales per department ordered by date.

PostgreSQL

SELECT employee, department, sales, SUM(sales) OVER ([1] department [2] [3]) AS cumulative_sales FROM employee_sales;

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

APARTITION BY

BORDER BY date

CROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

DGROUP BY department

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY inside OVER clause.

Omitting the window frame clause.

The PARTITION BY department groups rows, ORDER BY date orders them, and the window frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines the cumulative range.