Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH NEXT AS (
- SELECT
- user_id,
- START,
- END,
- COALESCE(DATETIME(LAG(END) OVER (partition BY user_id ORDER BY START), '15 days') < START, 0) AS step
- FROM
- t
- ),
- num AS (
- SELECT
- user_id,
- START,
- END,
- SUM(step) OVER (partition BY user_id ORDER BY START) + 1 AS sub_sequence_num
- FROM
- NEXT
- )
- SELECT
- user_id,
- START,
- END,
- sub_sequence_num,
- dense_rank() OVER (partition BY user_id, sub_sequence_num ORDER BY START) AS sub_num_in_seq
- FROM
- num
- ORDER BY
- user_id,
- START
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement