WITH ? AS starting_time, ? AS ending_time, ? AS session_timeout, ? AS starting_event, ? AS ending_event, SessionData AS ( WITH date, arraySort( groupArray( ( tracking.event.time, tracking.event.event ) ) ) AS _sorted_events, arrayEnumerate(_sorted_events) AS _event_serial, arrayDifference(_sorted_events.1) AS _event_time_diff, arrayFilter( (x, y, z) -> y > session_timeout OR z.2 = starting_event, _event_serial, _event_time_diff, _sorted_events ) AS _gap_index_1, arrayFilter( (x, y) -> y.2 = ending_event, _event_serial, _sorted_events ) AS _gap_index_2_, arrayMap( x -> x + 1, _gap_index_2_ ) AS _gap_index_2, arrayMap(x -> if (has(_gap_index_1,x) OR has(_gap_index_2,x), 1, 0), _event_serial) AS _session_splitter, arraySplit((x, y) -> y, _sorted_events, _session_splitter) AS _session_chain SELECT date, user_id AS user_id, arrayJoin(_session_chain) AS event_chain, arrayCompact(x -> x.2, event_chain) AS event_chain_dedup FROM tracking.event WHERE project=? AND time>=starting_time AND time<ending_time AND event NOT IN (?, ?, ?, ?) GROUP BY date, user_id ), SessionOverallInfo AS ( SELECT date, COUNT(*) AS number_of_sessions FROM SessionData GROUP BY date ) SELECT SessionOverallInfo.date, SessionOverallInfo.number_of_sessions AS number_of_total_sessions FROM SessionOverallInfo ORDER BY SessionOverallInfo.date