Cross-Tabulation and Matrix Reporting in SQL

This chapter focuses on advanced SQL patterns for categorizing and profiling entities (such as employees) based on multi-conditional threshold criteria over time-series or transactional data. You will learn how to filter out noise, perform conditional aggregations, and construct hierarchical classification logic to solve complex reporting requirements.

1. The Problem Statement & Business Requirements

In enterprise reporting, we often need to classify entities based on their historical behavior over a given period. In this scenario, we want to categorize employees based on their daily working hours.

Specifically, we are looking to identify employees who meet two distinct performance or overtime criteria:

• Criteria 1 (8+ Hour Days): The employee worked 8 or more hours for at least 3 days.
• Criteria 2 (10+ Hour Days): The employee worked 10 or more hours for at least 2 days.
• Criteria 3 (Both): The employee simultaneously satisfies both Criteria 1 and Criteria 2.

The Challenge of Overlapping Sets

A key challenge in this analysis is that the categories are nested. Any day an employee works 10+ hours automatically qualifies as an 8+ hour day. Therefore, our SQL logic must account for this overlap without double-counting or misclassifying employees who fall into both categories.

2. Core Mental Model: The Overlapping Buckets Analogy

To understand how to structure this query, visualize a physical sorting system with nested sieves.

Real-Life Analogy

Imagine a loyalty program where customers get a bronze badge if they make 3 purchases of $\char36 8$ or more, and a silver badge if they make 2 purchases of $\char36 10$ or more.

• If a customer makes two $\char36 12$ purchases and one $\char36 9$ purchase, those $\char36 12$ purchases count toward both the $\char36 10$ threshold and the $\char36 8$ threshold.
• To evaluate their final tier, you must count their total qualifying transactions at both levels and apply a hierarchical evaluation: check if they qualify for both first, before falling back to individual tiers.

3. Data Schema and Sample State

To trace the logic, let us look at the underlying data structure. Assume we have a pre-processed Common Table Expression (CTE) named CT2 that categorizes each employee's daily work hours into a time_window column.

Input Table Schema (CT2)

• emp_id (Integer): Unique identifier for the employee.
• work_date (Date): The date of the shift.
• time_window (Varchar): Categorized as '10+' (worked $\ge 10$ hours), '8+' (worked $\ge 8$ and $< 10$ hours), or '8-' (worked $< 8$ hours).

Sample Data State

Expected Classification Analysis

• Employee 100: Worked three 8+ days and zero 10+ days. Meets Criteria 1 only.
• Employee 200: Worked two 10+ days. These also count as 8+ days, giving them a total of two 8+ days. Since they do not have $\ge 3$ days of 8+ hours, they meet Criteria 2 only.
• Employee 300: Worked two 10+ days and one 8+ day. This gives them a total of three 8+ days and two 10+ days. They meet both criteria.

4. Step-by-Step Evolution: Filtering and Time Windows

The first step is to eliminate noise. Since we are only interested in shifts of 8 hours or more, we can discard any records categorized as '8-'.

Naive Approach

A naive approach might try to keep all rows and run complex conditional sums for every category. This increases processing overhead and complicates the CASE WHEN logic.

Better Approach: Early Filtering

By filtering out '8-' early, we simplify our aggregation. Once '8-' is removed, every remaining row represents a day where the employee worked at least 8 hours.

SELECT emp_id, time_window
FROM CT2
WHERE time_window IN ('10+', '8+');
-- Or alternatively: WHERE time_window != '8-'

This filter ensures that a simple COUNT(*) will yield the total number of days an employee worked 8+ hours.

5. Step-by-Step Evolution: Conditional Aggregation

With the filtered dataset, we group by emp_id to calculate the aggregate metrics for each employee.

SELECT 
    emp_id,
    COUNT(*) AS total_8_hours_days,
    SUM(CASE WHEN time_window = '10+' THEN 1 ELSE 0 END) AS total_10_hours_days
FROM CT2
WHERE time_window IN ('10+', '8+')
GROUP BY emp_id;

How This Works

