Practical window function patterns in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate running total of sales per store

Given a table sales with columns store_id, sale_date, and amount, what is the running total of sales per store ordered by sale_date?

PostgreSQL

SELECT store_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS running_total
FROM sales
ORDER BY store_id, sale_date;

A [{"store_id":1,"sale_date":"2023-01-01","amount":100,"running_total":100},{"store_id":1,"sale_date":"2023-01-02","amount":150,"running_total":250},{"store_id":2,"sale_date":"2023-01-01","amount":200,"running_total":200},{"store_id":2,"sale_date":"2023-01-03","amount":100,"running_total":300}]

B [{"store_id":1,"sale_date":"2023-01-01","amount":100,"running_total":100},{"store_id":1,"sale_date":"2023-01-02","amount":150,"running_total":150},{"store_id":2,"sale_date":"2023-01-01","amount":200,"running_total":200},{"store_id":2,"sale_date":"2023-01-03","amount":100,"running_total":100}]

C ]}003:"latot_gninnur",001:"tnuoma","30-10-3202":"etad_elas",2:"di_erots"{,}002:"latot_gninnur",002:"tnuoma","10-10-3202":"etad_elas",2:"di_erots"{,}052:"latot_gninnur",051:"tnuoma","20-10-3202":"etad_elas",1:"di_erots"{,}001:"latot_gninnur",001:"tnuoma","10-10-3202":"etad_elas",1:"di_erots"{[

D [{"store_id":1,"sale_date":"2023-01-01","amount":100,"running_total":100},{"store_id":1,"sale_date":"2023-01-02","amount":150,"running_total":250},{"store_id":2,"sale_date":"2023-01-01","amount":200,"running_total":200},{"store_id":2,"sale_date":"2023-01-03","amount":100,"running_total":100}]

💡 Hint

Think about how the SUM window function accumulates values within each store partition ordered by date.

The SUM() window function with PARTITION BY store_id and ORDER BY sale_date calculates a running total of sales per store in date order. Option A correctly shows cumulative sums per store.

❓ query_result

intermediate

Find the rank of employees by salary within each department

Given a table employees with columns department_id, employee_name, and salary, which query returns the rank of each employee's salary within their department, with the highest salary ranked 1?

PostgreSQL

SELECT department_id, employee_name, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY department_id, salary_rank;

A [{"department_id":10,"employee_name":"Alice","salary":90000,"salary_rank":1},{"department_id":10,"employee_name":"Bob","salary":85000,"salary_rank":1},{"department_id":20,"employee_name":"Carol","salary":95000,"salary_rank":1},{"department_id":20,"employee_name":"Dave","salary":90000,"salary_rank":1}]

B [{"department_id":10,"employee_name":"Alice","salary":90000,"salary_rank":1},{"department_id":10,"employee_name":"Bob","salary":85000,"salary_rank":2},{"department_id":20,"employee_name":"Carol","salary":95000,"salary_rank":1},{"department_id":20,"employee_name":"Dave","salary":90000,"salary_rank":2}]

C [{"department_id":10,"employee_name":"Alice","salary":90000,"salary_rank":2},{"department_id":10,"employee_name":"Bob","salary":85000,"salary_rank":1},{"department_id":20,"employee_name":"Carol","salary":95000,"salary_rank":2},{"department_id":20,"employee_name":"Dave","salary":90000,"salary_rank":1}]

D [{"department_id":10,"employee_name":"Alice","salary":90000,"salary_rank":1},{"department_id":10,"employee_name":"Bob","salary":85000,"salary_rank":3},{"department_id":20,"employee_name":"Carol","salary":95000,"salary_rank":1},{"department_id":20,"employee_name":"Dave","salary":90000,"salary_rank":2}]

💡 Hint

Use RANK() with PARTITION BY department and ORDER BY salary descending.

RANK() assigns ranks starting at 1 for the highest salary per department. Option B correctly ranks employees by salary within each department.

📝 Syntax

advanced

Identify the syntax error in this window function query

Which option contains a syntax error when trying to calculate the moving average of sales over the last 3 days per store?

PostgreSQL

SELECT store_id, sale_date, amount,
       AVG(amount) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

A

SELECT store_id, sale_date, amount,
       AVG(amount) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

B

SELECT store_id, sale_date, amount,
       AVG(amount) OVER (PARTITION BY store_id ORDER BY sale_date RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

C

SELECT store_id, sale_date, amount,
       AVG(amount) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) AS moving_avg
FROM sales;

D

SELECT store_id, sale_date, amount,
       AVG(amount) OVER (PARTITION BY store_id ORDER BY sale_date ROWS 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

💡 Hint

Check the syntax of the ROWS clause in the window frame specification.

Option D is missing the keyword BETWEEN before the frame boundaries, causing a syntax error. The correct syntax requires 'ROWS BETWEEN x PRECEDING AND y CURRENT ROW'.

❓ optimization

advanced

Optimize query to find first and last sale date per customer

Which query is the most efficient to get the first and last sale date per customer from a sales table with many rows?

A

SELECT DISTINCT customer_id,
       FIRST_VALUE(sale_date) OVER (PARTITION BY customer_id ORDER BY sale_date) AS first_sale,
       LAST_VALUE(sale_date) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale
FROM sales;

B

SELECT customer_id, sale_date AS first_sale, sale_date AS last_sale
FROM sales
WHERE sale_date IN (SELECT MIN(sale_date) FROM sales WHERE customer_id = sales.customer_id
                    UNION
                    SELECT MAX(sale_date) FROM sales WHERE customer_id = sales.customer_id);

C

SELECT customer_id, MIN(sale_date) AS first_sale, MAX(sale_date) AS last_sale
FROM sales
GROUP BY customer_id;

D

SELECT customer_id, sale_date AS first_sale, sale_date AS last_sale
FROM sales
WHERE sale_date = (SELECT MIN(sale_date) FROM sales WHERE customer_id = sales.customer_id);

💡 Hint

Consider the cost of window functions versus aggregate functions on large datasets.

Option C uses simple aggregation with GROUP BY, which is more efficient than window functions or correlated subqueries for large tables. Options A, C, and D are less efficient due to window function overhead or multiple subqueries.

🧠 Conceptual

expert

Understanding difference between ROWS and RANGE in window frames

What is the key difference between using ROWS BETWEEN 1 PRECEDING AND CURRENT ROW and RANGE BETWEEN 1 PRECEDING AND CURRENT ROW in a window function ordered by a numeric column?

A ROWS counts physical rows before the current row, while RANGE includes all rows with values within the numeric range, possibly including multiple rows with the same value.

B ROWS includes all rows with values within the numeric range, while RANGE counts physical rows before the current row.

C ROWS and RANGE behave identically when ordering by a numeric column.

D ROWS excludes the current row, while RANGE includes the current row in the frame.

💡 Hint

Think about how physical row count differs from value-based range in window frames.

ROWS defines the frame by counting a fixed number of physical rows relative to the current row. RANGE defines the frame by including all rows with values within a specified range of the current row's ordering value, which can include multiple rows with the same value.

Practice

1. What does the ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) window function do in a query?

easy

A. Counts the number of employees in each department.

B. Assigns a unique rank to each employee within their department based on salary, starting at 1 for the highest salary.

C. Calculates the total salary for each department.

D. Returns the average salary of all employees.

Solution

1. Step 1: Understand ROW_NUMBER() with PARTITION BY

   The function assigns a unique sequential number to rows within each department group.

2. Step 2: ORDER BY salary DESC ranks highest salary first

   Ordering by salary descending means the highest salary gets row number 1 in each department.

3. Final Answer:

   Assigns a unique rank to each employee within their department based on salary, starting at 1 for the highest salary. -> Option B

4. Quick Check:

   ROW_NUMBER() with PARTITION BY and ORDER BY = ranked rows [OK]

Hint: ROW_NUMBER() numbers rows per group ordered by criteria [OK]

Common Mistakes:

• Confusing ROW_NUMBER() with COUNT() or SUM()
• Ignoring PARTITION BY and thinking it ranks globally
• Assuming it calculates totals or averages

2. Which of the following is the correct syntax to calculate a running total of sales ordered by date using window functions in PostgreSQL?

easy

A. SELECT date, sales, SUM(sales) PARTITION BY date FROM sales_data;

B. SELECT date, sales, SUM(sales) GROUP BY date ORDER BY running_total FROM sales_data;

C. SELECT date, sales, SUM(sales) WHERE ORDER BY date FROM sales_data;

D. SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data;

Solution

1. Step 1: Identify correct window function syntax

   SUM(sales) OVER (ORDER BY date) calculates running total by ordering rows by date.

2. Step 2: Check other options for syntax errors

   The incorrect options misuse PARTITION BY without OVER(), use WHERE incorrectly, or confuse GROUP BY with window functions.

3. Final Answer:

   SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data; -> Option D

4. Quick Check:

   SUM() OVER (ORDER BY ...) = running total syntax [OK]

Hint: Use OVER() with ORDER BY for running totals [OK]

Common Mistakes:

• Using PARTITION BY without OVER()
• Trying to use WHERE inside window function
• Confusing GROUP BY with window functions

3. Given the table sales(date, amount) with rows:
('2024-01-01', 100), ('2024-01-02', 150), ('2024-01-03', 200)
What is the output of this query?

SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales ORDER BY date;

medium

A. [('2024-01-01', 100, 450), ('2024-01-02', 150, 350), ('2024-01-03', 200, 200)]

B. [('2024-01-01', 100, 100), ('2024-01-02', 150, 150), ('2024-01-03', 200, 200)]

C. [('2024-01-01', 100, 100), ('2024-01-02', 150, 250), ('2024-01-03', 200, 450)]

D. [('2024-01-01', 100, NULL), ('2024-01-02', 150, NULL), ('2024-01-03', 200, NULL)]

Solution

1. Step 1: Understand SUM() OVER with frame

   The frame includes all rows from the start up to current row ordered by date, so it sums cumulatively.

2. Step 2: Calculate running totals row by row

   Row 1: 100; Row 2: 100+150=250; Row 3: 250+200=450.

3. Final Answer:

   [('2024-01-01', 100, 100), ('2024-01-02', 150, 250), ('2024-01-03', 200, 450)] -> Option C

4. Quick Check:

   Running total sums up to current row [OK]

Hint: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sums cumulatively [OK]

Common Mistakes:

• Ignoring frame clause and expecting total sum on all rows
• Confusing running total with simple SUM() aggregation
• Assuming NULLs appear without reason

4. You wrote this query to rank employees by salary within each department:

SELECT employee_id, department, salary, RANK() OVER (ORDER BY salary DESC) AS dept_rank FROM employees;

But the query returns the same rank for all employees regardless of department. What is the likely mistake?

medium

A. Missing PARTITION BY clause inside OVER()

B. ORDER BY salary ASC instead of DESC

C. Using RANK() instead of ROW_NUMBER()

D. Not ordering the final result by department

Solution

1. Step 1: Check PARTITION BY usage

   If PARTITION BY is missing or incorrect, ranking happens over entire table, ignoring departments.

2. Step 2: Confirm ORDER BY and function choice

   ORDER BY DESC is correct for highest salary first; RANK() is appropriate for ties; ordering final result doesn't affect ranking.

3. Final Answer:

   Missing PARTITION BY clause inside OVER() -> Option A

4. Quick Check:

   PARTITION BY groups rows for ranking [OK]

Hint: Always include PARTITION BY to rank within groups [OK]

Common Mistakes:

• Forgetting PARTITION BY causes global ranking
• Confusing RANK() and ROW_NUMBER() effects
• Thinking ORDER BY outside OVER() affects ranking

5. You want to find each employee's salary difference compared to the previous employee in the same department ordered by salary descending. Which query correctly uses window functions to achieve this?

hard

A. SELECT employee_id, department, salary, salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_diff FROM employees;

B. SELECT employee_id, department, salary, salary - LEAD(salary) OVER (ORDER BY salary) AS salary_diff FROM employees;

C. SELECT employee_id, department, salary, salary - LAG(salary) FROM employees GROUP BY department ORDER BY salary DESC;

D. SELECT employee_id, department, salary, salary - FIRST_VALUE(salary) OVER (PARTITION BY department) AS salary_diff FROM employees;

Solution

1. Step 1: Use LAG() to get previous row's salary per department

   LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) gets previous salary in descending order within department.

2. Step 2: Subtract previous salary from current salary

   salary - LAG(salary) calculates difference compared to previous employee.

3. Final Answer:

   SELECT employee_id, department, salary, salary - LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_diff FROM employees; -> Option A

4. Quick Check:

   LAG() gets previous row value for difference [OK]

Hint: Use LAG() with PARTITION BY and ORDER BY for previous row values [OK]

Common Mistakes:

• Using LEAD() instead of LAG() for previous row
• Omitting PARTITION BY causing wrong grouping
• Trying to use aggregate functions without OVER()