I’m trying to calculate average hours between two adjacent sessions using the data from the following table:
|A||2021-04-16 10:00:00.000 UTC||1|
|A||2021-04-16 11:00:00.000 UTC||2|
|A||2021-04-16 13:00:00.000 UTC||3|
|A||2021-04-16 16:00:00.000 UTC||4|
|B||2021-04-16 12:00:00.000 UTC||1|
|B||2021-04-16 14:00:00.000 UTC||2|
|B||2021-04-16 19:00:00.000 UTC||3|
|C||2021-04-16 10:00:00.000 UTC||1|
|C||2021-04-16 17:00:00.000 UTC||2|
|C||2021-04-16 18:00:00.000 UTC||3|
So, for user A we have
1 hour between session_num = 2 and session_num = 1, 2 hours between session_num = 3 and session_num = 2, 3 hours between session_num = 4 and session_num = 3.
Same for the other users:
2, 5 hours for user B;
7, 1 hours for user C.
The result I expect to get should be the arithmetic average of this date_diff(HOUR).
avg(1,2,3,2,5,7,1) = 3 hours is the average time between two adjacent sessions.
Any one have an idea what query can be used so the date_diff function would be applien only for anjacent sessions?
The average hours between sessions for a given user is most simply calculated as:
select user_id, timestamp_diff(max(event_timestamp), min(event_timestamp), hour) * 1.0 / nullif(count(*) - 1, 0) from t group by user_id;
That is, the average time between sessions for a user is the maximum timestamp minus the minimum timestamp divided by one less than the number of sessions.