Categorizing Employees and Tracking Metrics by Work Hours and Software Runs (CASE Logic & Conditional Aggregation)

This chapter dives into advanced SQL interview problem-solving techniques, specifically focusing on pivoting rows to columns using conditional CASE WHEN statements, handling sparse data with aggregate functions like MAX(), filtering grouped datasets with HAVING vs. Common Table Expressions (CTEs), and computing differences between sequential runs (e.g., latest vs. previous run statistics).

1. Why Do We Need Conditional Aggregation and Pivoting?

In standard relational databases, event logs, run histories, and time-series data are stored vertically (row-by-row). However, business intelligence and analytical interview questions frequently demand horizontal comparisons—such as comparing a system's latest run performance against its previous run performance side-by-side in a single row.

• The Status Quo: Querying raw logs returns multiple rows per entity (e.g., software ID) sorted by run sequence.
• The Problem: You cannot easily subtract or compare values across different rows for the same entity using basic arithmetic.
• The Solution: We assign row numbers (RN) to order the runs, use CASE WHEN logic to pivot vertical rows into distinct columns, and apply conditional aggregation (MAX()) combined with GROUP BY to collapse multiple rows into a single summary row per entity.

2. Core Mental Model: The Pivoting Analogy

Imagine a security guard monitoring daily security scans for various software tools.

Note: The CASE WHEN statement acts as a sorting filter for columns, while the MAX() aggregate function acts as a null-ignoring vacuum cleaner, pulling the non-null value up into the final grouped row.

3. Step-by-Step Evolution: From Raw Rows to Pivoted Columns

Let's look at how we transform row-level sequence numbers into separate columns using CASE WHEN. Assuming we already have a preliminary result set (or CTE) containing a row number RN (where RN = 1 represents the latest run and RN = 2 represents the second latest/previous run), we can split our metrics into dedicated columns.

Naive Implementation (Unaggregated Row Output)

If we simply select columns with CASE statements without grouping, we get sparse rows containing NULL values where conditions do not match:

SELECT 
    software_id,
    CASE WHEN rn = 1 THEN malware_detected END AS latest_run,
    CASE WHEN rn = 2 THEN malware_detected END AS previous_run
FROM ranked_runs;

Output Behavior:

• When rn = 1, latest_run populates with the malware count, while previous_run evaluates to NULL.
• When rn = 2, latest_run evaluates to NULL, while previous_run populates with the malware count.

4. Applying Grouping and Null-Ignoring Aggregates

To combine these sparse rows into a single consolidated row per software_id, we introduce GROUP BY alongside the MAX() aggregate function.

Why MAX()? Because for any given software_id, one row will contain the integer value and the other row will contain NULL. MAX() ignores NULL values and safely extracts the actual number. (You could also use SUM(), but MAX() is standard practice for conditional pivoting).

SELECT 
    software_id,
    MAX(CASE WHEN rn = 1 THEN malware_detected END) AS latest_run,
    MAX(CASE WHEN rn = 2 THEN malware_detected END) AS previous_run
FROM ranked_runs
GROUP BY software_id;

5. Filtering Aggregated Results: HAVING vs. CTEs

When working with aggregated metrics, filtering conditions depend on whether the filter applies to raw pre-aggregated rows or post-aggregation calculations.

Strategy A: Using the HAVING Clause

If we want to restrict our dataset to software that has executed at least twice (ensuring both latest and previous runs exist), we can filter at the group level using HAVING:

SELECT 
    software_id,
    MAX(CASE WHEN rn = 1 THEN malware_detected END) AS latest_run,
    MAX(CASE WHEN rn = 2 THEN malware_detected END) AS previous_run
FROM ranked_runs
WHERE rn IN (1, 2)
GROUP BY software_id
HAVING COUNT(*) = 2 
   AND MAX(CASE WHEN rn = 1 THEN malware_detected END) >= 10;

Strategy B: Wrapping in a CTE (Cleaner Readability)

Many engineers prefer wrapping the pivoted aggregation inside a Common Table Expression (CTE) to keep filtering logic clean and maintainable, especially when computing derived columns (like mathematical differences).

WITH aggregated_runs AS (
    SELECT 
        software_id,
        MAX(CASE WHEN rn = 1 THEN malware_detected END) AS latest_run,
        MAX(CASE WHEN rn = 2 THEN malware_detected END) AS previous_run
    FROM ranked_runs
    WHERE rn IN (1, 2)
    GROUP BY software_id
    HAVING COUNT(*) = 2
)
SELECT 
    software_id,
    latest_run,
    previous_run,
    (latest_run - previous_run) AS diff
FROM aggregated_runs
WHERE latest_run >= 10;

6. Complete Flow Walkthrough

Let's trace how data flows from the raw scan logs through the aggregation pipeline to the final calculated difference.

Raw Scan Logs (software_id, rn, malware_detected)
   |
   | Filters rows where rn IN (1, 2)
   v
Ranked Subset
   |
   | Groups by software_id & Pivots via MAX(CASE WHEN ...)
   v
Aggregated CTE (software_id, latest_run, previous_run)
   |
   | Filters where latest_run >= 10 AND COUNT(*) = 2
   v
Computed Final Output (software_id, latest_run, previous_run, diff)

Verification Query Example

During interviews or complex debugging sessions, you can run parallel verification queries to validate your aggregated output against raw rows:

-- Verification check for specific software ID (e.g., 101)
SELECT software_id, rn, malware_detected 
FROM ranked_runs 
WHERE software_id = 101;

7. Industry Reality & Debugging Edge Cases

When writing conditional aggregation queries in production systems, keep these common pitfalls in mind:

Summary and Key Takeaways

• Conditional Pivoting: Use CASE WHEN rn = 1 THEN value END to transform vertical row data into distinct horizontal columns.
• Null-Ignoring Aggregation: Always pair conditional CASE statements with an aggregate function like MAX() or SUM() and a GROUP BY clause to collapse multi-row records into single entity summaries.
• Filtering Flexibility: Use HAVING clauses for group-level validation (such as COUNT(*) = 2), or wrap pivoted queries in CTEs for cleaner downstream math and filtering.
• Derived Metrics: Once pivoted into columns, straightforward arithmetic (e.g., latest_run - previous_run) enables rapid trend analysis across sequential runs.
• Data Verification: Always cross-verify complex aggregated outputs against raw underlying rows during debugging to ensure edge cases (like single-run entities) are handled properly.