Challenge - 5 Problems
FILTER Clause Mastery
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate2:00remaining
Calculate conditional sums using FILTER clause
Given a table sales with columns
Assume the table data is:
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
Attempts:
2 left
💡 Hint
Remember FILTER applies the condition only to the aggregation inside it.
✗ Incorrect
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
intermediate1:30remaining
Understanding FILTER clause behavior with NULL values
Consider a table
If some rows have
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?Attempts:
2 left
💡 Hint
FILTER counts only rows where the condition is true, NULL is not true.
✗ Incorrect
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
advanced1:30remaining
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;
Attempts:
2 left
💡 Hint
Check the FILTER clause syntax carefully.
✗ Incorrect
FILTER clause syntax requires the keyword WHERE before the condition. Missing WHERE causes syntax error.
❓ optimization
advanced2:00remaining
Optimize query using FILTER clause instead of CASE
You have this query:
Which rewritten query using FILTER clause is equivalent and potentially more efficient?
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?
Attempts:
2 left
💡 Hint
FILTER clause requires WHERE keyword and condition inside parentheses.
✗ Incorrect
Option A correctly uses FILTER with WHERE and condition. Other options have syntax errors or invalid clauses.
🔧 Debug
expert2:30remaining
Debug unexpected results with FILTER clause and NULLs
A developer wrote this query:
But the
Choose the best explanation.
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.
Attempts:
2 left
💡 Hint
Remember how NULL behaves in SQL comparisons.
✗ Incorrect
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.