SQL LAG and LEAD: A Comprehensive Guide 🎯

Welcome to our deep dive into SQL LAG and LEAD functions! These powerful tools will help you tackle common data analysis challenges, making your SQL queries even more powerful. Let's get started!

What are LAG and LEAD? 💡

LAG and LEAD are window functions in SQL that allow you to refer to rows that are not the current row. LAG looks backward to the preceding rows, while LEAD looks forward to the following rows.

LAG Function 📝

The LAG function returns the value of an expression from a specified row that is offset a given number of rows before the current row.

sql

LAG(expression, offset, default_value) OVER (ORDER BY sort_expression)

expression: The column or calculation you want to reference.
offset: The number of rows to move back from the current row.
default_value: A value to return if no previous row exists.
sort_expression: The column used to determine the row positions.

LEAD Function 📝

The LEAD function returns the value of an expression from a specified row that is offset a given number of rows after the current row.

sql

LEAD(expression, offset, default_value) OVER (ORDER BY sort_expression)

expression: The column or calculation you want to reference.
offset: The number of rows to move forward from the current row.
default_value: A value to return if no future row exists.
sort_expression: The column used to determine the row positions.

Practical Example 🔨

Let's consider a simple table of sales data:

sql

CREATE TABLE sales_data (
    sale_id INT PRIMARY KEY,
    product VARCHAR(20),
    sale_date DATE,
    sales INT
);

INSERT INTO sales_data VALUES
    (1, 'Product A', '2022-01-01', 100),
    (2, 'Product B', '2022-01-02', 200),
    (3, 'Product C', '2022-01-03', 150),
    (4, 'Product D', '2022-01-04', 250),
    (5, 'Product E', '2022-01-05', 300);

LAG Example 🔨

Let's find the sales amount of the previous day for each sale:

sql

SELECT
    sale_id,
    product,
    sale_date,
    sales,
    LAG(sales) OVER (ORDER BY sale_date) AS prev_day_sales
FROM sales_data;

LEAD Example 🔨

Now, let's find the sales amount of the next day for each sale:

sql

SELECT
    sale_id,
    product,
    sale_date,
    sales,
    LEAD(sales) OVER (ORDER BY sale_date) AS next_day_sales
FROM sales_data;

Quiz Time 🧮

Happy learning, and remember to practice with different data sets to fully grasp the power of LAG and LEAD functions! 💪