Calculate Second-Degree Followers for Each YouTuber

following

+----------+----------+
| YouTuber | follower |
+----------+----------+
| A        | B        |
| A        | C        |
| B        | D        |
| C        | E        |
| D        | F        |
+----------+----------+

Scenario

Analyzing a social-network-style following table to compute second-degree followers for each YouTuber.

Question

Write an SQL query that returns, for every YouTuber, the count of distinct second-degree followers (followers of followers) excluding direct followers and the YouTuber themselves.

Hints

Self-join the table: T1.YouTuber→T1.follower→T2.follower; exclude duplicates and direct connections, then group count distinct second-degree followers.

Count Distinct Second-Degree Followers

You are given a social-network-style table following that records which YouTuber follows which other YouTuber. Each row represents a directed follow relationship (YouTuber → follower). Write an SQL query that returns, for every distinct YouTuber in the YouTuber column, the count of distinct second-degree followers (followers of their followers). Exclude any second-degree follower that is also a direct follower of the YouTuber, and also exclude the YouTuber themself. YouTubers with no second-degree followers should still appear with a count of 0.

Table following

column	type	constraints
YouTuber	VARCHAR(50)	NOT NULL
follower	VARCHAR(50)	NOT NULL

Sample data:

YouTuber	follower
A	B
A	C
B	D
C	E
D	F

Expected output

One row per distinct YouTuber from the YouTuber column, with the number of distinct second-degree followers. Second-degree followers are followers of followers, excluding direct followers and the YouTuber themself. YouTubers with no second-degree followers appear with a count of 0.

YouTuber	second_degree_count
A	2
B	1
C	0
D	0

Hints

1. Self-join the table to traverse YouTuber -> follower -> follower-of-follower.
2. Use a LEFT JOIN chain and COUNT(DISTINCT ...) to avoid double-counting second-degree followers.
3. Exclude second-degree candidates that are direct followers or equal to the original YouTuber.