Find Followers Count | LeetCode | MSSQL
Tags: leetcode, mssql, database, tutorial
Published: 2023-07-09 17:10:00

## The Problem

Consider a table `Followers` with the following schema:

| Column Name | Type |
|-------------|------|
| user_id     | int  |
| follower_id | int  |

`(user_id, follower_id)` is the primary key 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. The task is to write an SQL query that will, for each user, return the number of followers. The result table should be ordered by `user_id` in ascending order.

## Explanation

Consider the following `Followers` table:

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

For this input, the expected output would be:

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

Here, each row represents a distinct `user_id`, and the count of followers for each user.

## The Solution

We will discuss two different approaches to solve this problem, highlight their strengths and weaknesses, and explain their underlying structures.

### Using GROUP BY

This approach directly counts the `follower_id` for each `user_id` using GROUP BY clause.

```sql
SELECT
    user_id,
    COUNT(follower_id) [followers_count]
FROM Followers
GROUP BY user_id
```

This query is fairly straightforward and easy to understand. It works by grouping the data by `user_id`, and then it counts the followers within each group. The advantage of this method is its simplicity and directness. However, GROUP BY clause can be slow on large data sets. This query runtime is 994ms, beating 86.63% of other submissions on LeetCode.

![source1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4e2hel6w3vo3iqncgpdi.png)

### Using COUNT() with PARTITION BY

This approach counts `follower_id` for each `user_id` using window function.

```sql
SELECT DISTINCT
    user_id,
    COUNT(follower_id) OVER(PARTITION BY user_id) [followers_count]
FROM Followers
```

This query, while similar to the first, leverages the power of window functions to achieve the same result. The PARTITION BY clause essentially creates a "window" of rows with the same `user_id`, and the COUNT function then operates on this window. This approach can sometimes offer performance improvements over the GROUP BY method, especially when dealing with larger data sets. However, in this case, the performance was slightly less, with a runtime of 1416ms, beating 24.50% of other submissions on LeetCode.

![source2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/38adti2cqytqvlkrb1o3.png)

## Conclusion

Both methods solve the problem, but their performances can vary based on the size and distribution of the data set. While the first solution using GROUP BY clause performed better on LeetCode, in a real-world scenario with a larger data set and a well-tuned database, the second method using window function might perform better. This example serves to illustrate the importance of understanding different methods and when each can be advantageous. Performance in LeetCode and real-world applications may not align perfectly, but practicing with various techniques helps us better prepare for different scenarios.

You can find the original problem at [LeetCode](https://leetcode.com/problems/find-followers-count/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% cta https://beacons.ai/ranggakd %} 👉 Check out all the links on my beacons.ai page 👈 {% endcta %}