Practical window function patterns in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the running total of sales ordered by date.

PostgreSQL

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

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

Acustomer_id

Bsales

Cdate

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by sales instead of date causes incorrect running totals.

Using a non-date column for ordering breaks the chronological sum.

The running total needs to be ordered by the date column to accumulate sales in chronological order.

2fill in blank

medium

Complete the code to assign a rank to each employee based on their salary in descending order.

PostgreSQL

SELECT employee_id, salary, RANK() OVER (ORDER BY salary [1]) AS salary_rank FROM employees;

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

AASC

BNULLS FIRST

CRAND()

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Using ASC ranks lowest salary first, which is incorrect here.

Using RAND() or NULLS FIRST causes syntax errors.

Ranking employees by salary from highest to lowest requires DESC order.

3fill in blank

hard

Fix the error in the code to calculate the difference between each sale and the previous sale.

PostgreSQL

SELECT sale_id, sale_amount, sale_amount - LAG([1]) OVER (ORDER BY sale_date) AS diff FROM sales;

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

Asale_date

Bsale_amount

Csale_id

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using sale_date inside LAG causes type errors.

Using sale_id or customer_id gives meaningless differences.

The LAG function should reference the sale_amount column to compare current and previous sales.

4fill in blank

hard

Fill both blanks to calculate the cumulative average sales per region ordered by date.

PostgreSQL

SELECT region, date, sales, AVG([1]) OVER (PARTITION BY [2] ORDER BY date) AS cum_avg FROM sales_data;

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

Asales

Bdate

Cregion

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by date or customer_id breaks grouping by region.

Averaging date or region columns causes errors.

The cumulative average is calculated on sales partitioned by region.

5fill in blank

hard

Fill all three blanks to assign a row number per department ordered by hire date, and show the first hired employee per department.

PostgreSQL

SELECT department, employee_name, hire_date, rn FROM (SELECT department, employee_name, hire_date, ROW_NUMBER() OVER (PARTITION BY [1] ORDER BY [2] [3]) AS rn FROM employees) AS t WHERE rn = 1;

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

Adepartment

Bhire_date

CASC

Demployee_name

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by employee_name or descending order gives wrong first employee.

Partitioning by hire_date or employee_name is incorrect.

Row numbers are assigned per department, ordered by hire_date ascending to get the earliest hire.