Question:
You are given a network table. Table contains 3 col’s – friend1, friend2, weight.
+———-+——–+——–+—+
|friend1 | friend2 | weight |
+———-+———-+——–+—+
| A | B | 5 |
| B | C | 6 |
| B | D | 2 |
| D | E | 9 |
| A | C | 6 |
+———-+———-+——–+—+
You have to find a weight between 2nd level friends. i.e if A->B and B->C, you have to find sum of weight of A->C that is sum of A->B and B->C weight’s in Descending order of weight. Your query should not return results if pair A->C already in table.
Solution:
SELECT N1.friend1,N2.friend2,(N1.weight+N2.weight) as rank FROM network as N1, network as N2 WHERE N1.friend2=N2.friend1 and N2.friend2 NOT IN (SELECT friend2 FROM network where friend1=N1.friend1) ORDER BY rank DESC ;
Output:
+———-+——–+——–+——-+
|friend1 | friend2 | weight |
+———-+———-+——–+——+
| B | E | 11 |
| A | D | 7 |
+———-+———-+——–+——+