Snowflake: SQL Query to identify all the different users with the same ID from the same or multiple tables

user_2392375 0
Viewed 259

There are two tables with both IDs and Usernames. Looking for a snowflake SQL query that results in identifying the IDs shared by different users from the same table or both tables.

Table 1

ID User
1001 A
1002 B
1003 C
1002 D
1005 E

Table 2

ID User
1006 H
1005 E
1003 G
1002 F
1001 A

Expected Results: 1002 - [B,D,F] -> The ID 1002 is used by 3 different users across the two tables 1003 - [C,G] -> The ID 1003 is used by 2 different users across the two tables

Note: 1001 is not included because it's the same user from both tables

snowflake-sql snowflake-cloud-data-platform sql
2 Answers

Using QUALIFY:

SELECT *
FROM (SELECT Id, User
     FROM Table1
     UNION
     SELECT Id, User
     FROM Table2) sub
QUALIFY COUNT(User) OVER(PARTITION BY Id) > 1
user_50708790

query that results in identifying the IDs shared by different users from the same table or both tables.

This sounds like aggregation to me:

select id
from ((select id, user from table1) union all
      (select id, user from table2)
     ) t12
group by id
having min(user) <> max(user);
user_11440350
Related