Query for relationships between rows

Question

I need to find a relation between multiple person in single table, for example I have the below table:

Guests Table

so I need by sql script to say Guest 123 and 456 they checked in together to the same hotel in the same time 80% and so on...

Kindly support.

Answer 1

It's a little complicated so I've broken it down into multiple subqueries for you using a CTE with a matched key.

This will produce a series of matched pairs - for the primary guest and secondary guest with ratios of how often they stay together rather than just check in.

Setup:

create table temp(
    hotelID integer,
    checkInDate date,
    guestID integer
)

insert into temp values (101, '2020/06/01', 123)
insert into temp values (101, '2020/06/01', 456)
insert into temp values (102, '2020/06/15', 123)
insert into temp values (102, '2020/06/15', 456)
insert into temp values (103, '2020/06/30', 123)
insert into temp values (103, '2020/06/30', 456)
insert into temp values (104, '2020/07/15', 123)
insert into temp values (104, '2020/07/15', 789)
insert into temp values (105, '2020/07/01', 456)
insert into temp values (105, '2020/07/01', 789)

Query:

    with keyCte as (
        select 
            distinct cast(hotelID as varchar(3)) + cast(checkInDate as varchar(10)) as myKey,
            guestID
        from temp
    ) 

    select 
          guestPrime
        , guestTwo
        , instances as guestPrimeStays
        , matches as guestTwoMatches
        , cast(matches as float) / cast(instances as float) as hitRate

    from (
            select 
                  guestID
                , count(*) as instances 
            from keyCte 
            group by guestID
         ) sq3

    join (
        select
            guestPrime
            , guestTwo
            , count(*) as matches
        from (
            select 
                keyCte.guestID as guestPrime
              , kcte.guestID as guestTwo
            from keyCte
            join keyCte kcte on kcte.myKey = keyCte.myKey and kcte.guestID != keyCte.guestID
        ) sq 
        group by guestPrime, guestTwo

    ) sq2 on sq2.guestPrime = guestID