Explain the following SQL statement. How does it work and what does it do?

Question

Select name, count(secondary) from

(select f1.followed_id as profile,

f2.follower_id as secondary

from following as f1

join following as f2

on f1.follower_id=f2.followed_id

group by profile, secondary) as t

join profiles

on t.profiles=profiles.id

group by profile;

Explain the following SQL statement. How does it work and what does it do?

Summary

To better illustrate the query, the tables that follow are used to generate profiles. Follower id and followed id are two properties in the following table, while id is the only property in the profiles table. After that, certain records are added to both tables. The subquery is then executed, and records are retrieved. The primary query is run from them, and the query is clearly stated from the records fetched SQL.

Explanation

To create the following table:

create table following(

followed_id int,

follower_id int);

To insert records into the following table:

insert into following(followed_id, follower_id)

values(1, 2),

(2, 1),

(2, 3),

(3, 1);

Following table:

To create profiles table

create table profiles(id int);

profiles table:

Subquery of the given query:

In this query self-join takes place. The first followed id of table’s 1dt instance is 1, then the follower id of the 1^st instance of table 1 is checked with the followed id of the 2^nd instance of the table. The follower id of 1^st instance is 2, and it is present as followed id in the second table in rows 2^nd and 3^rd, from where the follower id is taken which are 1 and 3. Hence the first two rows of output are (1, 1) and (1, 3).

select f1.followed_id as profile,f2.follower_id as secondary

from following as f1

join following as f2 on f1. follower_id=f2.followed_id

group by profile, secondary.

 

The actual query asked in the question:

From the subquery, as shown above, five records are fetched. From the subquery table, records are grouped based on profile. For profile 1,  2 secondaries are present and for profile 2 and 3, 2 and 1 secondaries are present respectively.

select profile, count( secondary) from

(select f1.followed_id as profile,f2.follower_id as secondary

from following as f1

join following as f2 on f1. follower_id=f2.followed_id

group by profile, secondary )as t join profiles

on t. profile=profiles.id group by profile

Also, read the Given below-defined UML class diagram.