Problem

Table: Followers

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| follower_id | int  |
+-------------+------+
(user_id, follower_id) is the primary key (combination of columns with unique values) for this table.
This table contains the IDs of a user and a follower in a social media app where the follower follows the user.

Problem Definition

Write a solution that will, for each user, return the number of followers.

Return the result table ordered by user_id in ascending order.

Example

Image scaled to 30%

Output

Image scaled to 30%

Try It Yourself

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Solution

To solve this problem, the approach involves using SQL queries to analyze the Followers table and determine the number of followers for each user in a social media app. The table consists of pairs of user and follower IDs, representing the follower-followee relationships.

The solution employs the COUNT function along with the GROUP BY clause to group the data based on the user_id. This allows the calculation of the count of followers for each user. The COUNT function counts the occurrences of each unique user_id in the table, providing the number of followers for each user.

The result set is then ordered by user_id in ascending order using the ORDER BY clause, as specified in the problem statement. This ensures that the final table presents the number of followers for each user, organized by the user IDs.

SELECT user_id,
       Count(user_id) AS followers_count
FROM   Followers
GROUP  BY user_id
ORDER  BY user_id; 

Let’s break down the query step by step:

Step 1: Counting followers for each user

We use the COUNT function to count the number of followers for each user_id by grouping the results based on user_id.

SELECT user_id,
       Count(user_id) AS followers_count
FROM   Followers
GROUP  BY user_id

Output After Step 1:

+---------+----------------+
| user_id | followers_count|
+---------+----------------+
| 0       | 1              |
| 1       | 1              |
| 2       | 2              |
+---------+----------------+

Step 2: Ordering the result by user_id

Finally, we order the result by user_id in ascending order as requested.

ORDER BY user_id;

Final Output:

+---------+----------------+
| user_id | followers_count|
+---------+----------------+
| 0       | 1              |
| 1       | 1              |
| 2       | 2              |
+---------+----------------+