Find Followers Count

Difficulty: Easy | Acceptance: 69.60% | Paid: No Topics: Database

Table: Followers

+-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | follower_id | int | +-------------+------+ (user_id, follower_id) is the primary key of this table. Each row of this table indicates that the user with ID follower_id is following the user with ID user_id.

Write an SQL query to find the number of followers for each user.

Return the result table ordered by user_id in ascending order.

The query result format is in the following example.

• Examples
• Constraints
• Approach 1: GROUP BY with COUNT
• Approach 2: Subquery with COUNT

Examples

Example 1:

Input: Followers table:

+---------+-------------+
| user_id | follower_id |
+---------+-------------+
| 0       | 1           |
| 1       | 0           |
| 2       | 1           |
+---------+-------------+

Output:

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

Explanation: The followers of 0 are 1 The followers of 1 are 2 The followers of 2 are 1

Constraints

1 <= user_id, follower_id <= 1000

GROUP BY with COUNT

Intuition Group the followers by user_id and count the number of followers in each group using the aggregate COUNT function.

Steps

• Select user_id and count of follower_id from the Followers table
• Group the results by user_id
• Order the results by user_id in ascending order

import sqlite3

def find_followers_count():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute('''
        SELECT user_id, COUNT(follower_id) AS followers_count
        FROM Followers
        GROUP BY user_id
        ORDER BY user_id
    ''')
    return cursor.fetchall()

# LeetCode SQL format
# SELECT user_id, COUNT(follower_id) AS followers_count
# FROM Followers
# GROUP BY user_id
# ORDER BY user_id

Complexity

• Time: O(n) where n is the number of rows in the Followers table
• Space: O(k) where k is the number of unique user_ids
• Notes: This is the most efficient and standard approach for aggregation queries

Subquery with COUNT

Intuition Use a correlated subquery to count followers for each distinct user_id by filtering rows where user_id matches.

Steps

• Select distinct user_id from the Followers table
• For each user_id, use a subquery to count matching follower_id
• Order the results by user_id in ascending order

import sqlite3

def find_followers_count():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute('''
        SELECT DISTINCT user_id, 
            (SELECT COUNT(*) FROM Followers f2 WHERE f2.user_id = f1.user_id) AS followers_count
        FROM Followers f1
        ORDER BY user_id
    ''')
    return cursor.fetchall()

# LeetCode SQL format
# SELECT DISTINCT user_id, 
#     (SELECT COUNT(*) FROM Followers f2 WHERE f2.user_id = f1.user_id) AS followers_count
# FROM Followers f1
# ORDER BY user_id

Complexity

• Time: O(n²) in the worst case due to correlated subquery execution
• Space: O(k) where k is the number of unique user_ids
• Notes: Less efficient than GROUP BY approach but demonstrates alternative SQL patterns