Lesson 18: Stop Grouping! See Previous Rows Without Collapsing Data

8. Introduction: Why This Matters in Data Engineering

Welcome back to the series. If you've ever built a dashboard where every single row gets collapsed into a grand total sum, or if you've felt frustrated by having to join your table with a self-join just to see "yesterday's" value, this lesson is for you.

In the world of software engineering and data analysis, we often fall into the trap of over-summarizing data. We are taught early on that GROUP BY is our friend, but it can also be a foe when you need to retain individual row granularity while still having context about neighboring rows.

Interviewers at top tech companies love to test this specific skill. They want to know: "Do you understand the difference between aggregating data and maintaining its structure?" They are looking for candidates who can perform period-over-period analysis—like calculating a month-over-month growth rate—without resorting to clunky self-joins or temporary tables that slow down your query plan.

By mastering these tools, you move from writing basic SQL queries to writing professional-grade analytical queries. You stop summarizing; you start exploring.

9. Problem Overview

The challenge we are tackling today is simple to state but powerful in application: How can we access data from the previous or next row relative to the current row without collapsing our result set?

In traditional SQL, if you want to know what the price was yesterday while looking at today's price, you might be tempted to join the table to itself. You'd create a current_date table and a previous_date table and match them. But that is heavy on resources and often unnecessary.

We need a way to say, "For this specific record (ordered by date), give me the value of the record that came immediately before it," and we want to do this inside the same row. We want to keep our full list of records intact, not turn them into a single aggregated summary. This is where Window Functions come in, specifically those designed for looking back (Lag) and forward (Lead).

10. Examples

Let's ground this with some concrete data. Imagine we are tracking the stock price of a fictional company, "MainStreet Inc." over five days. We want to calculate the drop in price from Day $N$ to Day $N-1$.

Input Data

Date	Price
2023-01-01	110
2023-01-02	108
2023-01-03	115
2023-01-04	105
2023-01-05	112

Desired Output

We want our final table to look exactly like the input, but with two new columns added: Price_Prev and Price_Next.

Date	Price	Price_Prev	Price_Next
2023-01-01	110	NULL	108
2023-01-02	108	110	115
2023-01-03	115	108	105
2023-01-04	105	115	112
2023-01-05	112	105	NULL

Explanation of Output:

• Row 1 (Jan 1): There is no previous row, so Price_Prev is NULL. The next price is 108.
• Row 2 (Jan 2): The previous price was 110. The next price is 115.
• Last Row (Jan 5): There is no next row, so Price_Next is NULL.

11. Intuition: The Moving Car Analogy

How do we solve this? Imagine you are driving a car down "Main Street," which represents our timeline ordered by date.

You are the current row (the SELECT *). As you drive, you can see two things clearly without stopping your engine or merging into a different parking spot:

1. The house to your left (the one you just passed). This is your Previous value.
2. The house ahead of you that is coming up on the horizon. This is your Next value.

In data terms, we are "windowing" into the dataset. We tell SQL: "Look at this specific window of rows around me."

• If we look strictly back one step, we use LAG.
• If we look strictly forward one step, we use LEAD.
• If we want to know what was there very long ago or the first thing in the set, we might need FIRST_VALUE (though in this specific 'moving' context, LAG handles it via recursion or iteration logic).

The key intuition here is context. Standard SELECT statements are about retrieving a snapshot. Window functions allow us to retrieve the relationship between rows while preserving the original row.

12. Brute Force Solution

Before we use the magic window functions, let's look at how an engineer might attempt this without knowing them. This is often called the "Brute Force" or "Self-Join" approach.

The Idea

To find the previous price, you need to join your table (prices) with itself. You create an alias curr for the current row and prev for the row from yesterday. You join them on a condition like: curr.date = prev.date + INTERVAL '1 day' (or whatever your time unit is).

Algorithm

1. Create a self-join between two instances of the table.
2. Match each row in curr to the immediate predecessor in prev.
3. Select columns from both tables.

Advantages

