Calculate Second-Degree Followers for Each YouTuber
Company: Databricks
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Take-home Project
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.
Quick Answer: This question evaluates competency in relational data manipulation and graph-style relationship reasoning, including use of joins, aggregation and distinct counting, and is categorized under Data Manipulation (SQL/Python).