Most Connections on Linkedin

Write an SQL query to fetch the people who has the most connections and the number of connections on Linkedin from the table.

Input :-

Connections table :-
ID is the Primary Key column for the Connections table.

Sent_ID	Accepted_ID	Accept_Date
5	2	2015/05/03
7	4	2017/02/01
2	7	2019/06/07
5	7	2020/05/04

Output :-

ID	Num
7	3

Query :-

with table1 as
(
    select Accepted_ID as ID, Sent_ID as connections
    from Connections
    UNION ALL
    select Sent_ID as ID, Accepted_ID as connections
    from Connections
)
select ID, count(distinct connections) as Num
from table1
group by ID
order by count(distinct connections) desc
limit 1;

Explanation : - 

This query is retrieving the ID with the highest number of distinct connections from a table called "Connections". Here's an explanation of how the query works :-

1. The query begins by defining a temporary table called "table1" using a common table expression(CTE). The CTE consists of two Select statements combined using the ‘Union All’ operator.

• The first Select statement retrieves the Accepted_ID column as ID and the Sent_ID column as connections from the Connections table.
• The second Select statement retrieves the Sent_ID column as ID and the Accepted_ID column as connections from the Connections table.
• The ‘Union All’ operator combines the results of the two SELECT statements into a single result set.
  
  

2. The main part of the query uses the temporary table "table1" to perform the aggregation and sorting operations:

• The Select statement selects the ‘ID’ column and counts the number of distinct connections for each ID using the Count(Distinct connections) function.
• The result is grouped by the 'ID' column using the 'Group By' clause.
• The results are then ordered in descending order based on the count of distinct connections using the 'Order By' clause.
• The 'Limit 1' clause is used to restrict the result set to only the first row.
• The final result of the query will be a single row that contains the ID with the highest number of distinct connections and the corresponding count of those connections.
  
  

In summary, this query finds the ID with the most distinct connections in the Connections table and returns the ID along with the count of those connections.