LAG and LEAD for row comparison in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the LAG() function do in SQL?

LAG() lets you look at a value from a previous row in the same result set without using a self-join. It's like looking back one step in a list.

beginner

How does the LEAD() function differ from LAG()?

LEAD() looks forward to the next row's value, while LAG() looks backward to the previous row's value. Both help compare rows easily.

intermediate

What is a common use case for LAG and LEAD functions?

They are often used to compare current row values with previous or next rows, like finding changes over time or calculating differences between rows.

intermediate

Write a simple SQL snippet using LAG() to get the previous month's sales for each row.

SELECT month, sales, LAG(sales) OVER (ORDER BY month) AS prev_month_sales FROM sales_data;

beginner

What happens if there is no previous row when using LAG()?

LAG() returns NULL if there is no previous row to look at, so you can handle missing data gracefully.

What does LEAD() function return?

A Value from the previous row

B Current row value

C Sum of all rows

D Value from the next row

LEAD() returns the value from the next row in the result set.

Which clause is required to use LAG() or LEAD() functions?

A ORDER BY inside OVER()

B WHERE

C GROUP BY

D HAVING

LAG() and LEAD() require an ORDER BY clause inside the OVER() to define row order.

If you want to compare each row with the row two steps before, which function and parameter do you use?

A LEAD(column, 1)

B LEAD(column, 2)

C LAG(column, 2)

D LAG(column, 1)

LAG(column, 2) looks two rows back from the current row.

What value does LAG() return if there is no previous row?

A 0

B NULL

C Empty string

D Error

LAG() returns NULL when no previous row exists.

Which of these is NOT a use case for LAG and LEAD?

A Looking up values in unrelated tables

B Calculating running totals

C Finding differences between rows

D Comparing current row with previous or next row

LAG and LEAD work within the same result set, not for unrelated table lookups.

Explain how LAG and LEAD functions help compare rows in a table.

Think about looking backward and forward in a list.

Write a SQL query using LEAD() to find the next month's sales for each month in a sales table.

Use LEAD() with ORDER BY month inside OVER()