TECH **SQL for Data Analysis: LAG, LEAD, FIRST_VALUE, LAST_VALUE – Zero-Fluff Study Guide**

SQL for Data Analysis: LAG, LEAD, FIRST_VALUE, LAST_VALUE – Zero-Fluff Study Guide

1. What This Is & Why It Matters

You’re analyzing time-series data—sales, server logs, stock prices, or user activity—and you need to compare each row to its neighbors. Maybe you want to: - Calculate month-over-month growth (current month’s revenue vs. last month’s).
- Detect anomalies (a sudden spike in error logs compared to the previous hour).
- Rank performance (how does today’s conversion rate compare to the best/worst day in the quarter?).

Without window functions like LAG, LEAD, FIRST_VALUE, and LAST_VALUE, you’d resort to: - Self-joins (slow, messy, and hard to maintain).
- Cursors or loops (overkill for SQL).
- Manual Excel work (error-prone and not scalable).

These functions let you peek at other rows in the same result set without joining the table to itself. They’re your secret weapon for: ✅ Trend analysis (e.g., "Did sales drop after a price increase?").
✅ Gap detection (e.g., "Are there missing dates in this time series?").
✅ Ranking and benchmarking (e.g., "How does this user’s engagement compare to the best/worst in their cohort?").

Real-world scenario:
You’re analyzing a SaaS company’s subscription data. The CEO asks: "How many users downgraded their plan this month compared to last month?" With LAG, you can compare each user’s current plan to their previous one in a single query—no self-joins, no temporary tables.

2. Core Concepts & Components

? LAG(column, offset, default)

• Definition: Returns the value from a previous row in the same result set.
• Production insight: If you don’t specify offset, it defaults to 1 (previous row). If the row doesn’t exist (e.g., first row), it returns NULL unless you set a default.
• Example use case: Comparing today’s sales to yesterday’s.

? LEAD(column, offset, default)

• Definition: Returns the value from a future row in the same result set.
• Production insight: Useful for forecasting or detecting "next action" (e.g., "Did the user churn after this event?").
• Example use case: Predicting next month’s revenue based on current trends.

? FIRST_VALUE(column)

• Definition: Returns the first value in the current window frame.
• Production insight: Often used with ORDER BY to get the "best" or "worst" value in a group (e.g., "What was the first price this product was sold at?").
• Example use case: Finding the initial sign-up date for a cohort of users.

? LAST_VALUE(column)

• Definition: Returns the last value in the current window frame.
• Production insight: ⚠️ Tricky! By default, the window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE often returns the current row’s value. You must adjust the frame to RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING to get the true last value.
• Example use case: Finding the most recent price of a product.

? Window Frame (ROWS BETWEEN / RANGE BETWEEN)

• Definition: Defines the subset of rows around the current row that the function considers.
• Production insight: Without a frame, some functions (like LAST_VALUE) behave unexpectedly. Always specify: sql ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- Looks at previous, current, and next row RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- Default for most functions
• Example use case: Calculating a 3-day moving average.

? PARTITION BY

• Definition: Divides the result set into groups (like GROUP BY, but doesn’t collapse rows).
• Production insight: Essential for comparing rows within a group (e.g., "For each user, show their previous purchase").
• Example use case: Analyzing user behavior by country.

? ORDER BY (in window functions)

• Definition: Determines the order of rows within each partition.
• Production insight: Without ORDER BY, window functions return arbitrary results.
• Example use case: Sorting sales data by date to calculate month-over-month growth.

3. Step-by-Step Hands-On Section

Prerequisites

• A SQL environment (PostgreSQL, MySQL 8+, BigQuery, Snowflake, or SQL Server).
• A table with time-series data. We’ll use this example:

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    product_id INT,
    revenue DECIMAL(10,2),
    user_id INT
);

INSERT INTO sales VALUES
(1, '2023-01-01', 101, 100.00, 1),
(2, '2023-01-02', 101, 120.00, 1),
(3, '2023-01-03', 102, 80.00, 2),
(4, '2023-01-04', 101, 90.00, 1),
(5, '2023-01-05', 103, 200.00, 3),
(6, '2023-01-06', 102, 85.00, 2);

Task: Analyze Sales Trends with Window Functions

Goal: For each sale, show: 1. The previous day’s revenue for the same product.
2. The next day’s revenue for the same product.
3. The first and last revenue for each product.

Step 1: Compare to Previous Day (LAG)

SELECT
    sale_date,
    product_id,
    revenue,
    LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_day_revenue
FROM sales;

Output:
| sale_date | product_id | revenue | prev_day_revenue | |------------|------------|---------|------------------| | 2023-01-01 | 101 | 100.00 | NULL | | 2023-01-02 | 101 | 120.00 | 100.00 | | 2023-01-04 | 101 | 90.00 | 120.00 | | 2023-01-03 | 102 | 80.00 | NULL | | 2023-01-06 | 102 | 85.00 | 80.00 | | 2023-01-05 | 103 | 200.00 | NULL |

Why it works:
- PARTITION BY product_id groups sales by product.
- ORDER BY sale_date sorts sales chronologically.
- LAG(revenue, 1) looks at the previous row’s revenue.

Step 2: Compare to Next Day (LEAD)