1. COUNT(*): Because we filtered out all '8-' records, every row in the partition is either '8+' or '10+'. Therefore, COUNT(*) accurately represents the total number of days the employee worked at least 8 hours.
2. SUM(CASE WHEN...): This acts as a conditional filter. It scans the remaining rows and adds 1 to the sum only if the time_window is exactly '10+'.

Intermediate Aggregation Output

6. Step-by-Step Evolution: The Final Classification Logic

Now that we have the aggregated counts for each employee, we must classify them. This requires a hierarchical CASE WHEN statement.

Important Rule of SQL CASE WHEN Evaluation: SQL evaluates CASE statements sequentially from top to bottom and exits on the first matching condition. Therefore, the most restrictive condition (the "Both" case) must be evaluated first.

If we evaluated the single conditions first, an employee who qualified for "Both" (like Employee 300) would match the first condition (e.g., total_8_hours_days >= 3) and be classified incorrectly as only meeting that single criteria.

Hierarchical Classification Logic

CASE 
    WHEN total_8_hours_days >= 3 AND total_10_hours_days >= 2 THEN 'Both'
    WHEN total_8_hours_days >= 3 THEN '8+ Hours (3+ Days)'
    WHEN total_10_hours_days >= 2 THEN '10+ Hours (2+ Days)'
    ELSE 'None'
END AS criteria_met

7. Complete SQL Query Implementation

Below is the complete, production-ready SQL query combining the filtering, aggregation, and hierarchical classification steps.

WITH CT1 AS (
    -- Raw daily hours calculation per employee
    SELECT 
        emp_id,
        work_date,
        hours_worked
    FROM employee_hours
),
CT2 AS (
    -- Assigning time windows based on hours worked
    SELECT 
        emp_id,
        work_date,
        CASE 
            WHEN hours_worked >= 10 THEN '10+'
            WHEN hours_worked >= 8 THEN '8+'
            ELSE '8-'
        END AS time_window
    FROM CT1
),
AggregatedMetrics AS (
    -- Filtering noise and aggregating counts per employee
    SELECT 
        emp_id,
        COUNT(*) AS total_8_hours_days,
        SUM(CASE WHEN time_window = '10+' THEN 1 ELSE 0 END) AS total_10_hours_days
    FROM CT2
    WHERE time_window IN ('10+', '8+')
    GROUP BY emp_id
)
-- Final classification step
SELECT 
    emp_id,
    total_8_hours_days,
    total_10_hours_days,
    CASE 
        WHEN total_8_hours_days >= 3 AND total_10_hours_days >= 2 THEN 'Both'
        WHEN total_8_hours_days >= 3 THEN '8+ Hours (3+ Days)'
        WHEN total_10_hours_days >= 2 THEN '10+ Hours (2+ Days)'
        ELSE 'None'
    END AS category_status
FROM AggregatedMetrics;

8. Performance, Complexity, and Edge Cases

Complexity Analysis

• Time Complexity: $\mathcal{O}(N \log N)$ where $N$ is the number of rows in the raw employee_hours table. The primary cost comes from the GROUP BY operation, which requires sorting or hashing on emp_id.
• Space Complexity: $\mathcal{O}(U)$ where $U$ is the number of unique employees. The database engine must keep the aggregated counts for each unique employee in memory during the grouping phase.

Real-World Debugging & Edge Cases

Summary and Key Takeaways

• Nested Thresholds: When dealing with nested conditions (e.g., 10+ hours is subset of 8+ hours), structure your filters and aggregations to leverage this hierarchy rather than fighting it.
• Early Filtering: Discarding non-qualifying rows (like '8-' days) early in the query pipeline reduces the volume of data passed to downstream aggregation steps, improving performance.
• Simplifying Aggregations: By filtering out '8-' rows, COUNT(*) naturally becomes the count of 8+ days, eliminating the need for a second, redundant conditional sum.
• Hierarchical Evaluation: In SQL CASE WHEN statements, always evaluate the most complex and restrictive conditions (such as AND combinations) first.
• CTE Modularization: Breaking down the query into logical steps (CT1 -> CT2 -> AggregatedMetrics -> Final Select) makes the SQL code maintainable, readable, and easier to debug.