1729. Find Followers Count

Problem¶

Given a Followers(user_id, follower_id) table, count how many followers each user_id has and return results sorted by user_id.

Input:

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

Output:

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

Key trick¶

Group by the followed user, user_id, and count rows in each group.

Trap¶

Grouping by follower_id instead of user_id.
Forgetting ORDER BY user_id.
Trying to output users with zero followers, although there is no separate Users table.
Using COUNT(follower_id) can be less robust than COUNT(*) if nulls are possible.

Why is it interesting?¶

It checks the most fundamental aggregation pattern: group rows by an entity and compute a count.

SQL solution¶

SELECT
    user_id,
    COUNT(*) AS followers_count -- each row is one follower relationship
FROM Followers
GROUP BY user_id
ORDER BY user_id;

Pandas solution¶

import pandas as pd

def count_followers(followers: pd.DataFrame) -> pd.DataFrame:
    return (
        followers
        .groupby("user_id", as_index=False)
        .size()  # counts rows per user_id
        .rename(columns={"size": "followers_count"})
        .sort_values("user_id")
        .reset_index(drop=True)
    )

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQL_QUERY = """
SELECT
    user_id,
    COUNT(*) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;
"""


def count_followers(followers: pd.DataFrame) -> pd.DataFrame:
    return (
        followers
        .groupby("user_id", as_index=False)
        .size()
        .rename(columns={"size": "followers_count"})
        .sort_values("user_id")
        .reset_index(drop=True)
    )


@pytest.mark.parametrize(
    "rows, expected",
    [
        (
            [(0, 1), (1, 0), (2, 0), (2, 1)],
            [(0, 1), (1, 1), (2, 2)],
        ),
        (
            [(5, 10)],
            [(5, 1)],
        ),
        (
            [(3, 1), (1, 2), (3, 4), (1, 5), (2, 9)],
            [(1, 2), (2, 1), (3, 2)],
        ),
        (
            [],
            [],
        ),
    ],
)
def test_count_followers_sql_and_pandas(rows, expected):
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE Followers(user_id INTEGER, follower_id INTEGER)")
    conn.executemany(
        "INSERT INTO Followers(user_id, follower_id) VALUES (?, ?)",
        rows,
    )

    sql_result = conn.execute(SQL_QUERY).fetchall()
    assert sql_result == expected

    followers = pd.DataFrame(rows, columns=["user_id", "follower_id"])
    pandas_result = count_followers(followers)

    assert list(pandas_result.columns) == ["user_id", "followers_count"]
    assert list(pandas_result.itertuples(index=False, name=None)) == expected

Comment on my solution¶

Your SQL solution is correct and interview-ready.

Your pandas solution is also correct; using size is a good choice because it counts rows, matching COUNT(*).

SELECT
    user_id,
    COUNT(*) as followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;

import pandas as pd

def count_followers(followers: pd.DataFrame) -> pd.DataFrame:
    result = (
        followers.groupby("user_id", as_index=False)
        .agg(followers_count=("follower_id", "size"))
        .sort_values("user_id")
        .reset_index(drop=True)
    )

    return result[["user_id", "followers_count"]]

Extra¶

`COUNT(*)` and `COUNT(column)` are not exactly the same¶

COUNT(*) and COUNT(column) are not exactly the same.

COUNT(*) counts all rows in the group.
COUNT(follower_id) counts only rows where follower_id IS NOT NULL.

Example:

user_id | follower_id
--------+------------
1       | 10
1       | NULL
1       | 20

Query:

SELECT
    user_id,
    COUNT(*) AS count_star,
    COUNT(follower_id) AS count_column
FROM Followers
GROUP BY user_id;

Result:

user_id | count_star | count_column
--------+------------+-------------
1       | 3          | 2

Why?

COUNT(*) says: there are 3 rows for user_id = 1.
COUNT(follower_id) says: there are 2 non-null follower_id values.

In this LeetCode problem, (user_id, follower_id) is the primary key, so follower_id cannot be NULL in most SQL databases. Therefore both are effectively the same here.

But in interviews, COUNT(*) is usually preferred when you mean “count rows”, because it is clearer and safe if the counted column could contain nulls.