CASE with aggregate functions in SQL - Time & Space Complexity

Time Complexity: CASE with aggregate functions

O(n)

Understanding Time Complexity

We want to understand how the time to run a SQL query with CASE inside aggregate functions changes as the data grows.

Specifically, how does the query's work increase when there are more rows to process?

Scenario Under Consideration

Analyze the time complexity of the following SQL query.

SELECT
  department_id,
  SUM(CASE WHEN status = 'active' THEN salary ELSE 0 END) AS active_salary,
  COUNT(CASE WHEN status = 'inactive' THEN 1 ELSE NULL END) AS inactive_count
FROM employees
GROUP BY department_id;

This query calculates total salary for active employees and counts inactive employees per department.

Identify Repeating Operations

Look for repeated actions in the query.

• Primary operation: Scanning each row in the employees table once.
• How many times: Once per row, applying the CASE checks and updating sums or counts.

How Execution Grows With Input

As the number of rows grows, the query does more work.

Input Size (n)	Approx. Operations
10	About 10 row checks and updates
100	About 100 row checks and updates
1000	About 1000 row checks and updates

Pattern observation: The work grows directly with the number of rows; doubling rows roughly doubles work.

Final Time Complexity

Time Complexity: O(n)

This means the query's work grows in a straight line with the number of rows it processes.

Common Mistake

[X] Wrong: "Using CASE inside aggregates makes the query slower than scanning all rows once."

[OK] Correct: The CASE just adds a quick check per row; the query still scans each row only once, so it doesn't multiply the work.

Interview Connect

Understanding how aggregate functions with CASE scale helps you explain query performance clearly and confidently.

Self-Check

"What if we added a nested subquery inside the CASE? How would that affect the time complexity?"