Last Minute SQL Interview Prep Kit — Date-Time Arithmetic: Calculating Employee Work Hours & Malware Detection Analysis

This chapter focuses on advanced techniques for handling temporal sequences, ranking events chronologically, and calculating differences between consecutive runs. Through real-world interview questions from top-tier companies like LinkedIn, we explore how to leverage window functions, Common Table Expressions (CTEs), and conditional aggregation to solve complex time-series and event-based SQL problems.

1. Wrap-Up of the Previous Challenge: Conditional Criteria & Ordering

Before diving into temporal event analysis, let us address a critical syntax detail from the previous employee criteria challenge. When writing conditional logic using CASE WHEN statements, maintaining data type consistency across all branches is paramount.

The Type Consistency Rule

If your CASE statement returns a variable character (VARCHAR) string in one branch, all other branches must also return compatible string types. Mixing data types (e.g., returning an integer 1 in one branch and a string '2' in another) will trigger runtime errors or unexpected implicit type conversions.

-- Correct: Consistent string types
CASE 
    WHEN criteria_count >= 3 THEN '1' 
    ELSE '2' 
END AS criteria

Ordering the Output

To ensure deterministic results that match the expected output format, always apply an explicit ORDER BY clause at the end of your query:

ORDER BY emp_id ASC;

2. The LinkedIn "Malware Detection" Problem Statement

In production environments, security systems run multiple antivirus engines simultaneously. Each engine logs its execution timestamp and the number of malwares detected during that specific run.

The Scenario

We are given a dataset containing execution logs for multiple antivirus software packages. Each row represents a single execution run of a software package.

Input Schema: malware_runs

• software_id (INT): Unique identifier for the antivirus software.
• run_date (DATETIME): The timestamp when the software execution started.
• malware_detected (INT): The number of malware threats identified during the run.

The Objective

Write an SQL query to find:

1. The number of malwares detected by each software in its latest run.
2. The difference in malware detections between the latest run and the second-to-last run.

Filtering Constraints

To eliminate noise, the output must only include software packages that meet the following conditions:

• Condition A: The software must have run at least two times ($N \ge 2$).
• Condition B: The software must have detected at least 10 malwares in its latest run.

3. Core Mental Model: Chronological Ranking & Filtering

To compare the latest run with the second-to-last run, we must establish a reliable chronological order for each software package individually.

The Data Flow Workflow

Why ROW_NUMBER() is the Optimal Choice

We use ROW_NUMBER() partitioned by software_id and ordered by run_date DESC. This guarantees that for every software group:

• The absolute latest run is assigned a rank of 1.
• The second-to-last run is assigned a rank of 2.

4. Step-by-Step SQL Evolution

Let us construct the solution incrementally, starting from raw ranking to final conditional filtering.

Step 1: Ranking the Runs

We write a Common Table Expression (CTE) to assign a reverse-chronological index to each run per software.

WITH RankedRuns AS (
    SELECT 
        software_id,
        run_date,
        malware_detected,
        ROW_NUMBER() OVER (
            PARTITION BY software_id 
            ORDER BY run_date DESC
        ) AS rn
    FROM malware_runs
)
SELECT * FROM RankedRuns;

Step 2: Filtering for the Top Two Runs

We only care about the latest run (rn = 1) and the second-to-last run (rn = 2). Any run with rn > 2 is irrelevant to our calculations and should be filtered out early to optimize performance.

WITH RankedRuns AS (
    SELECT 
        software_id,
        run_date,
        malware_detected,
        ROW_NUMBER() OVER (
            PARTITION BY software_id 
            ORDER BY run_date DESC
        ) AS rn
    FROM malware_runs
)
SELECT * 
FROM RankedRuns 
WHERE rn <= 2;

Step 3: Pivoting Rows with Conditional Aggregation

Now that we have at most two rows per software, we can group by software_id and use conditional CASE WHEN statements wrapped in aggregate functions (like MAX or SUM) to pivot these rows into distinct columns.

