lag arrays / WF

gistfile1.txt

create table sess (user_id Int64, session_created_time DateTime) Engine=Memory;

insert into sess select 1, toDateTime(now())+number*2 from numbers(10);
insert into sess select 2, toDateTime(now())+number*5 from numbers(2);
insert into sess select 3, toDateTime(now())+number*6 from numbers(1);


select user_id, arrayReduce('median', arraySlice(arrayDifference(arrayMap(i->toUnixTimestamp(i), (arraySort(groupArray(session_created_time))))),2)) median
from sess
group by user_id

┌─user_id─┬─median─┐
│       3 │    nan │
│       2 │      5 │
│       1 │      2 │
└─────────┴────────┘

SELECT
    user_id,
    median(diff)
FROM
(
    SELECT
        user_id,
        session_created_time - prev_session_created_time AS diff
    FROM
    (
        SELECT
            user_id,
            session_created_time,
            lagInFrame(session_created_time) OVER (PARTITION BY user_id ORDER BY session_created_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_session_created_time
        FROM sess
    )
    WHERE prev_session_created_time != '1970-01-01 00:00:00'
)
GROUP BY user_id

┌─user_id─┬─median(diff)─┐
│       2 │            5 │
│       1 │            2 │
└─────────┴──────────────┘