• Universal: Works on almost every SQL dialect (Postgres, MySQL, Oracle, Snowflake) even if they didn't support window functions yet.

Disadvantages

• Performance: If your table has 10 million rows, you are creating a temporary relationship matrix that is computationally expensive ($O(N^2)$ in worst-case scenarios without strict indexing).
• Complexity: The SQL becomes messy quickly as you try to handle gaps (e.g., weekends with no data).
• Readability: It's hard to read compared to the window function one-liner.

Complexity Analysis (Brute Force)

• Time Complexity: $O(N)$ if indexed correctly on dates, but setup overhead is high. Without perfect indexes, it degrades significantly.
• Space Complexity: High, as you are holding two full datasets in memory to perform the join.

13. Optimal Solution: Using Window Functions

Now, let's write the professional solution using LAG, LEAD, and proper ordering. This approach keeps everything in a single pass through the data.

The Approach

We use the syntax: FUNCTION(column_name) OVER (ORDER BY column_sequence)

The OVER clause is the container that tells SQL "Look at the ordered sequence of rows." Inside that window, we ask LAG to peek back one row.

Step-by-Step Breakdown

1. Define the Frame: We use ORDER BY date. This is crucial. Without ordering, SQL doesn't know what "previous" means (it could be alphabetical or random).
2. Select Columns: We select all original columns.
3. Apply LAG: We add LAG(Price) OVER (ORDER BY Date) to fetch the previous price.
4. Apply LEAD: We add LEAD(Price) OVER (ORDER BY Date) to fetch the next price.

This creates a "sliding window" that moves down your result set, one row at a time.

14. Python Code

Here is a clean, production-quality solution using standard SQL syntax (compatible with PostgreSQL, Snowflake, BigQuery, and MySQL 8+). If you are running this in Python via sqlite3 or a pandas DataFrame context that supports SQL execution, this logic holds true.

def get_row_context_query(df_data):
    """
    Generates the optimal SQL query string to access previous/next rows.
    This assumes 'df_data' is represented as a dictionary of columns for illustration.
    
    Returns a formatted SQL Query string.
    """
    
    # Define the column names dynamically based on input if needed, 
    # but here we assume standard schema: 'date', 'price'
    base_columns = ["date", "price"]
    window_exprs = [
        f"LAG({base_columns[1]}) OVER (ORDER BY {base_columns[0]}) AS price_prev",
        f"LEAD({base_columns[1]}) OVER (ORDER BY {base_columns[0]}) AS price_next"
    ]

    # Constructing the query manually to demonstrate structure
    columns_clause = ", ".join([c for c in base_columns] + window_exprs)
    
    query = f"""
    SELECT 
        {columns_clause}
    FROM prices_table -- Replace with your actual table name
    ORDER BY date
    """
    
    return query

# Example usage logic (conceptual Python representation of SQL result):
# If you were doing this in Pandas directly for comparison:
import pandas as pd

# Simulated data preparation
df = pd.DataFrame({
    "date": ["2023-01-01", "2023-01-02", "2023-01-03"],
    "price": [110, 108, 115]
})

# Pandas equivalent logic using shift (which maps to SQL LAG/LEAD)
# df['price_prev'] = df['price'].shift(1) 

15. Code Walkthrough

Let's break down the SQL query returned by the function above line by line.

• SELECT: We start by selecting all our base columns: date and price.
• LAG(price) OVER (...): This is the core window function.
  • LAG(price): Tells the engine to take the value from column price.
  • OVER (ORDER BY date): This defines the window frame. It says, "Organize all rows by date." Then, look back one row in that specific order.
• AS price_prev: We alias the result so we know this new column represents the previous price.
• LEAD(...): Similar to LAG, but instead of looking back at the start of the window frame, it looks forward to the next element.
• ORDER BY date (at the end): While often redundant inside a window function if ordered correctly, keeping an outer ORDER BY ensures your final display matches the chronological timeline perfectly.

16. Dry Run: Walking Through an Example

Let's trace the execution plan mentally for our "Main Street" car analogy with three days of data.

