Need to get common users for every combination of user IDs

Hadoop
Enthusiast

Need to get common users for every combination of user IDs

Hi,

I have a table which has one user ID mapped to multiple other user IDs. I want to get the common user IDs mapped to every two user IDs

My Data looks in the following way

User ID User ID 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 3
2 5
2 7
2 9
2 11
2 13
2 15
2 17
. .
. .
. .
. .
. .
. .
2000 2001

User ID and User ID1 are 2 different columns. For each user ID in column 1 there are multiple user ID mapping  in column 2

I need to get for every User ID combination(Column 1 combination eg:(1 and 2), (1 and 3) and so on) starting from (1,2) till (1999,2000) how many common User ID1's are there

Thanks in advance for any solution

2 REPLIES
Junior Contributor

Re: Need to get common users for every combination of user IDs

SELECT t1.UserId, t2.UserId, COUNT(*)
FROM tab AS t1
JOIN tab AS t2
ON t1.UserId < t2.UserId -- different user id
AND t1.UserId1 = t2.UserId1 -- same user id 1
GROUP BY 1,2
Enthusiast

Re: Need to get common users for every combination of user IDs

Thanks Dieter for the solution