Second Degree Follower

Problem

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.

Problem Definition

A second-degree follower is a user who:

• follows at least one user, and
• is followed by at least one user.

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.

Example

Output

Try It Yourself

Solution

To identify second-degree followers—users who both follow at least one other user and are followed by at least one user—we can leverage SQL's self-join capabilities. The approach involves isolating users who meet both criteria and then counting their followers.

• Identify Users Who Follow Others: Determine users who are followers in the Follow table.
• Identify Users Who Are Followed: Determine users who are followees in the Follow table.
• Determine Second-Degree Followers: Find the intersection of users who follow others and are followed by others.
• Count Followers for Each Second-Degree Follower: For each second-degree follower, count the number of users who follow them.
• Order the Results: Present the final list ordered alphabetically by the follower's name.

SQL Query

SELECT f1.follower AS follower,
       COUNT(DISTINCT f2.follower) AS num
FROM   Follow f1
       INNER JOIN Follow f2
               ON f1.follower = f2.followee
GROUP  BY f1.follower
ORDER BY f1.follower ASC;

Step-by-Step Approach

Step 1: Identify Users Who Follow Others

Determine all users who follow at least one other user by selecting distinct followers from the Follow table.

SQL Query:

SELECT DISTINCT follower
FROM Follow;

Explanation:

• SELECT DISTINCT follower:
  • Retrieves unique users who act as followers in the Follow table.
• FROM Follow:
  • Specifies the Follow table as the data source.

Output After Step 1:

+----------+
| follower |
+----------+
| Bob      |
| Cena     |
| Donald   |
| Edward   |
+----------+

Step 2: Identify Users Who Are Followed

Determine all users who are followed by at least one other user by selecting distinct followees from the Follow table.

SQL Query:

SELECT DISTINCT followee
FROM Follow;

Explanation:

• SELECT DISTINCT followee:
  • Retrieves unique users who are followed in the Follow table.
• FROM Follow:
  • Specifies the Follow table as the data source.

Output After Step 2:

+----------+
| followee |
+----------+
| Alice    |
| Bob      |
| Donald   |
+----------+

Step 3: Determine Second-Degree Followers and Count Their Followers

Identify users who both follow others and are followed by others (second-degree followers) and count the number of their followers.

SQL Query:

SELECT f1.follower AS follower,
       COUNT(DISTINCT f2.follower) AS num
FROM   Follow f1
       INNER JOIN Follow f2
               ON f1.follower = f2.followee
GROUP  BY f1.follower
ORDER BY f1.follower ASC;

Explanation:

• FROM Follow f1 INNER JOIN Follow f2 ON f1.follower = f2.followee:
  • Performs a self-join on the Follow table.
  • f1.follower = f2.followee ensures that we're looking at users (f1.follower) who are followed by others (f2.follower).
• SELECT f1.follower AS follower:
  • Selects the user who is both a follower and a followee.
• COUNT(DISTINCT f2.follower) AS num:
  • Counts the number of unique followers each second-degree follower has.
• GROUP BY f1.follower:
  • Groups the results by the second-degree follower to aggregate their follower counts.
• ORDER BY f1.follower ASC:
  • Orders the final results alphabetically by the follower's name.

Output After Step 3:

+----------+-----+
| follower | num |
+----------+-----+
| Bob      | 2   |
| Donald   | 1   |
+----------+-----+

Pattern: intersection-of-roles then count (second-degree follower)

Name: users who play both roles in a directed edge table — someone who appears as a followee (is followed) and also as a follower (follows someone) — then count how many followers each such person has.

Set-intersection first (primary teaching form):

-- People who are followees (have followers) ∩ people who are followers (follow others)
WITH both_roles AS (
  SELECT DISTINCT followee AS person FROM Follow
  INTERSECT
  SELECT DISTINCT follower FROM Follow
)
SELECT f.followee AS follower, COUNT(*) AS num
FROM Follow f
JOIN both_roles b ON b.person = f.followee
GROUP BY f.followee
ORDER BY f.followee;

Mental model: (1) build the set of second-degree candidates by set-intersecting the two role projections; (2) count inbound edges only for that set. This avoids an unconstrained M×N self-join of the whole edge table.

Self-join form (common solution): join Follow to itself so a person's name appears once as followee and once as follower, then aggregate — correct but can explode intermediate cardinality; filter early with the CTE/intersection when the graph is dense.

When-NOT: if the problem only asked "who follows someone who follows X," that is a path-2 pattern (different join: a.followee = b.follower), not role intersection.

Drill: Using only INTERSECT (no join), list person names who are both follower and followee on the sample. Then attach counts with a second query.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Second Degree Follower

Why this exists / the decision it encodes

Second-degree here means role intersection: users who appear as both followee (are followed) and follower (follow someone). Then count inbound followers for that set. It is not path-length-2 ("friends of friends") — that is a different join.

Worked example with numbers or traced SQL/FD

Roles: followee set ∩ follower set = second-degree candidates
Self-join form:
  FROM Follow f1 JOIN Follow f2 ON f1.follower = f2.followee
  → f1.follower is someone who follows (role follower) and is a followee in f2
  COUNT(DISTINCT f2.follower) = how many follow them
CTE-first (safer at scale):
  both_roles = DISTINCT followee INTERSECT DISTINCT follower
  then count Follow rows for those persons only
Cartesian risk: celebrity with M followings × N followers intermediate rows before GROUP BY

When NOT / named alternative

When NOT self-join-first: dense graphs — prefilter both_roles with INTERSECT/CTE. When NOT this pattern: "who follows a follower of X" is path-2 (a.followee=b.follower). Materialized follower counts for hot celebrities in production social graphs.

Failure mode / ops fingerprint / interview trap

Trap: counting follow edges without DISTINCT when duplicate edge rows allowed. Ops: self-join spill on social graph ETL. Interview: confuse second-degree role with distance-2 path.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: graph edge tables need early filters; self-join cardinality is the production judgment. Name the pattern before writing SQL.

Hostile-panel drills (with model answers)

Q1. Define second-degree follower for this problem.
Model answer: A user who follows at least one user AND is followed by at least one user — intersection of roles on a directed edge table.

Q2. Why prefilter with INTERSECT?
Model answer: It shrinks candidates before joining/counting, avoiding M×N intermediate blowups of a full self-join on large Follow tables.

Q3. How does path-2 SQL differ?
Model answer: Join Follow a to Follow b ON a.follower = b.followee (or a.followee = b.follower depending on direction) to walk two edges; that answers "follows someone who follows…", not role ∩.