SELECT
    sale_date,
    product_id,
    revenue,
    LEAD(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS next_day_revenue
FROM sales;

Output:
| sale_date | product_id | revenue | next_day_revenue | |------------|------------|---------|------------------| | 2023-01-01 | 101 | 100.00 | 120.00 | | 2023-01-02 | 101 | 120.00 | 90.00 | | 2023-01-04 | 101 | 90.00 | NULL | | 2023-01-03 | 102 | 80.00 | 85.00 | | 2023-01-06 | 102 | 85.00 | NULL | | 2023-01-05 | 103 | 200.00 | NULL |

Step 3: Find First and Last Revenue (FIRST_VALUE and LAST_VALUE)

SELECT
    sale_date,
    product_id,
    revenue,
    FIRST_VALUE(revenue) OVER (PARTITION BY product_id ORDER BY sale_date) AS first_revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- Critical for LAST_VALUE!
    ) AS last_revenue
FROM sales;

Output:
| sale_date | product_id | revenue | first_revenue | last_revenue | |------------|------------|---------|---------------|--------------| | 2023-01-01 | 101 | 100.00 | 100.00 | 90.00 | | 2023-01-02 | 101 | 120.00 | 100.00 | 90.00 | | 2023-01-04 | 101 | 90.00 | 100.00 | 90.00 | | 2023-01-03 | 102 | 80.00 | 80.00 | 85.00 | | 2023-01-06 | 102 | 85.00 | 80.00 | 85.00 | | 2023-01-05 | 103 | 200.00 | 200.00 | 200.00 |

Why LAST_VALUE needs a frame:
- Without RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, LAST_VALUE would return the current row’s value (not the last in the partition).

Step 4: Calculate Month-over-Month Growth

WITH daily_sales AS (
    SELECT
        sale_date,
        product_id,
        revenue,
        LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_revenue
    FROM sales
)
SELECT
    sale_date,
    product_id,
    revenue,
    prev_revenue,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS pct_growth
FROM daily_sales
WHERE prev_revenue IS NOT NULL;

Output:
| sale_date | product_id | revenue | prev_revenue | pct_growth | |------------|------------|---------|--------------|------------| | 2023-01-02 | 101 | 120.00 | 100.00 | 20.00 | | 2023-01-04 | 101 | 90.00 | 120.00 | -25.00 | | 2023-01-06 | 102 | 85.00 | 80.00 | 6.25 |

4. ? Production-Ready Best Practices

Performance

• Index PARTITION BY and ORDER BY columns to speed up window functions.
• Avoid SELECT * with window functions—they can explode memory usage.
• Use ROWS BETWEEN instead of RANGE BETWEEN when possible (faster for large datasets).

Readability

• Alias window functions clearly (e.g., LAG(revenue) AS prev_revenue).
• Comment complex frames (e.g., -- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING for LAST_VALUE).
• Break CTEs into logical steps (e.g., first calculate LAG, then compute growth).

Error Handling

• Handle NULL values (e.g., LAG(revenue, 1, 0) to default to 0).
• Validate ORDER BY—wrong sorting = wrong results.
• Test edge cases (first/last rows, empty partitions).

Maintainability

• Document assumptions (e.g., "Assumes sale_date is unique per product").
• Use consistent naming (e.g., prev_ for LAG, next_ for LEAD).
• Avoid nested window functions (hard to debug).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "Which function returns the previous row’s value?"
2. ❌ FIRST_VALUE (wrong)

3. ✅ LAG (correct)

4. "How do you find the last value in a partition?"

5. ❌ LAST_VALUE(column) (without a frame)

6. ✅ LAST_VALUE(column) OVER (ORDER BY ... RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

7. "What’s the difference between ROWS BETWEEN and RANGE BETWEEN?"

8. ROWS BETWEEN: Physical rows (e.g., "previous 2 rows").

9. RANGE BETWEEN: Logical values (e.g., "all rows with the same date").

10. "How would you calculate a 7-day moving average?"
    sql
AVG(revenue) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

Key Trap Distinctions

• LAG vs. LEAD: LAG looks backward, LEAD looks forward.
• FIRST_VALUE vs. MIN: FIRST_VALUE returns the first row’s value, MIN returns the smallest value.
• LAST_VALUE default frame: Without a frame, it returns the current row’s value.

7. ? Hands-On Challenge (with Solution)

Challenge:
You have a table user_activity with columns user_id, activity_date, and session_duration. Write a query to: 1. Show each user’s session duration.
2. Compare it to their previous session’s duration.
3. Flag sessions where duration dropped by >50% compared to the previous session.

Solution:

SELECT
    user_id,
    activity_date,
    session_duration,
    LAG(session_duration, 1) OVER (PARTITION BY user_id ORDER BY activity_date) AS prev_duration,
    CASE
        WHEN LAG(session_duration, 1) OVER (PARTITION BY user_id ORDER BY activity_date) IS NOT NULL
             AND session_duration < 0.5 * LAG(session_duration, 1) OVER (PARTITION BY user_id ORDER BY activity_date)
        THEN 'DROP >50%'
        ELSE 'No drop'
    END AS drop_flag
FROM user_activity;

Why it works:
- LAG fetches the previous session’s duration.
- The CASE statement compares current vs. previous duration.
- PARTITION BY user_id ensures comparisons are per user.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Window Functions Docs – Official guide with examples.
2. Mode Analytics SQL Tutorial – Interactive SQL exercises (including window functions).
3. SQLZoo Window Functions – Hands-on practice.
4. "SQL for Data Analysis" (O’Reilly) – Chapter 6 covers window functions in depth.