Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
2. Second Degree Follower
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

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

Output

Image

Try It Yourself

MYSQL
MYSQL
. . . .

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   |
+----------+-----+

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible