0% completed
Problem Statement
Table: Posts
Each row in this table represents a post made by a user in a social media platform. It contains the unique post ID, the user who posted it, and the number of likes received by that post.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| post_id | int |
| user_id | int |
| post_likes | int |
+-------------+---------+
(post_id, user_id)
is a unique primary key in the above table.post_id
is the primary key for this table.user_id
refers to the ID of the user who has liked the post.
Develop a solution to find the number of likes for each post.
Return the result table ordered by post_id
in ascending order.
Example
Input:
Posts table: +---------+---------+ | post_id | user_id | +---------+---------+ | 1 | 2 | | 2 | 1 | | 1 | 3 | | 3 | 1 | | 1 | 4 | | 4 | 2 | | 2 | 2 | | 4 | 3 | +---------+---------+
Output:
+---------+-------------+ | post_id | post_likes | +---------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 2 | +---------+-------------+
Try It Yourself
Solution
To determine the number of likes each post has received, we analyze the Posts
table. Each row in the Posts
table represents a like made by a user (user_id
) on a specific post (post_id
). By aggregating this data, we can count how many users have liked each post, effectively determining the total number of likes per post.
Here are the steps for the solution.
-
Aggregate Likes per Post:
- Count the number of
user_id
s associated with eachpost_id
to determine the total likes per post.
- Count the number of
-
Order the Results:
- Present the final count of likes in ascending order of
post_id
for clarity and organization.
- Present the final count of likes in ascending order of
SQL Query
SELECT post_id, COUNT(user_id) AS post_likes FROM Posts GROUP BY post_id ORDER BY post_id ASC;
Step-by-Step Approach
Step 1: Select Relevant Columns
Identify the columns necessary for calculating the number of likes per post.
SQL Snippet:
SELECT post_id, COUNT(user_id) AS post_likes
Explanation:
-
SELECT post_id,
- *Retrieve the
post_id
to group likes per post.
- *Retrieve the
-
COUNT(user_id) AS post_likes
COUNT(user_id)
counts the number of non-NULLuser_id
entries for eachpost_id
.AS post_likes
aliases the count aspost_likes
for clarity in the output.
Step 2: Aggregate Data Using GROUP BY
Group the data by post_id
to perform aggregation on a per-post basis.
SQL Snippet:
GROUP BY post_id
Explanation:
- Groups all rows that have the same
post_id
into a single group. - The
COUNT(user_id)
function then operates within each group to count the number of likes.
Intermediate Output After Step 3:
+---------+-------------+ | post_id | post_likes | +---------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 2 | +---------+-------------+
Step 3: Order the Results
Sort the final results by post_id
in ascending order for organized presentation.
SQL Snippet:
ORDER BY post_id ASC;
Explanation:
ORDER BY post_id ASC;
ORDER BY post_id
specifies the column to sort by.ASC
denotes ascending order (from smallest to largest).
Final Output:
+---------+-------------+ | post_id | post_likes | +---------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 2 | +---------+-------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible