Second Degree Follower

medium database sql

Problem

Follow(followee, follower). Return followers who are themselves followed by someone, with their own follower count.

Inputf=[('Alice','Bob'),('Bob','Cena'),('Bob','Donald'),('Donald','Edward')]

Output[('Bob',2),('Donald',1)]

Bob is followed by Cena/Donald (count 2) and follows Alice. Donald is followed by Edward (count 1) and follows Bob.

rows (compact)

SELECT followee AS follower, COUNT(*) AS num
FROM Follow
WHERE followee IN (SELECT follower FROM Follow)
GROUP BY followee
ORDER BY followee;

Explanation

A second-degree follower is someone who is followed by others and also follows someone themselves. We need their name plus how many followers they have.

In the Follow(followee, follower) table, the followee column is the person being followed. Grouping by followee and counting rows gives each person's follower count.

To make sure that person is also a follower, we add WHERE followee IN (SELECT follower FROM Follow). This keeps only those who appear in the follower column somewhere, i.e. they follow at least one person.

Example: Bob is followed by Cena and Donald (count 2) and Bob also follows Alice, so Bob qualifies as (Bob, 2). Alice is followed by Bob but Alice never follows anyone, so she is filtered out.

Time: O(n) Space: O(n)