LEAD & LAG Functions

Part of the Fundamentals section of Coddy's SQL journey — lesson 61 of 72.

The LEAD and LAG functions allow us to access the value of the current row by n steps back or n steps ahead.

For example, if we want to calculate the ratio of a company's revenue for the current row and one month ago, we can extract the value from the previous month:

id	revenue	month
1	532	5
2	492	6
3	393	7
4	723	8

SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY id

This will create the following table:

id	revenue	prev_month_revenue
1	532
2	492	532
3	393	492
4	723	393

This way we can calculate the prev_month_revenue/revenue ratio.

If we instead used the LEAD function, it would take the next month's revenue of each row:

SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id

id	revenue	next_month_revenue
1	532	492
2	492	393
3	393	723
4	723

Challenge

Easy

Available tables and columns:
air_conditioners: id, efficiency, strength, month

Compare each air conditioner's efficiency with the previous one.

Write a query that shows each air conditioner's efficiency along with the efficiency of the previous air conditioner installed (based on id). Also, calculate the efficiency difference between the current and previous air conditioner. Order the result by id and efficiency in ascending order.

Expected output columns:

id
efficiency
previous_efficiency (using LAG)
efficiency_difference (current efficiency - previous efficiency)

Finally after this query, filter the row where there is an empty previous_efficiency or empty efficiency_difference like so:

SELECT * FROM (
    -- Your query here
)
WHERE previous_efficiency != 0

Cheat sheet

The LAG and LEAD functions allow access to values from previous or next rows:

LAG function - gets value from previous row:

LAG(column_name, n) OVER (ORDER BY column)

LEAD function - gets value from next row:

LEAD(column_name, n) OVER (ORDER BY column)

Example using LAG to get previous month's revenue:

SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue
FROM table1 ORDER BY id

Example using LEAD to get next month's revenue:

SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id

The first row will have NULL for the LAG value, and the last row will have NULL for the LEAD value.

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

LEAD & LAG Functions

Cheat sheet

All lessons in Fundamentals