SUM, AVG, COUNT as window functions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of SUM() as a window function

Given the table sales with columns id, region, and amount, what is the output of this query?

SELECT id, region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY id) AS running_total FROM sales ORDER BY id;

PostgreSQL

CREATE TABLE sales (id INT, region TEXT, amount INT);
INSERT INTO sales VALUES
(1, 'East', 100),
(2, 'East', 150),
(3, 'West', 200),
(4, 'East', 50),
(5, 'West', 100);

A [{"id":1,"region":"East","amount":100,"running_total":100},{"id":2,"region":"East","amount":150,"running_total":250},{"id":3,"region":"West","amount":200,"running_total":200},{"id":4,"region":"East","amount":50,"running_total":300},{"id":5,"region":"West","amount":100,"running_total":300}]

B [{"id":1,"region":"East","amount":100,"running_total":100},{"id":2,"region":"East","amount":150,"running_total":150},{"id":3,"region":"West","amount":200,"running_total":200},{"id":4,"region":"East","amount":50,"running_total":50},{"id":5,"region":"West","amount":100,"running_total":100}]

C [{"id":1,"region":"East","amount":100,"running_total":100},{"id":2,"region":"East","amount":150,"running_total":250},{"id":3,"region":"West","amount":200,"running_total":300},{"id":4,"region":"East","amount":50,"running_total":350},{"id":5,"region":"West","amount":100,"running_total":400}]

D [{"id":1,"region":"East","amount":100,"running_total":100},{"id":2,"region":"East","amount":150,"running_total":250},{"id":3,"region":"West","amount":200,"running_total":200},{"id":4,"region":"East","amount":50,"running_total":250},{"id":5,"region":"West","amount":100,"running_total":300}]

💡 Hint

Think about how the SUM() window function adds amounts within each region, ordered by id.

The SUM() OVER (PARTITION BY region ORDER BY id) calculates a running total of amounts for each region separately, adding amounts in order of id.

❓ query_result

intermediate

Output of AVG() as a window function with frame

Consider the table scores with columns student_id and score. What is the output of this query?

