Using LAG() and LEAD() for Trend Analysis

Mastering advanced SQL analytics requires moving beyond basic aggregations to examine sequential data, sliding windows, and row-to-column transformations. This chapter explores rolling sums, sliding window variations, conditional pivoting strategies, join behavior mechanics, and complex analytical filtering used in high-stakes technical interviews.

1. Why Do We Need Trend Analysis and Windowing?

In modern data engineering and business intelligence, simple group-by aggregations are often insufficient. Businesses do not just want to know the total sales for a year; they want to track how sales evolve month-over-month, evaluate rolling performance windows, and pivot transactional rows into wide executive dashboards.

• Granularity Mismatch: Raw operational tables (like orders) contain multiple line items per day or month. Analysts must first aggregate data to match the target analytical granularity (e.g., monthly sales).
• Sequential Context: Standard SQL queries look at individual rows in isolation. Window functions (LAG(), LEAD(), SUM() OVER()) allow rows to inspect neighboring rows without collapsing the result set.

2. Core Mental Model: Rolling Sums and Window Framing

When calculating a rolling sum (e.g., a 3-month rolling window), the database engine creates a sliding frame across ordered partitions of data.

Real-Life Analogy: The Sliding Accounting Ledger

Imagine looking down a chronological ledger of monthly expenses. To calculate a 3-month rolling sum for March, you place a physical 3-row index card over January, February, and March, sum those numbers, and write down the total. When moving to April, you slide the index card down by one row (dropping January, including April). SQL window frames (ROWS BETWEEN ...) programmatically replicate this sliding card.

• Standard 3-Month Rolling Sum: Includes the current month plus the two preceding months.
  Structure & Concept Flow

  Copy DiagramCopy

  Month 1: 23,000 \

  Month 2: 28,000 } -> Sum = 106,000 (Current 3-Month Window)

  Month 3: 55,000 /

   

3. Naive vs. Advanced Window Framing: Excluding the Current Row

A common business requirement in financial reporting is calculating a rolling average or sum excluding the current, incomplete period (since the current month's sales data might still be accumulating).

The Naive Approach (Including Current Month)

SELECT 
    order_month,
    monthly_sales,
    SUM(monthly_sales) OVER (
        ORDER BY order_month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3m_inclusive
FROM monthly_sales_summary;

The Production Approach (Excluding Current Month)

To exclude the current row while capturing the prior three months, adjust the frame boundaries to use 3 PRECEDING AND 1 PRECEDING:

SELECT 
    order_month,
    monthly_sales,
    SUM(monthly_sales) OVER (
        ORDER BY order_month 
        ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ) AS rolling_3m_exclusive
FROM monthly_sales_summary;

4. Granularity Matching Before Windowing

A frequent interview trap is attempting to apply window functions or running sums directly on low-level transactional tables without pre-aggregating.

• Rule of Thumb: If your question asks for monthly trends, your dataset's rows must represent individual months before you invoke SUM() OVER (ORDER BY month).
• If you run window functions on unaggregated tables, your window frames will partition across individual order transactions rather than consolidated monthly totals, yielding wildly incorrect results.

5. Pivoting Rows to Columns Using Conditional Aggregation

Interviewers frequently test your ability to transform vertical row data into horizontal pivot columns (e.g., categories as separate columns per year). While databases support PIVOT syntax, using Conditional Aggregation (CASE WHEN) is universal, highly readable, and portable across all SQL dialects.

The Unpivoted Starting View

-- Standard Grouped Query
SELECT 
    YEAR(order_date) AS order_year,
    category,
    SUM(sales) AS sales
FROM orders
GROUP BY YEAR(order_date), category;

Transforming Rows to Columns with CASE WHEN

To pivot categories (Furniture, Office Supplies, Technology) into individual columns, strip out the category from the GROUP BY clause and wrap individual category sums inside conditional statements:

SELECT 
    YEAR(order_date) AS order_year,
    
    SUM(CASE WHEN category = 'Furniture' THEN sales ELSE 0 END) AS furniture_sales,
    
    SUM(CASE WHEN category = 'Office Supplies' THEN sales ELSE 0 END) AS office_supplies_sales,
    
    SUM(CASE WHEN category = 'Technology' THEN sales ELSE 0 END) AS technology_sales

FROM orders
GROUP BY YEAR(order_date);

6. Understanding Join Mechanics and Output Cardinality

When tackling complex data integration questions involving inner, left, right, and full outer joins, interviewers test your mental model of Cartesian products and null handling.

Consider two small sample tables:

• Table A: [1, 1, 2, NULL, 3, 3]
• Table B: [2, NULL, 1, 1, 4, 3]

• Inner Join: Retains only matching non-null key intersections.
• Left Join: Retains all rows from the left table, padding unmatched right table columns with NULL.
• Key Interview Warning: Always trace duplicate keys carefully. If Table A has 3 instances of ID = 5 and Table B has 2 instances of ID = 5, an inner join will produce $3 \times 2 = 6$ output rows for that key.

7. Real-World Case Study: Meta Interview Question — Workaholic Employees

As demonstrated in technical interview platforms (such as namastesql.com, Question #14: Workaholic Employees), real-world SQL problems combine filtering, conditional aggregation, and multi-criteria evaluation.

Problem Breakdown

• Objective: Identify "workaholic employees" based on specific performance or behavioral criteria from an employee and activity log schema.
• Criteria Evaluation: Employees who satisfy at least one defined threshold (e.g., excessive working hours, weekend shifts, or lack of standard breaks).
• Execution Strategy:
  1. Inspect table schemas and data types.
  2. Write modular subqueries or CTEs to isolate each criterion.
  3. Combine flags using logical operators (OR or UNION) depending on performance optimization needs.

-- Conceptual skeleton for multi-criteria workaholic evaluation
WITH EmployeeMetrics AS (
    SELECT 
        employee_id,
        SUM(CASE WHEN worked_weekend = 1 THEN 1 ELSE 0 END) AS weekend_shifts,
        AVG(daily_hours) AS avg_daily_hours
    FROM employee_logs
    GROUP BY employee_id
)
SELECT employee_id
FROM EmployeeMetrics
WHERE weekend_shifts >= 4 
   OR avg_daily_hours > 10;

Summary and Key Takeaways

• Pre-Aggregate First: Always ensure your table granularity matches your analytical question before applying window functions or rolling sums.
• Control Window Frames: Use ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING to exclude incomplete current periods from rolling calculations.
• Master Conditional Pivoting: Replace complex pivot syntax with SUM(CASE WHEN condition THEN value ELSE 0 END) grouped by the target dimension for universal database compatibility.
• Account for Duplicate Multipliers: In joins, matching duplicate keys multiply output rows via Cartesian products; always anticipate row inflation.
• Handle Nulls Explicitly: Remember that standard SQL equality comparisons fail on NULL values during join and filter operations.
• Leverage Window Functions for Trends: Use LAG() and LEAD() to compare sequential rows without requiring complex self-joins.
• Break Down Complex Interviews: Approach multi-criteria problems (like Meta's Workaholic Employees) by isolating each business rule into clear CTE components before final filtering.