Conditional aggregation pattern in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of conditional aggregation with CASE

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

SELECT region,
       SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS high_sales
FROM Sales
GROUP BY region;

SQL

CREATE TABLE Sales (region VARCHAR(10), amount INT);
INSERT INTO Sales VALUES
('East', 120), ('East', 80), ('West', 150), ('West', 90), ('North', 110);

-- Query:
SELECT region,
       SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS high_sales
FROM Sales
GROUP BY region;

A [{"region": "East", "high_sales": 200}, {"region": "West", "high_sales": 240}, {"region": "North", "high_sales": 110}]

B [{"region": "East", "high_sales": 120}, {"region": "West", "high_sales": 150}, {"region": "North", "high_sales": 110}]

C [{"region": "East", "high_sales": 80}, {"region": "West", "high_sales": 90}, {"region": "North", "high_sales": 0}]

D [{"region": "East", "high_sales": 200}, {"region": "West", "high_sales": 150}, {"region": "North", "high_sales": 110}]

💡 Hint

Think about how the CASE statement filters amounts greater than 100 before summing.

The CASE inside SUM adds only amounts greater than 100. For East, only 120 counts; for West, only 150; for North, 110.

🧠 Conceptual

intermediate

Understanding conditional aggregation logic

Which of the following best describes what conditional aggregation does in SQL?

A It applies a condition inside an aggregate function to selectively include values.

B It changes the grouping columns dynamically based on conditions.

C It filters rows before aggregation based on a condition.

D It creates new tables with aggregated data conditionally.

💡 Hint

Think about how CASE is used inside aggregate functions.

Conditional aggregation uses CASE inside aggregates to include only certain values in the calculation, without filtering rows.

📝 Syntax

advanced

Identify the syntax error in conditional aggregation

Which option contains a syntax error in this conditional aggregation query?

SELECT department,
       COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count
FROM Employees
GROUP BY department;

A SELECT department, COUNT(CASE status = 'active' THEN 1 END) AS active_count FROM Employees GROUP BY department;

B SELECT department, COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count FROM Employees GROUP BY department;

C SELECT department, COUNT(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count FROM Employees GROUP BY department;

D SELECT department, COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END) AS active_count FROM Employees GROUP BY department;

💡 Hint

Look carefully at the CASE syntax inside COUNT.

Option A is missing the WHEN keyword after CASE, causing a syntax error.

❓ optimization

advanced

Optimizing conditional aggregation for performance

Which query is generally more efficient for counting rows with a condition in large tables?

A SELECT COUNT(*) FILTER (WHERE status = 'active') FROM Employees;

B SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) FROM Employees;

C SELECT COUNT(*) FROM Employees WHERE status = 'active';

D SELECT COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END) FROM Employees;

💡 Hint

Consider how filtering rows before aggregation affects performance.

Filtering rows in the WHERE clause (option C) reduces data before aggregation, usually improving performance over conditional aggregation inside aggregates.

🔧 Debug

expert

Debugging unexpected results in conditional aggregation

A developer wrote this query to count active and inactive users per city:

SELECT city,
       SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_users,
       SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_users
FROM Users
GROUP BY city;

But the result shows zero counts for some cities that have users. What is the most likely cause?

A The SUM function cannot be used with CASE statements.

B The ELSE 0 in CASE causes all counts to be zero.

C The GROUP BY city is missing a HAVING clause to filter cities.

D The status column contains NULL values that are not counted in either condition.

💡 Hint

Think about how NULL values affect conditional aggregation.

If status is NULL, neither condition matches, so those rows add zero to both sums, possibly causing zero counts despite users existing.