Find Followers Count - Problem
You are given a Followers table that contains information about user-follower relationships in a social media app.
Table: Followers
user_id(int): The ID of the user being followedfollower_id(int): The ID of the user who is following- The combination
(user_id, follower_id)is the primary key
Write a SQL solution to find the number of followers for each user. Return the result table ordered by user_id in ascending order.
Table Schema
Followers
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | ID of the user being followed |
follower_id
PK
|
int | ID of the user who is following |
Primary Key: (user_id, follower_id)
Note: Each row represents a follower relationship where follower_id follows user_id
Input & Output
Example 1 — Multiple Users with Followers
Input Table:
| user_id | follower_id |
|---|---|
| 1 | 3 |
| 2 | 3 |
| 2 | 1 |
Output:
| user_id | followers_count |
|---|---|
| 1 | 1 |
| 2 | 2 |
💡 Note:
User 1 has 1 follower (user 3). User 2 has 2 followers (users 3 and 1). Results are ordered by user_id ascending.
Example 2 — Single User Multiple Followers
Input Table:
| user_id | follower_id |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
Output:
| user_id | followers_count |
|---|---|
| 1 | 3 |
💡 Note:
User 1 has 3 followers (users 2, 3, and 4). The GROUP BY aggregates all followers for user 1.
Example 3 — Each User One Follower
Input Table:
| user_id | follower_id |
|---|---|
| 1 | 2 |
| 3 | 4 |
Output:
| user_id | followers_count |
|---|---|
| 1 | 1 |
| 3 | 1 |
💡 Note:
User 1 has 1 follower (user 2) and user 3 has 1 follower (user 4). Results ordered by user_id.
Constraints
-
1 ≤ user_id, follower_id ≤ 1000 -
user_id != follower_id(users cannot follow themselves)
Visualization
Tap to expand
Understanding the Visualization
1
Input
Followers table with user-follower pairs
2
Group & Count
GROUP BY user_id and COUNT followers
3
Output
User IDs with their follower counts
Key Takeaway
🎯 Key Insight: Use GROUP BY with COUNT() to aggregate data and calculate totals per group
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code