Complex count distinct

I have a dataset with session IDs and pages... I want to know how many distinct session IDs include a specific page ('ABC'). The trick is one session ID could have multiple rows with page ABC...

Session ID -- Page

123 -- ABC

123 -- ABC

123 -- DEF

456 -- DEF

456 -- ABC

789 -- GHI

Hypothetically, how do I create a beastmode to count distinct session IDs with ABC as 2?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    Have you tried this:

    COUNT(DISTINCT 
       CASE WHEN `pageid` = 'ABC' then `sessionid`end
       )
    

    This should do it




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.