Why LAG function for previous row access in SQL? - Purpose & Use Cases

The Big Idea

What if you could instantly see yesterday's data next to today's without flipping pages or writing notes?

The Scenario

Imagine you have a list of daily sales numbers on paper, and you want to compare each day's sales to the previous day. You try to do this by flipping back and forth between pages or writing down extra notes to remember the previous day's number.

The Problem

This manual way is slow and confusing. You might lose track of which day you are on, make mistakes copying numbers, or waste time flipping pages. It's hard to keep everything organized and accurate.

The Solution

The LAG function in SQL lets you look at the previous row's value easily, right next to the current row. It automatically remembers the previous data for you, so you can compare or calculate differences without any extra effort.

Before vs After

✗ Before

SELECT date, sales FROM daily_sales;
-- Then manually compare each row with the previous one outside SQL

✓ After

SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS previous_day_sales FROM daily_sales;

What It Enables

It makes comparing current and past data simple and fast, unlocking powerful trend analysis and insights.

Real Life Example

A store manager can quickly see how today's sales compare to yesterday's, helping decide if a promotion worked or if changes are needed.

Key Takeaways

Manually tracking previous rows is slow and error-prone.

LAG function automatically accesses previous row data in SQL.

This enables easy comparisons and better data insights.