CASE with aggregate functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CASE with SUM aggregate

Given the 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
ORDER BY region;

SQL

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

A [{"region": "East", "high_sales": 200}, {"region": "North", "high_sales": 50}, {"region": "West", "high_sales": 150}]

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

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

D [{"region": "East", "high_sales": 200}, {"region": "North", "high_sales": 250}, {"region": "West", "high_sales": 240}]

💡 Hint

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

The CASE expression returns the amount only if it is greater than 100; otherwise, it returns 0. SUM adds these filtered amounts per region.

❓ query_result

intermediate

COUNT with CASE inside aggregate

Consider a table employees with columns department and status ('active' or 'inactive'). What does this query return?

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

SQL

CREATE TABLE employees (department VARCHAR(10), status VARCHAR(10));
INSERT INTO employees VALUES
('HR', 'active'), ('HR', 'inactive'), ('IT', 'active'), ('IT', 'active'), ('IT', 'inactive');

A [{"department": "HR", "active_count": 1}, {"department": "IT", "active_count": 2}]

B [{"department": "HR", "active_count": 2}, {"department": "IT", "active_count": 3}]

C [{"department": "HR", "active_count": 2}, {"department": "IT", "active_count": 2}]

D [{"department": "HR", "active_count": 1}, {"department": "IT", "active_count": 3}]

💡 Hint

COUNT counts non-null values; CASE returns 1 only for active status.

CASE returns 1 for active employees, NULL otherwise. COUNT counts non-null values, so it counts active employees per department.

📝 Syntax

advanced

Identify syntax error in CASE with aggregate

Which option contains a syntax error in this SQL snippet?

SELECT department,
       SUM(CASE status WHEN 'active' THEN 1 ELSE 0 END) AS active_sum
FROM employees
GROUP BY department;

A SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)

B SUM(CASE status WHEN 'active' THEN 1 ELSE 0 END)

C SUM(CASE WHEN status = 'active' THEN 1 END)

D SUM(CASE status WHEN 'active' THEN 1)

💡 Hint

Check if the CASE expression has a matching END and proper ELSE clause.

Option D lacks the ELSE clause and the END keyword is missing or misplaced, causing syntax error.

❓ optimization

advanced

Optimizing CASE with AVG aggregate

You want to calculate the average salary of employees who joined before 2020 using this query:

SELECT AVG(CASE WHEN join_year < 2020 THEN salary ELSE NULL END) AS avg_salary_before_2020
FROM employees;

Which option improves performance without changing the result?

A SELECT AVG(salary) AS avg_salary_before_2020 FROM employees WHERE join_year < 2020;

B SELECT AVG(CASE WHEN join_year &lt; 2020 THEN salary END) AS avg_salary_before_2020 FROM employees;

C SELECT AVG(salary) AS avg_salary_before_2020 FROM employees;

D SELECT AVG(CASE WHEN join_year &lt; 2020 THEN salary ELSE 0 END) AS avg_salary_before_2020 FROM employees;

💡 Hint

Filtering rows before aggregation is usually faster than using CASE inside aggregate.

Option A filters rows before aggregation, reducing data processed and improving performance without changing results.

🧠 Conceptual

expert

Understanding NULL handling in CASE with COUNT

Given a table orders with columns status ('completed', 'pending', NULL), what is the result of this query?

SELECT COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
       COUNT(CASE WHEN status IS NULL THEN 1 END) AS null_status_count
FROM orders;

Assuming the table has 3 completed, 2 pending, and 1 NULL status rows, what is the output?

A {"completed_count": 6, "null_status_count": 1}

B {"completed_count": 3, "null_status_count": 0}

C {"completed_count": 3, "null_status_count": 1}

D {"completed_count": 3, "null_status_count": 2}

💡 Hint

COUNT counts only non-null values; CASE returns NULL if condition is false.

CASE returns 1 only when condition is true, else NULL. COUNT counts non-null values, so counts completed and NULL status rows correctly.