Date	Price	LAG Logic (Look Back)	LEAD Logic (Look Forward)
Day 1	100	NULL (No row before Day 1 to look back at)	110 (The price in the next row, Day 2)
Day 2	110	100 (We are now "at" Day 2. The engine looks behind us to Day 1)	120 (The price in the next row, Day 3)
Day 3	120	110 (Looking back to Day 2)	NULL (No row after Day 3 exists)

Execution Flow:

1. The database sorts the data by date.
2. It initializes a "window" for Row 1. It calculates LAG: finds nothing before, returns NULL. It calculates LEAD: finds Row 2's price (110), returns 110.
3. It slides the window to Row 2. LAG now sees Row 1's price (100). LEAD looks at Row 3 (120).
4. It slides to Row 3. LAG sees Row 2 (110). LEAD finds nothing, returns NULL.

17. Complexity Analysis

Why is this better than the brute force method?

• Time Complexity: $O(N)$. The database scans the table exactly once (or a logarithmic amount of times depending on indexing for sorting), calculating values in parallel with the sort operation. It does not need to construct a massive temporary join matrix.
• Space Complexity: $O(1)$ additional space per row relative to the input size, effectively $O(N)$ total for the output, but it doesn't require creating intermediate result sets like self-joins do. The "window" is virtual; it exists in memory only as a pointer during the sort pass.

18. Alternative Solutions

Are there other ways to look at previous/next rows?

FIRST_VALUE and LAST_VALUE

You might see FIRST_VALUE(price) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). This is essentially a way to say "Get the very first price in the entire history." While valid, it's less dynamic than LAG because it doesn't move with your current row; it always points to the static start of the dataset. LAG is the tool for relative movement.

NTH_VALUE (Oracle/Postgres specific)

Some databases offer NTH_VALUE(price, -1) which mimics LAG perfectly. However, standard SQL and LeetCode environments usually prefer the explicit LAG syntax for clarity.

Comparison:

• Self-Join: Powerful but heavy ($O(N \log N)$ or worse). Hard to maintain.
• LAG/LEAD: Lightweight ($O(N)$), readable, standard in modern SQL.

19. Edge Cases

What happens when things get weird?

1. Empty Input: If the table is empty, LAG and LEAD simply return no rows. No errors occur.
2. Gaps in Dates: What if you have a row for Jan 1st and then immediately Jan 3rd (Jan 2nd was missing)?
   • Since we ordered by date, LAG will see Jan 1st as the previous row to Jan 3rd. This is usually desired behavior in analytics unless you explicitly want to skip weekends using a specific gap-filling window frame (e.g., RANGE BETWEEN).
3. Duplicates: If two rows have the exact same timestamp, the order is technically indeterminate without a secondary sort key (like an id). If you want strict chronology with duplicates, add ORDER BY date, id.
4. NULL Values: LAG will propagate NULLs correctly. If price is NULL on Day 2, LAG on Day 3 will correctly see that NULL from Day 2.

20. Common Mistakes

Here are five traps I see developers fall into during interviews and production:

1. Forgetting the ORDER BY: This is the #1 mistake. If you don't specify an order, LAG doesn't know which row is "previous." You might get a random previous value or NULLs everywhere.
2. Using Group By: Don't use GROUP BY here! LAG is a window function that works on rows. If you GROUP BY your data, you collapse it into one row per group, and the window function disappears.
3. Misunderstanding NULLs: Beginners expect LAG to "skip" over NULL prices. It does not. It looks at the row, regardless of whether the value inside is NULL or not. You must handle NULL logic explicitly (e.g., COALESCE(LAG(price)...)).
4. Wrong Partitioning: If your data spans multiple years and you calculate Year-over-Year growth, you might need PARTITION BY year. Without it, LAG looks at the very last row of 2023 as the previous row for 2024. Always check if you need to partition!

SELECT 
    date,
    sales,
    LAG(sales) OVER (ORDER BY date) as prev_sales
FROM monthly_sales;