SQL solution for Single Identity from Multiple IDs

Viewed 62

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, and ccc all share the same visitor_id
  • ddd and eee share the same email_address as aaa, bbb, and ccc
  • fff shares the same phone_number as ddd (and thus is the same vistior_id as aaa)
  • ggg shares the same email_address as fff (and thus is the same vistior_id as fff which is the same visitor_id as aaa)
  • hhh shares the same phone_number as ggg (and thus is the same vistior_id as ggg which is the same as aaa)

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

0 Answers
Related