FIRST_VALUE, LAST_VALUE, Nth_VALUE

🎬 Scenario: Identifying Key Engagement Posts

Your manager at Meta, Jordan, wants insights into the first, last, and most engaging posts within user activity groups.

Can you provide engagement insights by retrieving the most liked post for each user, as well as their first and last post?

Your task is to use FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() to analyze user engagement patterns. Use the meta_posts table in this lesson:

1. Understanding FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE()

Window functions allow you to retrieve specific row values while keeping the full dataset.

Syntax for FIRST_VALUE, LAST_VALUE, and NTH_VALUE

1SELECT column1,  
2       function(column2) OVER (PARTITION BY column ORDER BY column) AS result  
3FROM table_name;

• PARTITION BY groups data into subsets (optional).
• ORDER BY column defines row sequence.

2. Using FIRST_VALUE() to Find the First Post per User

Jordan wants to identify the first post each user made based on post date.

Example Query: Retrieving First Post Per User

1SELECT user_id, post_id, post_date, likes,  
2       FIRST_VALUE(post_id) OVER (PARTITION BY user_id ORDER BY post_date) AS first_post  
3FROM meta_posts;

What’s Happening?

• FIRST_VALUE(post_id) OVER (PARTITION BY user_id ORDER BY post_date) retrieves the earliest post each user made.
• The first post remains the same across all of a user’s rows.

3. Using LAST_VALUE() to Find the Most Recent Post

Jordan now wants to identify the last post made by each user.

Example Query: Retrieving Last Post Per User

1SELECT user_id, post_id, post_date, likes,  
2       LAST_VALUE(post_id) OVER (PARTITION BY user_id ORDER BY post_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_post  
3FROM meta_posts;

What’s Happening?

• LAST_VALUE(post_id) OVER (...) retrieves the latest post per user.
• The function requires a frame specification (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to ensure it looks at all rows in the partition.

4. Using NTH_VALUE() to Find the Second Most Liked Post

Jordan wants to retrieve the second most liked post for each user.

Example Query: Finding the Second Most Liked Post

1SELECT user_id, post_id, post_date, likes,  
2       NTH_VALUE(post_id, 2) OVER (PARTITION BY user_id ORDER BY likes DESC) AS second_most_liked_post  
3FROM meta_posts;

What’s Happening?

• NTH_VALUE(post_id, 2) OVER (PARTITION BY user_id ORDER BY likes DESC) retrieves the second most liked post per user.
• If a user has only one post, the result is NULL.

✍️ SQL Exercises

Exercise 1: Finding the First Post Per User

Jordan wants to identify the first post made by each user.

Filter condition:

• Retrieve user ID, post ID, post date, likes, and first post ID.
• Sort by user_id and post_date in ascending order.

Write an SQL query to return the requested data.

Exercise 2: Finding the Second Most Liked Post

Jordan also wants to retrieve the second most liked post per user.

Filter condition:

• Retrieve user ID, post ID, post date, likes, and second most liked post ID.
• Sort by user_id and likes in descending order.

Write an SQL query to return the requested data.