SELECT 
    software_id,
    MAX(CASE WHEN rn = 1 THEN malware_detected END) AS latest_count,
    MAX(CASE WHEN rn = 2 THEN malware_detected END) AS previous_count
FROM RankedRuns
WHERE rn <= 2
GROUP BY software_id;

Note: Since rn is unique per partition, MAX() simply extracts the single non-null value returned by the CASE statement for that specific rank.

5. Complete, Production-Ready SQL Solution

By combining our conditional aggregation with the final filtering constraints using a HAVING clause, we arrive at the complete, optimized query.

WITH RankedRuns AS (
    SELECT 
        software_id,
        run_date,
        malware_detected,
        ROW_NUMBER() OVER (
            PARTITION BY software_id 
            ORDER BY run_date DESC
        ) AS rn
    FROM malware_runs
),
FilteredRuns AS (
    -- Keep only the two most recent runs per software
    SELECT 
        software_id,
        malware_detected,
        rn
    FROM RankedRuns
    WHERE rn <= 2
)
SELECT 
    software_id,
    MAX(CASE WHEN rn = 1 THEN malware_detected END) AS latest_count,
    (
        MAX(CASE WHEN rn = 1 THEN malware_detected END) - 
        MAX(CASE WHEN rn = 2 THEN malware_detected END)
    ) AS diff_to_previous
FROM FilteredRuns
GROUP BY software_id
HAVING 
    -- Constraint A: Must have run at least two times
    COUNT(software_id) >= 2 
    -- Constraint B: Must have detected at least 10 malwares in the latest run
    AND MAX(CASE WHEN rn = 1 THEN malware_detected END) >= 10
ORDER BY software_id ASC;

Complexity Analysis

• Time Complexity: $\mathcal{O}(N \log K)$ where $N$ is the total number of rows in malware_runs and $K$ is the average number of runs per software_id (due to sorting within partitions).
• Space Complexity: $\mathcal{O}(N)$ to store the intermediate window function states in memory before filtering.

6. Edge Cases & Debugging Workflows

When presenting this solution in an interview, proactively discussing edge cases demonstrates senior-level engineering maturity.

Edge Case A: Software with Only One Run

• Problem: If a software has only run once, rn = 2 will not exist. The expression MAX(CASE WHEN rn = 2 THEN malware_detected END) will evaluate to NULL. Subtracting a NULL value from latest_count results in NULL.
• How our query handles it: The filter COUNT(software_id) >= 2 in the HAVING clause guarantees that any software with fewer than two runs is excluded entirely from the output.

Edge Case B: Identical Timestamps (run_date Collision)

• Problem: If two runs occur at the exact same millisecond, ROW_NUMBER() will arbitrarily assign 1 and 2 to them unless a secondary sorting column is provided.
• Mitigation: If deterministic behavior is strictly required for ties, add a secondary sorting key to the ORDER BY clause (e.g., ORDER BY run_date DESC, run_id DESC).

Edge Case C: Zero or Negative Malware Counts

• Problem: If malware counts can be negative or null due to system errors.
• Mitigation: Use COALESCE(malware_detected, 0) to handle null values safely during arithmetic operations.

Summary and Key Takeaways

• Type Consistency in CASE Statements: Always ensure that all return paths of a CASE WHEN expression share the same data type (e.g., wrap numeric literals in single quotes if other branches return strings).
• Chronological Ranking: Use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) to isolate the most recent events in a time-series dataset.
• Early Filtering: Use a CTE to filter on window ranks (rn <= 2) early. This reduces the dataset size before performing expensive grouping and aggregation operations.
• Conditional Aggregation: Combine MAX() or SUM() with CASE WHEN to pivot row-based sequential data (like "latest" and "second latest") into clean, side-by-side columns.
• Post-Aggregation Filtering: Use the HAVING clause to enforce multi-row business constraints, such as verifying minimum execution counts and threshold checks on pivoted values.