CODEWITHSUNDEEP
mysqlsql
Abs
Abs

Reputation: 1766

SQL - query to find most popular friends

I have a MySQL table of edges in a graph. I need a query to find the most popular friends of a given person. Could someone help me out? Here are some details:

mysql> describe edges;
+--------------+--------------+------+-----+-------------------+----------------+
| Field        | Type         | Null | Key | Default           | Extra          |
+--------------+--------------+------+-----+-------------------+----------------+
| ID           | int(11)      | NO   | PRI | NULL              | auto_increment |
| from_node_id | int(11)      | NO   |     | NULL              |                |
| to_node_id   | int(11)      | NO   |     | NULL              |                |
+--------------+--------------+------+-----+-------------------+----------------+
3 rows in set (0.12 sec)

Basically if A has 3 friends, B, C, and D - I'd like to be able to rank A's friends by how many friends they have. So essentially I can find which of A's friends are the most popular :)

If possible I'd like to do this without using nested queries so the execution is fast. The table is quite large!

There is a table of nodes as well, but I believe you shouldn't need that to make this query run :) Any help would be greatly appreciated!

EDIT: Here is some sample data and a sample result:

Example input table

+----+--------------+------------+
| id | from_node_id | to_node_id |
+----+--------------+------------+
|  1 |            1 |          2 |
|  2 |            1 |          3 |
|  3 |            1 |          4 |
|  4 |            5 |          2 |
|  5 |            6 |          2 |
|  6 |            7 |          3 |
+----+--------------+------------+

Example output table for the node 1. Shows popularity of each friend

+---------+-------------+
| node_id | num_friends |
+---------+-------------+
|       2 |           3 |
|       3 |           2 |
|       4 |           1 |
+---------+-------------+

Upvotes: 0

Views: 195

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Get the counts of each to_node_id in a derived table and join it with the original table to filter it on from_node_id.

select t.to_node_id,x.num_friends
from (select to_node_id,count(*) as num_friends
      from t 
      group by to_node_id) x
join t on t.to_node_id=x.to_node_id
where t.from_node_id=1
order by x.num_friends desc,t.to_node_id

Another way to do it with a self join.

select t1.to_node_id,count(*) as num_friends
from t t1
join t t2 on t1.to_node_id=t2.to_node_id
where t1.from_node_id=1
group by t1.to_node_id
order by num_friends desc,t1.to_node_id

Upvotes: 1

dragmosh
dragmosh

Reputation: 393

Is this what you're looking for?

select e1.to_node_id as node_id, count(*) as num_friends
from edges e1
inner join edges e2 on e2.to_node_id = e1.from_node_id
where e1.from_node_id = 1
group by e1.to_node_id;

Upvotes: 0

Related Questions