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 1st instance of table 1 is checked with the followed id of the 2nd instance of the table. The follower id of 1st instance is 2, and it is present as followed id in the second table in rows 2nd and 3rd, 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.
