Trying to record consecutive timestamp streaks within clickhouse

user_14565745 0
Viewed 184

So, I have a table that stores a userid, and session time. I want to get the largest streak a user has had per user. A streaking being having a least 1 session per week for (streak_size) amount of weeks

My table is like so:

create table sessions(userid String, session_ts DateTime) 
Engine = MergeTree Order By(session_ts);

insert into sessions values 
('user1','2020-12-13 00:00:00'),('user1','2020-12-20 00:00:00'),
('user1','2020-12-27 00:00:00'),('user1','2021-01-10 00:00:00'),
('user1','2021-01-17 00:00:00');

and compare a row to the next row to see if the userid is equal and the week are exactly 1 week apart

the issue is that I don't know how to tell it to stop counting, or start a new count when the streak is broken

What I would like my input to look like is:

[userid: user1, streak: 3]
clickhouse sql-like sql
1 Answers

It's a task "gaps-and-islands"

SELECT
    userid,
    arrayMap(i -> length(i), arraySplit((x, y) -> (y > 1), arrayDifference(groupArray(weeknum)) AS x, x)) AS r,
    arrayMax(r) AS r1
FROM
(
    SELECT userid, toRelativeWeekNum(session_ts) weeknum FROM sessions ORDER BY userid, weeknum
)
GROUP BY userid

┌─userid─┬─r─────┬─r1─┐
│ user1  │ [3,2] │  3 │
└────────┴───────┴────┘
user_116443080
Related