amazon redshift - SQL rollup on sessions


Keywords:sql 


Question: 

I have an impression event table that has a bunch of timestamps and marked start/end boundaries. I am trying to roll it up to have a metric that says "this session contains at least 1 impression with feature x". I'm not sure how exactly to do this. Any help would be appreciated. Thanks.

enter image description here

I want to roll this up into something that looks like:

account, session_start, session_end, interacted_with_feature
3004514, 2018-02-23 13:43:35.475, 2018-02-23 13:43:47.377, FALSE

where it is simple for me to say if this session had any interactions with the feature or not.


2 Answers: 

Perhaps aggregation does what you want:

select account, min(timestamp), max(timestamp), max(interacted_with_feature)
from t
group by account;
 

I was able to solve this with conditional cumulative sums to generate a session group ID for each row.

with cte as (
    select *
        , sum(case when session_boundary = 'start' then 1 else 0 end) 
              over (partition by account order by timestamp rows unbounded preceding)
          as session_num
    from raw_sessions
)

select account
    , session_num
    , min(timestamp) as session_start
    , max(timestamp) as session_end
    , bool_or(interacted_with_feature) as interacted_with_feature
from cte
group by account, session_num