LAG and LEAD

🎬 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:

1. Understanding LAG() and LEAD()#

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

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

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

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

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.

Write an SQL query to return the requested data.

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.

Write an SQL query to return the requested data.