Active Profile Follower Connectivity Report

Your analytics team stores social follows in a table named follow.

Columns:

• followee: account being followed
• follower: account that follows followee

Data guarantees:

• (followee, follower) is unique
• no account follows itself

Define a two-sided active account as an account that:

• follows at least one other account (appears in follower), and
• is followed by at least one account (appears in followee).

Task: Return every two-sided active account and how many distinct followers it has.

Output requirements:

• Return exactly two columns: follower, num
• follower: qualifying account id
• num: count of distinct accounts that follow that follower account
• Order by follower in ascending alphabetical order

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)

follow:
| followee | follower |
|----------|----------|
| Alice    | Bob      |
| Bob      | Cena     |
| Bob      | Donald   |
| Donald   | Edward   |

[
  {"follower":"Bob","num":2},
  {"follower":"Donald","num":1}
]

follow:
| followee | follower |
|----------|----------|
| Root     | Leaf1    |
| Root     | Leaf2    |
| Root     | Leaf3    |
| Root     | Leaf4    |

[]

follow:
| followee | follower |
|----------|----------|
| Nina     | Omar     |
| Omar     | Pia      |
| Pia      | Nina     |
| Nina     | Quinn    |
| Ravi     | Nina     |
| Pia      | Ravi     |

[
  {"follower":"Nina","num":2},
  {"follower":"Omar","num":1},
  {"follower":"Pia","num":2},
  {"follower":"Ravi","num":1}
]