Table: Follow
+-------------+---------+ | Column Name | Type | +-------------+---------+ | followee | varchar | | follower | varchar | +-------------+---------+ (followee, follower) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates that the user follower follows the user followee on a social network. There will not be a user following themself.
A second-degree follower is a user who:
Write a solution to report the second-degree users and the number of their followers.
Return the result table ordered by follower in alphabetical order.
The result format is in the following example.
Example 1:
Input: Follow table: +----------+----------+ | followee | follower | +----------+----------+ | Alice | Bob | | Bob | Cena | | Bob | Donald | | Donald | Edward | +----------+----------+ Output: +----------+-----+ | follower | num | +----------+-----+ | Bob | 2 | | Donald | 1 | +----------+-----+ Explanation: User Bob has 2 followers. Bob is a second-degree follower because he follows Alice, so we include him in the result table. User Donald has 1 follower. Donald is a second-degree follower because he follows Bob, so we include him in the result table. User Alice has 1 follower. Alice is not a second-degree follower because she does not follow anyone, so we don not include her in the result table.
Problem Overview: The Second Degree Follower problem gives a follow table with two columns: follower and followee. A second-degree follower of user C exists when user A follows B and B follows C. The task is to return each followee who has at least one such second-degree follower and count how many distinct users form those relationships.
Approach 1: Self Join + GROUP BY Aggregation (O(n²) time, O(1) extra space)
The clean SQL approach uses a self join on the follow table. Treat the table as two logical copies: f1 and f2. Join them where f1.followee = f2.follower. This join links the first step (A → B) with the second step (B → C), producing the chain that defines a second-degree follower. Once the join produces these pairs, group the results by f2.followee (the final user in the chain) and count distinct f1.follower values.
The key insight is that a second-degree relationship is just a two-hop path in the follow graph. A SQL self join models this path directly. GROUP BY combined with COUNT(DISTINCT ...) ensures each second-degree follower is counted once. Rows are filtered with a HAVING clause so only users with at least one such follower appear in the result.
This pattern appears frequently in relational graph-style problems. When a table represents edges in a social graph, joining the table with itself lets you traverse relationships multiple steps deep. Understanding this technique is essential for many SQL and database interview questions.
Indexes on followee and follower significantly improve performance because the join condition relies on these columns. Without indexes, the database engine may scan the table multiple times, resulting in roughly O(n²) join behavior. With proper indexing, the practical runtime is closer to O(n log n) depending on the optimizer.
Recommended for interviews: The self join with GROUP BY is the expected solution. It demonstrates that you understand relational joins, graph traversal through tables, and aggregation in SQL. Explaining the two-hop relationship and translating it into a self join shows stronger database reasoning than relying on nested subqueries alone. This pattern also appears in problems involving SQL joins and social network graph queries.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join + GROUP BY | O(n²) without indexes | O(1) | Standard SQL solution for two-hop relationships in a follow graph |
| Self Join with Index Optimization | ~O(n log n) | O(1) | Preferred in production databases where indexes exist on follower/followee |
Leetcode MEDIUM 614 - Second Degree Follower JOIN Solution - Explained by Everyday Data Science • Everyday Data Science • 1,001 views views
Watch 4 more video solutions →Practice Second Degree Follower with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor