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.