sessions.sql

sessions.sql

SELECT event.USER_ANONYMOUS_ID || '-' || sha1(event._TIMESTAMP) as session_id
     , event.USER_ANONYMOUS_ID
     , event._TIMESTAMP                                                     as session_start_at
     , lead(_TIMESTAMP) over
    (partition by event.USER_ANONYMOUS_ID order by event._TIMESTAMP)        as next_session_start_at
FROM (SELECT e.USER_ANONYMOUS_ID
           , e._TIMESTAMP
           , IFF(DATEDIFF(days
                     , LAG(e._TIMESTAMP) OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                     , e._TIMESTAMP) = 0
        , IFF(DATEDIFF(minutes
                  , LAG(e._TIMESTAMP) OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                  , e._TIMESTAMP) is null
                     , null
                     , IFF(DATEDIFF(minutes
                               , LAG(e._TIMESTAMP)
                                     OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                               , e._TIMESTAMP) IS NULL
                  , NULL
                  , IFF(DATEDIFF(minutes
                            , LAG(e._TIMESTAMP)
                                  OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                            , e._TIMESTAMP) <= 30
                               , IFF(UTM_CAMPAIGN is null
                            , DATEDIFF(minutes
                                         , LAG(
                                               e._TIMESTAMP)
                                               OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                                         , e._TIMESTAMP)
                            , IFF(LAG(UTM_CAMPAIGN)
                                      over (partition by e.USER_ANONYMOUS_ID order by _TIMESTAMP) is null
                                         , DATEDIFF(minutes
                                      , LAG(e._TIMESTAMP)
                                            OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                                      , e._TIMESTAMP)
                                         , IFF(LAG(UTM_CAMPAIGN)
                                                   over (partition by e.USER_ANONYMOUS_ID order by _TIMESTAMP) =
                                               UTM_CAMPAIGN
                                      , DATEDIFF(minutes
                                                   , LAG(
                                                         e._TIMESTAMP)
                                                         OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                                                   , e._TIMESTAMP)
                                      , null)))
                               , DATEDIFF(minutes
                            , LAG(e._TIMESTAMP)
                                  OVER (PARTITION BY e.USER_ANONYMOUS_ID ORDER BY e._TIMESTAMP)
                            , e._TIMESTAMP))))
        , null) inactivity_time
      FROM EVENTS AS e
      where to_date(e._TIMESTAMP) between to_date(?
          , 'YYYY-MM-DD')
                and to_date(?
              , 'YYYY-MM-DD')) as event
WHERE event.inactivity_time > 30
   OR event.inactivity_time is null