Problem

Table: Follow

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| followee    | varchar |
| follower    | varchar |
+-------------+---------+
(followee, follower) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the user follower follows the user followee on a social network.
There will not be a user following themself.

Problem Definition

A second-degree follower is a user who:

• follows at least one user, and
• is followed by at least one user.

Write a solution to report the second-degree users and the number of their followers.

Return the result table ordered by follower in alphabetical order.

Example

Image scaled to 45%

Output

Image scaled to 50%

Try It Yourself

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Solution

To identify second-degree followers—users who both follow at least one other user and are followed by at least one user—we can leverage SQL’s self-join capabilities. The approach involves isolating users who meet both criteria and then counting their followers.

• Identify Users Who Follow Others: Determine users who are followers in the Follow table.
• Identify Users Who Are Followed: Determine users who are followees in the Follow table.
• Determine Second-Degree Followers: Find the intersection of users who follow others and are followed by others.
• Count Followers for Each Second-Degree Follower: For each second-degree follower, count the number of users who follow them.
• Order the Results: Present the final list ordered alphabetically by the follower’s name.

SQL Query

SELECT f1.follower AS follower,
       COUNT(DISTINCT f2.follower) AS num
FROM   Follow f1
       INNER JOIN Follow f2
               ON f1.follower = f2.followee
GROUP  BY f1.follower
ORDER BY f1.follower ASC;

Step-by-Step Approach

Step 1: Identify Users Who Follow Others

Determine all users who follow at least one other user by selecting distinct followers from the Follow table.

SQL Query:

SELECT DISTINCT follower
FROM Follow;

Explanation:

• SELECT DISTINCT follower:

  • Retrieves unique users who act as followers in the Follow table.

• FROM Follow:

  • Specifies the Follow table as the data source.

Output After Step 1:

+----------+
| follower |
+----------+
| Bob      |
| Cena     |
| Donald   |
| Edward   |
+----------+

Step 2: Identify Users Who Are Followed

Determine all users who are followed by at least one other user by selecting distinct followees from the Follow table.

SQL Query:

SELECT DISTINCT followee
FROM Follow;

Explanation:

• SELECT DISTINCT followee:

  • Retrieves unique users who are followed in the Follow table.

• FROM Follow:

  • Specifies the Follow table as the data source.

Output After Step 2:

+----------+
| followee |
+----------+
| Alice    |
| Bob      |
| Donald   |
+----------+

Step 3: Determine Second-Degree Followers and Count Their Followers

Identify users who both follow others and are followed by others (second-degree followers) and count the number of their followers.

SQL Query:

SELECT f1.follower AS follower,
       COUNT(DISTINCT f2.follower) AS num
FROM   Follow f1
       INNER JOIN Follow f2
               ON f1.follower = f2.followee
GROUP  BY f1.follower
ORDER BY f1.follower ASC;

Explanation:

• FROM Follow f1 INNER JOIN Follow f2 ON f1.follower = f2.followee:

  • Performs a self-join on the Follow table.
  • f1.follower = f2.followee ensures that we’re looking at users (f1.follower) who are followed by others (f2.follower).

• SELECT f1.follower AS follower:

  • Selects the user who is both a follower and a followee.

• COUNT(DISTINCT f2.follower) AS num:

  • Counts the number of unique followers each second-degree follower has.

• GROUP BY f1.follower:

  • Groups the results by the second-degree follower to aggregate their follower counts.

• ORDER BY f1.follower ASC:

  • Orders the final results alphabetically by the follower’s name.

Output After Step 3:

+----------+-----+
| follower | num |
+----------+-----+
| Bob      | 2   |
| Donald   | 1   |
+----------+-----+