PARTITION BY

🎬 Scenario: Analyzing Engagement per User#

Your manager at Meta, Jordan, wants to analyze user engagement patterns, specifically:

How many likes has each user received across all their posts? Can we also find their highest liked post?

Your task is to use PARTITION BY with window functions to segment and analyze user-level engagement. Use the meta_posts table in this lesson:

1. Understanding PARTITION BY in SQL#

The PARTITION BY clause groups rows into subsets without aggregating them into a single row, like GROUP BY does.

Syntax for PARTITION BY

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

• PARTITION BY groups the dataset into logical segments.
• Unlike GROUP BY, it retains all rows and applies the function within each partition.

2. Using PARTITION BY to Find Total Likes Per User#

Jordan wants a report showing the total number of likes each user has received across all their posts, while keeping each post visible.

Example Query: Calculating Total Likes Per User

SELECT user_id, post_id, post_date, likes,  
       SUM(likes) OVER (PARTITION BY user_id) AS total_likes_per_user  
FROM meta_posts;

What’s Happening?

• SUM(likes) OVER (PARTITION BY user_id) calculates total likes per user.
• Each post still appears in the result, but all posts for a user show the same total.

3. Using PARTITION BY to Rank Posts Per User#

Jordan now wants to rank posts per user based on likes, so they can identify each user’s most popular post.

Example Query: Ranking Posts Per User by Likes

SELECT user_id, post_id, post_date, likes,  
       RANK() OVER (PARTITION BY user_id ORDER BY likes DESC) AS rank_per_user  
FROM meta_posts;

What’s Happening?

• RANK() OVER (PARTITION BY user_id ORDER BY likes DESC) assigns rankings separately for each user.
• The highest liked post for each user is ranked 1.

4. Using PARTITION BY to Track Running Totals Per User#

Jordan also wants to track how engagement grows for each user over time.

Example Query: Running Total of Likes Per User

SELECT user_id, post_id, post_date, likes,  
       SUM(likes) OVER (PARTITION BY user_id ORDER BY post_date) AS running_total_likes  
FROM meta_posts;

What’s Happening?

• SUM(likes) OVER (PARTITION BY user_id ORDER BY post_date) tracks engagement growth for each user.
• Helps understand how engagement accumulates over time.

✍️ SQL Exercises#

Exercise 1: Calculating Total Likes Per User#

Jordan wants to find the total number of likes each user has received across all posts, while keeping each post visible.

Filter condition:

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

Write an SQL query to return the requested data.

Exercise 2: Ranking Posts Per User by Likes#

Jordan also wants to rank each user’s posts based on likes.

Filter condition:

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

Write an SQL query to return the requested data.