FILTER clause for conditional aggregation in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate conditional sums using FILTER clause

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

SELECT region,
       SUM(amount) FILTER (WHERE category = 'A') AS sum_a,
       SUM(amount) FILTER (WHERE category = 'B') AS sum_b
FROM sales
GROUP BY region
ORDER BY region;

Assume the table data is:

region | amount | category
-------+--------+---------
East   | 100    | A
East   | 200    | B
West   | 150    | A
West   | 50     | B
West   | 100    | C

A [{"region": "East", "sum_a": 100, "sum_b": 200}, {"region": "West", "sum_a": 150, "sum_b": 50}]

B [{"region": "East", "sum_a": 100, "sum_b": 200}, {"region": "West", "sum_a": 150, "sum_b": 0}]

C [{"region": "East", "sum_a": 100, "sum_b": 0}, {"region": "West", "sum_a": 150, "sum_b": 50}]

D [{"region": "East", "sum_a": 300, "sum_b": 200}, {"region": "West", "sum_a": 150, "sum_b": 150}]

💡 Hint

Remember FILTER applies the condition only to the aggregation inside it.

The FILTER clause sums only amounts where the category matches the condition. For East, sum_a is 100 (only one row with category A), sum_b is 200 (one row with B). For West, sum_a is 150, sum_b is 50. Other categories are ignored.

🧠 Conceptual

intermediate

Understanding FILTER clause behavior with NULL values

Consider a table orders with columns customer_id, status, and total. What does the FILTER clause do when the condition evaluates to NULL?

SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders
FROM orders
GROUP BY customer_id;

If some rows have status as NULL, how are they counted?

A Rows with NULL status are counted in completed_orders because NULL is treated as true.

B Rows with NULL status are not counted in completed_orders because the condition evaluates to NULL, which is treated as false.

C Rows with NULL status cause the query to fail with an error.

D Rows with NULL status are counted as zero but included in the total count.

💡 Hint

FILTER counts only rows where the condition is true, NULL is not true.

In SQL, a condition that evaluates to NULL is treated as false in FILTER. So rows with NULL status are excluded from the filtered count.

📝 Syntax

advanced

Identify the syntax error in FILTER clause usage

Which of the following queries will cause a syntax error in PostgreSQL?

SELECT department,
       SUM(salary) FILTER (category = 'Engineering')
FROM employees
GROUP BY department;

A Syntax error because GROUP BY is missing a column.

B The query runs without error and returns sums filtered by category.

C Syntax error because SUM cannot be used with FILTER clause.

D Syntax error because FILTER clause requires WHERE keyword before the condition.

💡 Hint

Check the FILTER clause syntax carefully.

FILTER clause syntax requires the keyword WHERE before the condition. Missing WHERE causes syntax error.

❓ optimization

advanced

Optimize query using FILTER clause instead of CASE

You have this query:

SELECT region,
       SUM(CASE WHEN category = 'A' THEN amount ELSE 0 END) AS sum_a,
       SUM(CASE WHEN category = 'B' THEN amount ELSE 0 END) AS sum_b
FROM sales
GROUP BY region;

Which rewritten query using FILTER clause is equivalent and potentially more efficient?

A SELECT region, SUM(amount) FILTER (WHERE category = 'A') AS sum_a, SUM(amount) FILTER (WHERE category = 'B') AS sum_b FROM sales GROUP BY region;

B SELECT region, SUM(amount) WHERE category = 'A' AS sum_a, SUM(amount) WHERE category = 'B' AS sum_b FROM sales GROUP BY region;

C SELECT region, SUM(amount) FILTER (category = 'A') AS sum_a, SUM(amount) FILTER (category = 'B') AS sum_b FROM sales GROUP BY region;

D SELECT region, SUM(amount) CASE WHEN category = 'A' THEN amount END AS sum_a, SUM(amount) CASE WHEN category = 'B' THEN amount END AS sum_b FROM sales GROUP BY region;

💡 Hint

FILTER clause requires WHERE keyword and condition inside parentheses.

Option A correctly uses FILTER with WHERE and condition. Other options have syntax errors or invalid clauses.

🔧 Debug

expert

Debug unexpected results with FILTER clause and NULLs

A developer wrote this query:

SELECT product_id,
       COUNT(*) FILTER (WHERE status = 'sold') AS sold_count,
       COUNT(*) FILTER (WHERE status != 'sold') AS other_count
FROM product_sales
GROUP BY product_id;

But the other_count is unexpectedly low. The status column contains NULL values. Why?
Choose the best explanation.

A The query fails because NULL values cause COUNT(*) to error inside FILTER.

B The query counts NULLs in other_count because NULL != 'sold' is true.

C The condition <code>status != 'sold'</code> excludes NULLs because NULL comparison returns NULL, so those rows are not counted.

D The query counts NULLs in sold_count because NULL = 'sold' is true.

💡 Hint

Remember how NULL behaves in SQL comparisons.

In SQL, NULL compared with anything returns NULL, which is treated as false in FILTER. So rows with NULL status are excluded from both filters, lowering other_count.