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

SELECT column1,  
       function(column2) OVER (PARTITION BY column ORDER BY column) AS result  
FROM 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

SELECT user_id, post_id, post_date, likes,  
       FIRST_VALUE(post_id) OVER (PARTITION BY user_id ORDER BY post_date) AS first_post  
FROM 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

SELECT user_id, post_id, post_date, likes,  
       LAST_VALUE(post_id) OVER (PARTITION BY user_id ORDER BY post_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_post  
FROM 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

SELECT user_id, post_id, post_date, likes,  
       NTH_VALUE(post_id, 2) OVER (PARTITION BY user_id ORDER BY likes DESC) AS second_most_liked_post  
FROM 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.