I am looking for a way, via SQL, to establish a single visitor_id from rows that share common (but different) keys and then produce an output table assigning the established visitor_id back to all of the rows.
My Database is AWS Athena.
Example:
with things as (
select 'aaa' as thing_id, '111' as visitor_id, 'alpha@gmail.com' as email_address, '(111) 111-1111' as phone_number
union all select 'bbb' as thing_id, '111' as visitor_id, 'beta@gmail.com' as email_address, null as phone_number
union all select 'ccc' as thing_id, '111' as visitor_id, null as email_address, '(222) 222-2222' as phone_number
union all select 'ddd' as thing_id, '222' as visitor_id, 'alpha@gmail.com' as email_address, '(333) 333-3333' as phone_number
union all select 'eee' as thing_id, '333' as visitor_id, 'alpha@gmail.com' as email_address, null as phone_number
union all select 'fff' as thing_id, '444' as visitor_id, 'charlie@gmail.com' as email_address, '(333) 333-3333' as phone_number
union all select 'ggg' as thing_id, '555' as visitor_id, 'charlie@gmail.com' as email_address, '(444) 444-4444' as phone_number
union all select 'hhh' as thing_id, '666' as visitor_id, null as email_address, '(444) 444-4444' as phone_number
)
I know that all of these things belong to the same visitor_id as
aaa,bbb, andcccall share the samevisitor_iddddandeeeshare the sameemail_addressasaaa,bbb, andcccfffshares the samephone_numberasddd(and thus is the samevistior_idasaaa)gggshares the sameemail_addressasfff(and thus is the samevistior_idasfffwhich is the samevisitor_idasaaa)hhhshares the samephone_numberasggg(and thus is the samevistior_idasgggwhich is the same asaaa)
My desired output table would be:
|---------------------|------------------|
| thing_id | vistior_id |
|---------------------|------------------|
| aaa | 111 |
|---------------------|------------------|
| bbb | 111 |
|---------------------|------------------|
| ccc | 111 |
|---------------------|------------------|
| ddd | 111 |
|---------------------|------------------|
| eee | 111 |
|---------------------|------------------|
| fff | 111 |
|---------------------|------------------|
| ggg | 111 |
|---------------------|------------------|
| hhh | 111 |
|---------------------|------------------|
I have tried cross joining and then eliminating duplicates. However, I am struggling with fff, ggg, and hhh, as they are only related to aaa through the other thing_ids