with
    'create_role' as start_event,
    ('create_role', 'pvp_end') as evnet_all,
    '2021-06-30 00:00:00' as start_data,
    '2021-07-06 23:59:59' as end_data
select event_chain,
       count()                  as values
from (with
          toUInt32(minIf(`#event_time`, `#event_name` = start_event)) AS start_event_ts,
          arraySort(
                  x ->
                      x.1,
                  arrayFilter(
                          x -> x.1 >= start_event_ts,
                          groupArray((toUInt32(`#event_time`), `#event_name`))
                      )
              ) AS sorted_events,
          arrayEnumerate(sorted_events) AS event_idxs,
          arrayFilter(
                  (x, y, z) -> z.1 >= start_event_ts and (z.2 = start_event OR y > 1800),
                  event_idxs,
                  arrayDifference(sorted_events.1),
                  sorted_events
              ) AS gap_idxs,
          arrayMap(x -> x, gap_idxs) AS gap_idxs_,
          arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,
          arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_events
      select `#account_id`,
             arrayJoin(split_events)         AS event_chain_,
             arrayMap(x ->
                          x.2, event_chain_) AS event_chain,
             has(event_chain, start_event)   AS has_midway_hit
      from (select `#event_time`, `#event_name`, `#account_id`
            from shjy.event
            where addHours(`#event_time`, 8) >= start_data
              and addHours(`#event_time`, 8) <= end_data
              and `#event_name` in evnet_all)
      group by `#account_id`
      HAVING has_midway_hit = 1
         )
where arrayElement(event_chain, 1) = start_event
GROUP BY event_chain
ORDER BY values desc