SQL LAG and LEAD

Lesson Objectives

By the end of this lesson, you will:

Learn how to use LAG() to retrieve the previous row’s value
Use LEAD() to retrieve the next row’s value
Write SQL queries to analyze user engagement trends on Meta posts

🎬 Scenario: Analyzing Engagement Trends Over Time

Your manager at Meta, Jordan, wants insights into how engagement changes between consecutive posts.

Can you compare the likes of each post with the previous and next post?

Your task is to use LAG() and LEAD() to analyze engagement trends over time. Use the meta_posts table in this lesson:

post_id	user_id	post_content	post_date	likes	shares	post_status
1	1	“Had a great day at the beach!”	“2024-01-10T14:30:00”	120	45	“Public”
2	2	“Launching our new product!”	“2024-01-12T09:20:00”	250	90	“Friends Only”
3	3	“Just finished a 10K run!”	“2024-01-15T18:10:00”	80	30	“Public”
4	4	“Hosting a webinar next week.”	“2024-01-18T20:45:00”	50	25	“Private”
5	5	“Traveling to Japan soon!”	“2024-01-20T10:05:00”	180	75	“Public”
6	1	“Excited for the weekend!”	“2024-01-25T16:15:00”	95	40	“Friends Only”
7	2	“Need recommendations for a new laptop.”	“2024-01-27T12:40:00”	70	20	“Public”
8	3	“Learning SQL window functions!”	“2024-01-29T08:55:00”	110	50	“Public”
9	4	“Working on a new AI model.”	“2024-02-01T14:00:00”	200	100	“Friends Only”
10	5	“Book recommendations?”	“2024-02-05T11:30:00”	65	15	“Public”

1. Understanding LAG() and LEAD()

Function	What It Does	Example Use Case
LAG()	Returns the value from the previous row	Compare the current post’s likes to the previous post
LEAD()	Returns the value from the next row	Predict engagement changes by looking ahead

Syntax for LAG() and LEAD()

1SELECT column1,  
2       LAG(column2) OVER (ORDER BY column3) AS prev_value,  
3       LEAD(column2) OVER (ORDER BY column3) AS next_value  
4FROM table_name;

ORDER BY column3 defines the sequence of rows.
LAG() retrieves data from the previous row.
LEAD() retrieves data from the next row.

2. Using LAG() to Compare Engagement with Previous Posts

Jordan wants to compare likes between consecutive posts.

Example Query: Finding Previous Post Likes

1SELECT post_id, post_date, likes,  
2       LAG(likes) OVER (ORDER BY post_date) AS prev_likes  
3FROM meta_posts;

post_id	post_date	likes	prev_likes
1	“2024-01-10T14:30:00”	120	null
2	“2024-01-12T09:20:00”	250	120
3	“2024-01-15T18:10:00”	80	250
4	“2024-01-18T20:45:00”	50	80
5	“2024-01-20T10:05:00”	180	50
6	“2024-01-25T16:15:00”	95	180
7	“2024-01-27T12:40:00”	70	95
8	“2024-01-29T08:55:00”	110	70
9	“2024-02-01T14:00:00”	200	110
10	“2024-02-05T11:30:00”	65	200

What’s Happening?

LAG(likes) OVER (ORDER BY post_date) retrieves likes from the previous post.
The first row has NULL since no previous post exists.

3. Using LEAD() to Compare Engagement with Next Posts

Jordan now wants to compare each post’s likes to the next post.

Example Query: Finding Next Post Likes

1SELECT post_id, post_date, likes,  
2       LEAD(likes) OVER (ORDER BY post_date) AS next_likes  
3FROM meta_posts;

post_id	post_date	likes	next_likes
1	“2024-01-10T14:30:00”	120	250
2	“2024-01-12T09:20:00”	250	80
3	“2024-01-15T18:10:00”	80	50
4	“2024-01-18T20:45:00”	50	180
5	“2024-01-20T10:05:00”	180	95
6	“2024-01-25T16:15:00”	95	70
7	“2024-01-27T12:40:00”	70	110
8	“2024-01-29T08:55:00”	110	200
9	“2024-02-01T14:00:00”	200	65
10	“2024-02-05T11:30:00”	65	null

What’s Happening?

LEAD(likes) OVER (ORDER BY post_date) retrieves likes from the next post.
The last row has NULL since no next post exists.

✍️ SQL Exercises

Exercise 1: Comparing Likes with Previous Posts

Jordan wants to compare the likes of each post with the previous post.

Filter condition:

Retrieve post ID, post date, likes, and previous likes.
Sort by post_date in ascending order.

post_id	post_date	likes	prev_likes
1	“2024-01-10T14:30:00”	120	null
2	“2024-01-12T09:20:00”	250	120
3	“2024-01-15T18:10:00”	80	250
4	“2024-01-18T20:45:00”	50	80
5	“2024-01-20T10:05:00”	180	50
6	“2024-01-25T16:15:00”	95	180
7	“2024-01-27T12:40:00”	70	95
8	“2024-01-29T08:55:00”	110	70
9	“2024-02-01T14:00:00”	200	110
10	“2024-02-05T11:30:00”	65	200

Write an SQL query to return the requested data.

Upgrade to Pro

Exercise 2: Comparing Likes with Next Posts

Jordan also wants to compare the likes of each post with the next post.

Filter condition:

Retrieve post ID, post date, likes, and next likes.
Sort by post_date in ascending order.

post_id	post_date	likes	next_likes
1	“2024-01-10T14:30:00”	120	250
2	“2024-01-12T09:20:00”	250	80
3	“2024-01-15T18:10:00”	80	50
4	“2024-01-18T20:45:00”	50	180
5	“2024-01-20T10:05:00”	180	95
6	“2024-01-25T16:15:00”	95	70
7	“2024-01-27T12:40:00”	70	110
8	“2024-01-29T08:55:00”	110	200
9	“2024-02-01T14:00:00”	200	65
10	“2024-02-05T11:30:00”	65	null

Write an SQL query to return the requested data.

Upgrade to Pro