Rolling Sums and Moving Windows (ROWS BETWEEN)

An advanced SQL interview preparation masterclass focusing on conditional aggregations, window framing techniques, and multi-criteria business logic evaluation for employee productivity analysis.

1. Problem Statement: Identifying Workaholic Employees

In enterprise human resources and workforce analytics, organizations frequently need to evaluate employee engagement and working hour distributions over a standard weekly cycle. The objective of this problem is to ingest raw daily login and logout timestamps for a group of employees, compute their exact daily working hours, and determine who qualifies as a "workaholic" based on two distinct operational criteria.

The Business Rules & Criteria

• Criteria 1: An employee has worked for more than 8 hours a day for at least 3 days within the given one-week window.
• Criteria 2: An employee has worked for more than 10 hours a day for at least 2 days within the given one-week window.
• Output Requirements: Identify all employees meeting either or both criteria, report the specific criteria satisfied (1, 2, or Both), and display the final result sorted in order of increasing employee ID.

2. Real-Life Analogy: The Overtime Audit

Imagine an automated auditing system inside a corporate office building.

• Every time an employee badges in at the turnstile in the morning and badges out in the evening, a security log records the exact minute.
• At the end of the week, the HR manager does not just look at the total sum of hours crammed into a single day; instead, they pull a calendar grid.
• The manager runs two highlighters: a yellow highlighter to mark any day exceeding 8 hours, and a pink highlighter for marathon days exceeding 10 hours.
• If the yellow highlights stack up to 3 or more days, the employee triggers Criteria 1. If the pink highlights stack up to 2 or more days, they trigger Criteria 2. If both conditions are met, the auditor logs them under Both.

3. Naive Approach vs. Production-Ready Time Calculations

When beginners attempt to calculate duration between timestamps in SQL, they often rely on coarse built-in functions like DATEDIFF(hour, login, logout), which truncate fractional hours and introduce severe rounding errors.

The Pitfall of Truncated Date Diffs

If an employee logs in at 09:15 and logs out at 18:20, the actual duration is 9 hours and 5 minutes (9.083 hours).

• Using an integer-based hour difference function might truncate this to 9 or 8 depending on database dialects.
• For precise threshold checks (like strictly greater than 8.0 or 10.0 hours), truncation leads to missed records or false positives.

The Precise Minute-Division Strategy

To achieve high fidelity, we calculate the exact difference in minutes, cast the result to a floating-point decimal by multiplying by 1.0, and divide by 60.0.

SELECT 
    employee_id,
    login_time,
    logout_time,
    (DATEDIFF(minute, login_time, logout_time) * 1.0) / 60.0 AS hours_worked
FROM employee_logs;

4. Step-by-Step Evolution: Building Common Table Expressions (CTEs)

To solve this cleanly without writing monolithic, unmaintainable queries, we break the logic down into sequential modular layers using Common Table Expressions (CTEs).

Raw Employee Logs Table
       |
       |  (Step 1: Calculate precise decimal hours worked per day)
       v
CTE 1: Daily Hours Worked
       |
       |  (Step 2: Apply CASE statements to categorize daily time windows)
       v
CTE 2: Categorized Time Windows
       |
       |  (Step 3: Aggregate and evaluate weekly criteria thresholds)
       v
Final Result: Workaholic Employees & Satisfied Criteria

5. Handling Conditional Priority in CASE Statements

Once we have the exact hours_worked for each employee per day, we must categorize each work day into specific buckets: 10+, 8+, or 8-.

The Ordering Trap in CASE Logic

SQL evaluates CASE WHEN branches sequentially from top to bottom, stopping at the very first condition that evaluates to true.

• If we check for hours_worked > 8 before checking for hours_worked > 10, an employee who worked 11 hours will immediately match the > 8 condition, get classified as 8+, and bypass the 10+ check entirely.
• Rule of Thumb: Always place the most restrictive, highest threshold condition at the top of your CASE statement.

-- CORRECT CASE STATEMENT ORDERING
SELECT 
    employee_id,
    log_date,
    hours_worked,
    CASE 
        WHEN hours_worked > 10 THEN '10+'
        WHEN hours_worked > 8 THEN '8+'
        ELSE '8-'
    END AS time_window
FROM cte_daily_hours;

6. Complete Flow Walkthrough: Code Implementation

Below is the complete, production-grade SQL script demonstrating the multi-CTE architecture to solve the workaholic employee challenge.

-- Step 1: Compute exact decimal hours worked per employee per day
WITH cte_daily_hours AS (
    SELECT 
        employee_id,
        log_date,
        (DATEDIFF(minute, login_time, logout_time) * 1.0) / 60.0 AS hours_worked
    FROM employee_logs
),

-- Step 2: Categorize daily performance into time windows
cte_time_windows AS (
    SELECT 
        employee_id,
        log_date,
        hours_worked,
        CASE 
            WHEN hours_worked > 10 THEN '10+'
            WHEN hours_worked > 8 THEN '8+'
            ELSE '8-'
        END AS time_window
    FROM cte_daily_hours
),

-- Step 3: Aggregate occurrences per employee to test criteria thresholds
cte_employee_aggregates AS (
    SELECT 
        employee_id,
        SUM(CASE WHEN time_window IN ('10+', '8+') THEN 1 ELSE 0 END) AS days_gt_8,
        SUM(CASE WHEN time_window = '10+' THEN 1 ELSE 0 END) AS days_gt_10
    FROM cte_time_windows
    GROUP BY employee_id
)

-- Step 4: Final Selection and Criteria Flag Mapping
SELECT 
    employee_id,
    CASE 
        WHEN days_gt_8 >= 3 AND days_gt_10 >= 2 THEN 'Both'
        WHEN days_gt_8 >= 3 THEN '1'
        WHEN days_gt_10 >= 2 THEN '2'
    END AS criteria_satisfied
FROM cte_employee_aggregates
WHERE days_gt_8 >= 3 OR days_gt_10 >= 2
ORDER BY employee_id ASC;

7. Comparative Summary of Time Window Strategies

8. Summary and Key Takeaways

• Avoid Integer Truncation: Always convert timestamp differences into fine-grained units (minutes or seconds) and multiply by a floating-point literal (1.0) before dividing to prevent accidental data loss.
• Top-Down CASE Evaluation: Always place stricter, higher numerical thresholds at the top of conditional CASE blocks to prevent broader conditions from prematurely intercepting values.
• Modular CTE Architecture: Break complex analytical problems down into digestible steps—compute raw metrics first, apply business categories second, aggregate third, and filter/format output last.
• Conditional Aggregation: Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) inside aggregate queries to pivot row-level categorical attributes into countable metrics per entity.
• Multi-Criteria Evaluation: Structure final output filters using logical OR operators and conditional mapping to handle cases where entities satisfy multiple business rules simultaneously.