SELECT student_id, score, AVG(score) OVER (ORDER BY student_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_score FROM scores ORDER BY student_id;

PostgreSQL

CREATE TABLE scores (student_id INT, score INT);
INSERT INTO scores VALUES
(1, 80),
(2, 90),
(3, 70),
(4, 100);

A [{"student_id":1,"score":80,"avg_score":80.0},{"student_id":2,"score":90,"avg_score":85.0},{"student_id":3,"score":70,"avg_score":70.0},{"student_id":4,"score":100,"avg_score":85.0}]

B [{"student_id":1,"score":80,"avg_score":80.0},{"student_id":2,"score":90,"avg_score":90.0},{"student_id":3,"score":70,"avg_score":70.0},{"student_id":4,"score":100,"avg_score":100.0}]

C [{"student_id":1,"score":80,"avg_score":80.0},{"student_id":2,"score":90,"avg_score":85.0},{"student_id":3,"score":70,"avg_score":80.0},{"student_id":4,"score":100,"avg_score":85.0}]

D [{"student_id":1,"score":80,"avg_score":85.0},{"student_id":2,"score":90,"avg_score":80.0},{"student_id":3,"score":70,"avg_score":85.0},{"student_id":4,"score":100,"avg_score":70.0}]

💡 Hint

The frame includes the current row and the one before it, so average is over 1 or 2 scores.

For student 1, only one row (80). For student 2, average of 80 and 90 = 85. For student 3, average of 90 and 70 = 80. For student 4, average of 70 and 100 = 85.

📝 Syntax

advanced

Identify the syntax error in COUNT() window function

Which option contains a syntax error when using COUNT() as a window function?

PostgreSQL

SELECT department, employee_id, COUNT(*) OVER PARTITION BY department FROM employees;

A SELECT department, employee_id, COUNT(*) OVER (PARTITION BY department) FROM employees;

B SELECT department, employee_id, COUNT(*) OVER PARTITION BY department FROM employees;

C SELECT department, employee_id, COUNT(*) OVER (PARTITION BY department ORDER BY employee_id) FROM employees;

D SELECT department, employee_id, COUNT(*) OVER (PARTITION BY department ROWS UNBOUNDED PRECEDING) FROM employees;

💡 Hint

Check the parentheses around the window specification.

The window specification for OVER must be enclosed in parentheses. Option B misses these parentheses, causing a syntax error.

❓ optimization

advanced

Optimizing AVG() window function with partition

You want to calculate the average sales per region for each sale row in the sales table. Which query is the most efficient and correct to achieve this?

PostgreSQL

Table sales(id INT, region TEXT, amount INT)

A SELECT id, region, amount, AVG(amount) OVER (PARTITION BY region) AS avg_region_sales FROM sales;

B SELECT id, region, amount, AVG(amount) FROM sales GROUP BY region;

C SELECT id, region, amount, AVG(amount) OVER () AS avg_region_sales FROM sales;

D SELECT id, region, amount, AVG(amount) OVER (ORDER BY region) AS avg_region_sales FROM sales;

💡 Hint

Think about how to get average per region for each row without grouping rows.

Option A uses AVG() as a window function partitioned by region, giving average per region for each row. Option A groups rows and loses individual rows. Option A averages over all rows ignoring region. Option A orders but does not partition, so average is over all rows ordered.

🧠 Conceptual

expert

Understanding COUNT() window function behavior

Given the table orders with columns order_id, customer_id, and status, what will be the result of this query?

SELECT order_id, customer_id, status, COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_count FROM orders ORDER BY order_id;

Assuming data:
order_id | customer_id | status 1 | 101 | shipped 2 | 102 | pending 3 | 101 | shipped 4 | 101 | pending 5 | 102 | shipped

PostgreSQL

CREATE TABLE orders (order_id INT, customer_id INT, status TEXT);
INSERT INTO orders VALUES
(1, 101, 'shipped'),
(2, 102, 'pending'),
(3, 101, 'shipped'),
(4, 101, 'pending'),
(5, 102, 'shipped');

A [{"order_id":1,"customer_id":101,"status":"shipped","running_count":1},{"order_id":2,"customer_id":102,"status":"pending","running_count":1},{"order_id":3,"customer_id":101,"status":"shipped","running_count":3},{"order_id":4,"customer_id":101,"status":"pending","running_count":4},{"order_id":5,"customer_id":102,"status":"shipped","running_count":2}]

B [{"order_id":1,"customer_id":101,"status":"shipped","running_count":1},{"order_id":2,"customer_id":102,"status":"pending","running_count":2},{"order_id":3,"customer_id":101,"status":"shipped","running_count":2},{"order_id":4,"customer_id":101,"status":"pending","running_count":3},{"order_id":5,"customer_id":102,"status":"shipped","running_count":3}]

C [{"order_id":1,"customer_id":101,"status":"shipped","running_count":1},{"order_id":2,"customer_id":102,"status":"pending","running_count":1},{"order_id":3,"customer_id":101,"status":"shipped","running_count":1},{"order_id":4,"customer_id":101,"status":"pending","running_count":2},{"order_id":5,"customer_id":102,"status":"shipped","running_count":1}]

D [{"order_id":1,"customer_id":101,"status":"shipped","running_count":1},{"order_id":2,"customer_id":102,"status":"pending","running_count":1},{"order_id":3,"customer_id":101,"status":"shipped","running_count":2},{"order_id":4,"customer_id":101,"status":"pending","running_count":3},{"order_id":5,"customer_id":102,"status":"shipped","running_count":2}]

💡 Hint

COUNT(*) counts rows per customer_id up to current order_id in order.

The window frame counts all rows for the same customer_id from the first order up to the current order ordered by order_id, producing a running count per customer.

Practice

1. What does the COUNT(*) OVER() window function do in PostgreSQL?

easy

A. Counts all rows in the result set without grouping them.

B. Counts only distinct rows in the table.

C. Counts rows and groups them by a specified column.

D. Counts rows but returns only one aggregated result.

Solution

1. Step 1: Understand COUNT(*) OVER() usage

   The function counts all rows in the query result but does not group or collapse rows.

2. Step 2: Difference from GROUP BY

   Unlike GROUP BY, it keeps all rows visible and adds the count as a column.

3. Final Answer:

   Counts all rows in the result set without grouping them. -> Option A

4. Quick Check:

   COUNT(*) OVER() counts all rows [OK]

Hint: Window COUNT(*) counts all rows without grouping [OK]

Common Mistakes:

• Confusing window COUNT with GROUP BY count
• Thinking it returns a single aggregated row
• Assuming it counts distinct values only

2. Which of the following is the correct syntax to calculate a running total of sales using SUM as a window function in PostgreSQL?

easy

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

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

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

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

Solution

1. Step 1: Identify correct window function syntax

   SUM(sales) OVER (ORDER BY date) calculates cumulative sum ordered by date.

2. Step 2: Check other options for syntax errors

   The other options misuse window function syntax or omit OVER keyword.

3. Final Answer:

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

4. Quick Check:

   SUM() OVER (ORDER BY ...) is correct syntax [OK]

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

Common Mistakes:

• Omitting OVER() clause
• Using PARTITION BY without OVER()
• Confusing GROUP BY with window functions

3. Given the table employees with columns department and salary, what will the following query return?

SELECT department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary FROM employees;

medium

A. Syntax error due to missing GROUP BY clause.

B. Each row shows salary and overall average salary of all employees.

C. Each row shows salary and average salary of all departments combined.

D. Each row shows salary and average salary of its department.

Solution

1. Step 1: Understand PARTITION BY in AVG window function

   AVG(salary) OVER (PARTITION BY department) calculates average salary per department for each row.

2. Step 2: Confirm output per row

   Each row keeps salary and adds average salary of that row's department without grouping rows.

3. Final Answer:

   Each row shows salary and average salary of its department. -> Option D

4. Quick Check:

   AVG() OVER (PARTITION BY ...) averages per group per row [OK]

Hint: PARTITION BY groups rows but keeps all rows visible [OK]

Common Mistakes:

• Thinking it aggregates to one row per group
• Confusing window AVG with GROUP BY AVG
• Expecting overall average without PARTITION BY

4. Identify the error in this query:

SELECT employee_id, salary, COUNT(*) OVER PARTITION BY department FROM employees;

medium

A. COUNT(*) cannot be used as a window function.

B. Missing parentheses around PARTITION BY clause in OVER().

C. employee_id cannot be selected with window functions.

D. No error; query is correct.

Solution

1. Step 1: Check syntax of window function

   OVER clause must have parentheses: OVER (PARTITION BY department).

2. Step 2: Validate other parts

   COUNT(*) is valid as window function; selecting employee_id is allowed.

3. Final Answer:

   Missing parentheses around PARTITION BY clause in OVER(). -> Option B

4. Quick Check:

   OVER() requires parentheses [OK]

Hint: Always use parentheses after OVER keyword [OK]

Common Mistakes:

• Omitting parentheses in OVER()
• Thinking COUNT(*) can't be window function
• Assuming window functions require GROUP BY

5. You want to find the cumulative count of orders per customer ordered by order date. Which query correctly uses COUNT as a window function to achieve this?

hard

A. SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id, order_date ORDER BY order_date;

B. SELECT customer_id, order_date, COUNT(*) OVER (ORDER BY order_date) AS cumulative_orders FROM orders;

C. SELECT customer_id, order_date, COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_orders FROM orders;

D. SELECT customer_id, order_date, COUNT(*) OVER (PARTITION BY order_date ORDER BY customer_id) AS cumulative_orders FROM orders;

Solution

1. Step 1: Understand cumulative count per customer

   COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) counts rows per customer in order of order_date cumulatively.

2. Step 2: Check other options for correctness

   SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id, order_date ORDER BY order_date; uses GROUP BY, not cumulative count; C lacks partition by customer; D partitions by order_date, not customer.

3. Final Answer:

   SELECT customer_id, order_date, COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_orders FROM orders; -> Option C

4. Quick Check:

   Use PARTITION BY customer_id with ORDER BY for cumulative count [OK]

Hint: Partition by customer and order by date for cumulative count [OK]

Common Mistakes:

• Missing PARTITION BY customer_id
• Using GROUP BY instead of window function
• Partitioning by wrong column