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

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

1SELECT user_id, post_id, post_date, likes,  
2       SUM(likes) OVER (PARTITION BY user_id) AS total_likes_per_user  
3FROM 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

1SELECT user_id, post_id, post_date, likes,  
2       RANK() OVER (PARTITION BY user_id ORDER BY likes DESC) AS rank_per_user  
3FROM 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

1SELECT user_id, post_id, post_date, likes,  
2       SUM(likes) OVER (PARTITION BY user_id ORDER BY post_date) AS running_total_likes  
3FROM 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.