I have a table with data:
+--------+---------+
| userid | item |
+--------+---------+
| user_1 | abc_1 |
| user_2 | abc_1 |
| user_2 | def_1 |
| user_3 | def_1 |
| user_4 | bla_bla |
| user_4 | null_bla|
| user_5 | ghi_2 |
| user_5 | jkl_2 |
| user_6 | ghi_2 |
| user_6 | mno_2 |
+--------+---------+
I would like to determine the network users who have the same items own by them and cluster them into each group. If the user doesn't have any similar item with the other users, exclude them from the output (in this case I would like to exclude user_4). Ideal query output (with distinct user) will be as follow:
+--------+---------+
| userid | network |
+--------+---------+
| user_1 | 1 |
| user_2 | 1 |
| user_3 | 1 |
| user_5 | 2 |
| user_6 | 2 |
+--------+---------+
user_1, user_2, user_3 are group into network 1 because user_1 (abc_1) & user_2 (abc_1) and user_2 (def_1) & user_3 (def_1) have the same items which form the network 1. Same concept applies to network 2 as well.
Also FYI, my table has more than 1,000+ network of people. I'm using AWS Redshift (Postgresql 8.0). Any efficient query would be helpful as well. Thanks.