Find Followers Count | Leetcode 1729 | Crack SQL Interviews in 50 Qs

A comprehensive guide to solving LeetCode 1729 ("Find Followers Count"), focusing on data aggregation, the GROUP BY clause, and handling row counts in SQL databases.

1. Why Do We Need This? (Problem Statement & Status Quo)

In modern social media and networking applications, keeping track of social graph metrics is essential for user engagement, analytics, and platform features.

• The Challenge: Given a database log of who follows whom, platform features often require generating user statistics—specifically, computing the total number of followers for every individual user.
• The Objective: Write an SQL query that returns each user's unique identifier along with their total follower count, ordered in ascending order by user_id.

2. Core Mental Model & Database Schema

To understand how follower relationships are stored, we examine the structure of the input table.

Table Schema: followers

• Key Constraint: The combination of (user_id, follower_id) serves as the Primary Key for this table. This means a specific user cannot follow the same target user more than once (no duplicate relationship rows).

Real-Life Analogy: The Fan Mailbox

Note: Imagine a celebrity or content creator who has a physical mailbox for fan letters. Every time a fan sends a letter, their ID is logged. To find out how many distinct fans a creator has, you don't just dump all letters and count papers; you group the letters by the creator's name and count how many unique follower cards are inside each creator's slot.

3. Input Data Example and Expected Output

Let's examine a concrete dataset to visualize how input rows map to aggregated output rows.

Input Table: followers

Step-by-Step Row Breakdown:

• User 0 is followed by User 1 $\rightarrow$ Total followers = 1
• User 1 is followed by User 0 $\rightarrow$ Total followers = 1
• User 2 is followed by User 0 and User 1 $\rightarrow$ Total followers = 2

Expected Output Table

4. Naive vs. Better Approach

When designing aggregate queries, selecting the correct clauses prevents errors and ensures optimal performance.

5. Step-by-Step Query Construction

We build our production SQL query iteratively, moving from data selection to grouping and aliasing.

Step 5.1: Select the Source Table

We begin by referencing the base table containing our relationship mappings:

FROM followers

Step 5.2: Group by User

To calculate metrics per unique user, we partition the rows using the GROUP BY clause:

GROUP BY user_id

Step 5.3: Select and Count

We project the user_id and evaluate the aggregate count of followers:

SELECT 
    user_id, 
    COUNT(follower_id) AS followers_count
FROM followers
GROUP BY user_id;

6. Complete Solution Query

Here is the complete, clean SQL solution accepted by LeetCode:

SELECT 
    user_id, 
    COUNT(follower_id) AS followers_count
FROM 
    followers
GROUP BY 
    user_id
ORDER BY 
    user_id ASC;

Complete Execution Flow Walkthrough:

followers Table (Raw Rows)
   |
   | executes FROM clause
   v
Group Rows by user_id (Partitions data for user 0, 1, 2)
   |
   | applies COUNT(follower_id) aggregation per partition
   v
Project columns: user_id & followers_count
   |
   | applies ORDER BY user_id ASC (Optional: ASC is default)
   v
Final Result Table sent to client

7. Sorting Nuances (ORDER BY Behavior)

• Default Sorting: In many SQL database engines (including MySQL/InnoDB), if a primary key or grouped column is involved, results may naturally appear sorted, but relying on implicit ordering is bad practice.
• Explicit Ordering: Adding ORDER BY user_id ASC guarantees that output rows are returned in ascending order of user IDs.
• Descending Alternative: If the problem required descending order, we would append DESC:
  SQL

  Copy

  ORDER BY user_id DESC
  

8. Summary and Key Takeaways

• Aggregation Foundation: Use GROUP BY whenever you need to compute metrics (like counts, sums, or averages) across distinct categories or entity IDs.
• Counting Rows: COUNT(column_name) or COUNT(*) efficiently tallies the records belonging to each specific group partition.
• Table Constraints: Leveraging underlying table schemas (such as primary keys ensuring unique (user_id, follower_id) pairs) simplifies aggregate logic by preventing duplicate entries.
• Aliasing for Clarity: Always use the AS keyword to provide clear, readable output column aliases (e.g., AS followers_count).
• Ordering Control: Use ORDER BY explicitly when a specific result sequence is required by business logic or problem constraints.
• Scalability: Database-level aggregation runs significantly faster than pulling raw records into application memory for manual counting.