SQL query 6 degrees of separation for network analysis

Question

I'm building a network analysis using D3.js to show connected phone numbers within my app down to six degrees of separation. The SQL (postgres) to find initial connections is below and fairly straightforward. However, I am stumped on how to modify this to traverse through six levels of connections then stop.

SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN (SELECT hash FROM game.phone_hashes WHERE player_id = $1);

I have found mentions of Common Table Expressions (CTE) and recursion through research into this problem, but am unsure how to apply them here.

What I'm aiming for is to get all the players connected to the initial player ($1) through a common phone hash, then all the players connected to each of those connections through a common phone hash, and on and on out to 6 degrees of separation.

Please edit your question and add the definition (`create table`) of the tables involved. — , Nov 21 '15 at 13:47
Also, it often helps to understand the question if the question includes an example - few rows of data and what the result of the query should be based on this sample data. — Vladimir Baranov, Nov 23 '15 at 11:51
A **table definition** is what you get with `\d game.phone_hashes` in psql. Or the complete `CREATE TABLE` script. — Erwin Brandstetter, Nov 28 '15 at 18:07

score 8 · Accepted Answer · answered Nov 24 '15 at 10:03

I think this is what you meant:

with recursive tc as(
select $1 as player_id, 1 as level
  union
select ph2.player_id, level+1
  from tc, phone_hashes ph1, phone_hashes ph2
  where tc.player_id=ph1.player_id
  and ph1.hash=ph2.hash
  and tc.level < 6  
)    
select distinct player_id from tc

Steve Chambers · Answer 2 · 2015-11-23T12:21:17.977

Think it would be:

-- 6 degrees of separation
SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN
(SELECT hash FROM game.phone_hashes
 WHERE hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE hash IN 
    (SELECT hash FROM game.phone_hashes
     WHERE hash IN
     (SELECT hash FROM game.phone_hashes
      WHERE player_id = $1))))));

Please see workings below:

-- 1 degree of separation
SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN
(SELECT hash FROM game.phone_hashes WHERE player_id = $1);

-- 2 degrees of separation
SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN
(SELECT hash FROM game.phone_hashes 
 WHERE hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE player_id = $1));

-- 3 degrees of separation
SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN
(SELECT hash FROM game.phone_hashes
 WHERE hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE player_id = $1)));

-- 4 degrees of separation
SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN
(SELECT hash FROM game.phone_hashes
 WHERE hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE player_id = $1))));


-- 5 degrees of separation
SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN
(SELECT hash FROM game.phone_hashes
 WHERE hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE hash IN
    (SELECT hash FROM game.phone_hashes
     WHERE player_id = $1)))));

-- 6 degrees of separation
SELECT player_id, ps.player_state, ps.email, ph.create_date
FROM game.phone_hashes ph
INNER JOIN game.customer_settings cs ON cs.id = ph.player_id
WHERE hash IN
(SELECT hash FROM game.phone_hashes
 WHERE hash IN
 (SELECT hash FROM game.phone_hashes
  WHERE hash IN
  (SELECT hash FROM game.phone_hashes
   WHERE hash IN
   (SELECT hash FROM game.phone_hashes
    WHERE hash IN 
    (SELECT hash FROM game.phone_hashes
     WHERE hash IN
     (SELECT hash FROM game.phone_hashes
      WHERE player_id = $1))))));

SQL query 6 degrees of separation for network analysis

2 Answers2

Linked