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

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

Answer

Using QUALIFY:

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

Advertisement

Answer