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.

Practice

1. What does the following SQL snippet do?
SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) FROM users;

easy

A. Counts how many users have status other than 'active'.

B. Calculates the total number of users regardless of status.

C. Returns the sum of all status values.

D. Counts how many users have status 'active'.

Solution

1. Step 1: Understand the CASE inside SUM

   The CASE returns 1 for each row where status is 'active', else 0.

2. Step 2: SUM adds all 1s and 0s

   SUM counts how many rows have status 'active' by adding 1s.

3. Final Answer:

   Counts how many users have status 'active'. -> Option D

4. Quick Check:

   SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts matches [OK]

Hint: SUM with CASE counting 1s counts matching rows [OK]

Common Mistakes:

• Thinking SUM adds status values instead of counting
• Confusing ELSE 0 with ELSE NULL
• Assuming it counts non-active users

2. Which of the following is the correct syntax to count rows where score is greater than 50 using CASE inside COUNT?

easy

A. SELECT COUNT(CASE WHEN score > 50 THEN 1 END) FROM results;

B. SELECT COUNT(CASE score > 50 THEN 1 ELSE 0 END) FROM results;

C. SELECT COUNT(CASE WHEN score > 50 THEN 1 ELSE 0 END) FROM results;

D. SELECT COUNT(CASE score > 50 THEN 1 END) FROM results;

Solution

1. Step 1: Recall COUNT counts non-null values

   COUNT counts rows where expression is NOT NULL.

2. Step 2: Use CASE to return 1 for matches, NULL otherwise

   CASE WHEN condition THEN 1 END returns 1 or NULL (default ELSE NULL).

3. Final Answer:

   SELECT COUNT(CASE WHEN score > 50 THEN 1 END) FROM results; -> Option A

4. Quick Check:

   COUNT with CASE returning NULL for no match counts only matches [OK]

Hint: COUNT counts non-null, so ELSE NULL is default [OK]

Common Mistakes:

• Using ELSE 0 causes COUNT to count all rows
• Omitting WHEN keyword
• Using invalid CASE syntax

3. 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 big_sales FROM sales GROUP BY region;

medium

A. Sum of all sales amounts per region.

B. Sum of sales amounts greater than 100 per region.

C. Count of sales greater than 100 per region.

D. Sum of sales amounts less than or equal to 100 per region.

Solution

1. Step 1: Analyze CASE inside SUM

   CASE returns amount if > 100, else 0, so only big sales count fully.

2. Step 2: SUM adds these amounts per region

   SUM totals amounts > 100 per region, ignoring smaller sales by adding 0.

3. Final Answer:

   Sum of sales amounts greater than 100 per region. -> Option B

4. Quick Check:

   SUM with CASE filters values conditionally [OK]

Hint: SUM with CASE ELSE 0 sums only matching values [OK]

Common Mistakes:

• Thinking it counts rows instead of sums amounts
• Ignoring ELSE 0 and expecting NULL filtering
• Confusing greater than with less than

4. Identify the error in this query:

SELECT department, COUNT(CASE department = 'HR' THEN 1 ELSE NULL END) FROM employees GROUP BY department;

medium

A. Missing WHEN keyword in CASE statement.

B. COUNT cannot be used with CASE.

C. GROUP BY should include COUNT column.

D. ELSE NULL is invalid in CASE.

Solution

1. Step 1: Check CASE syntax

   CASE requires WHEN before condition; here it's missing.

2. Step 2: Confirm correct syntax

   Correct form: CASE WHEN department = 'HR' THEN 1 ELSE NULL END.

3. Final Answer:

   Missing WHEN keyword in CASE statement. -> Option A

4. Quick Check:

   CASE syntax needs WHEN before condition [OK]

Hint: CASE must have WHEN before condition [OK]

Common Mistakes:

• Omitting WHEN keyword
• Misusing COUNT with CASE
• Incorrect GROUP BY usage

5. You want to find the average salary of employees, but only include salaries above 50000. Which query correctly uses CASE with AVG to achieve this?

hard

A. SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE 0 END) FROM employees;

B. SELECT AVG(CASE salary > 50000 THEN salary END) FROM employees;

C. SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) FROM employees;

D. SELECT AVG(CASE salary > 50000 THEN salary ELSE NULL END) FROM employees;

Solution

1. Step 1: Understand AVG ignores NULLs

   AVG calculates average of non-null values only.

2. Step 2: Use CASE to return salary if > 50000, else NULL

   This excludes salaries ≤ 50000 from average calculation.

3. Final Answer:

   SELECT AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) FROM employees; -> Option C

4. Quick Check:

   AVG with CASE ELSE NULL excludes unwanted rows [OK]

Hint: Use ELSE NULL to exclude rows from AVG calculation [OK]

Common Mistakes:

• Using ELSE 0 includes zeros in average, skewing result
• Omitting WHEN keyword
• Using invalid CASE syntax