Find Followers Count

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

Output

Try It Yourself

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. We count the follower_id column to determine 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.

SELECT user_id,
       COUNT(follower_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(follower_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              |
+---------+----------------+

Pattern: count-per-key

Name: count-per-key — GROUP BY user_id + COUNT(*) or COUNT(follower_id) to get follower cardinality per followee.

COUNT(*) vs COUNT(col): if follower_id is NOT NULL (typical for a follow edge PK), both match. COUNT(col) skips NULL col values; COUNT(*) counts rows. Prefer COUNT(*) for "number of edge rows."

Missing-group: users with zero followers never appear in a GROUP BY driven only from Followers. If a Users table exists and the report must show zeros:

SELECT u.user_id, COUNT(f.follower_id) AS followers_count
FROM Users u
LEFT JOIN Followers f ON f.user_id = u.user_id
GROUP BY u.user_id;

When-NOT: driving from Followers alone is correct when the problem only asks for users who appear as followees in the edge table (this LeetCode-style problem usually does).

Drill: Return only users with at least 2 followers (HAVING COUNT(*) >= 2). Sample: user 2 only.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — Find Followers Count

Why this concept exists (judgment layer)

Canonical count-per-key: GROUP BY followee + COUNT. Teaches COUNT(*) vs COUNT(col) and the missing-group zero-follower problem when Users is the universe.

Mental model (install this intuition)

Each Followers edge is one follow. Group by user_id (followee). COUNT(*) = number of edges. Users with zero edges absent unless LEFT JOIN from Users.

Worked example with numbers or traced steps

Edges: (0←1), (1←0), (2←0), (2←1)
GROUP BY user_id → 0:1, 1:1, 2:2
User 99 never followed → not in result (edge-only universe)
With Users LEFT JOIN: 99 gets COUNT=0
HAVING COUNT(*) >= 2 → only user 2

When NOT to use / named alternative

If report must include zero-follower accounts, do not drive only from Followers. If you need mutual follows or graph depth, this pattern is insufficient — use self-join/graph.

Failure mode & ops fingerprint

Fingerprint: COUNT(user_id) confuses readers; omitting ORDER BY when problem requires it; double-counting if join to Users multiplies before aggregate.

Hostile-panel drills (defend the decision)

Q1. COUNT(*) vs COUNT(follower_id) here?
Model answer: Same if follower_id NOT NULL (PK). Prefer COUNT(*) for row counts; COUNT(col) skips NULL col.

Q2. How to include users with zero followers?
Model answer: FROM Users u LEFT JOIN Followers f ON f.user_id=u.user_id GROUP BY u.user_id with COUNT(f.follower_id).

Q3. Pattern name?
Model answer: count-per-key (GROUP BY dimension + COUNT measure).