Second Degree Follower - Problem
Given a social network Follow table with followee and follower columns, find all second-degree followers.
A second-degree follower is a user who:
- Follows at least one user (appears as follower)
- Is followed by at least one user (appears as followee)
Return the second-degree users and their follower count, ordered by follower alphabetically.
Table Schema
Follow
| Column Name | Type | Description |
|---|---|---|
followee
PK
|
varchar | User being followed |
follower
PK
|
varchar | User who follows |
Primary Key: (followee, follower)
Note: Each row represents a follow relationship. No self-following allowed.
Input & Output
Example 1 — Basic Second-Degree Followers
Input Table:
| followee | follower |
|---|---|
| A | B |
| A | C |
| B | D |
Output:
| follower | num |
|---|---|
| B | 1 |
💡 Note:
User B follows A (appears as follower) and is followed by D (appears as followee). B is a second-degree follower with 1 follower (D). Users A and C only appear as followee/follower respectively, so they don't qualify.
Example 2 — Multiple Second-Degree Followers
Input Table:
| followee | follower |
|---|---|
| A | B |
| B | C |
| B | D |
| D | E |
Output:
| follower | num |
|---|---|
| B | 2 |
| D | 1 |
💡 Note:
User B follows A and is followed by C and D (2 followers). User D follows B and is followed by E (1 follower). Both are second-degree followers, ordered alphabetically.
Example 3 — No Second-Degree Followers
Input Table:
| followee | follower |
|---|---|
| A | B |
| C | D |
Output:
| follower | num |
|---|
💡 Note:
No user appears in both roles. B only follows, A and C are only followed, D only follows. No second-degree followers exist.
Constraints
-
followee != follower(no self-following) -
1 ≤ number of relationships ≤ 1000 - All usernames are valid varchar strings
Visualization
Tap to expand
Understanding the Visualization
1
Input
Follow relationships table
2
Self-Join
Match followers with followees
3
Count
Group and count followers
Key Takeaway
🎯 Key Insight: Use self-join when you need to find records that satisfy multiple roles in the same table
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code