Calculate Second-Degree Followers for Each YouTuber
Company: Databricks
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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.
Overview: 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).
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.
Tables
following(YouTuber VARCHAR(50), follower VARCHAR(50))
Hints
- Self-join the table to traverse YouTuber -> follower -> follower-of-follower.
- Use a LEFT JOIN chain and COUNT(DISTINCT ...) to avoid double-counting second-degree followers.