Given a clickstream table events(event_id BIGINT, user_id INT, event_ts TIMESTAMP, url VARCHAR(200), server_log_ts TIMESTAMP) where event_ts may be missing, group each user's rows into inactivity sessions based on a 30-minute threshold.
For every row, first compute its effective timestamp as event_ts when present, otherwise server_log_ts. Then, for each user, order their rows by this effective timestamp in ascending order. If two rows for the same user share the same effective timestamp, use event_id ascending as the tie-breaker.
A row starts a new session when no earlier row exists for that user, or when the difference between its effective timestamp and the previous row's effective timestamp is greater than 30 minutes. A gap of exactly 30 minutes or less continues the current session.
A session's page count is the number of rows in that session. Its duration in seconds is the difference between the latest and earliest effective timestamps within the session; a session with a single row has a duration of 0.
Return one output row per user containing:
session_countmedian_session_duration_secondsp95_pages_per_sessionUse the average of the two middle values when a user has an even number of sessions for the median. Use the nearest-rank method for the 95th percentile of page counts.
Example 1:
Input:
event_id,user_id,event_ts,url,server_log_ts
10,101,2024-05-01 11:41:00,/home,2024-05-01 11:41:05
20,101,NULL,/cart,2024-05-01 10:40:00
30,101,2024-05-01 11:10:00,/checkout,2024-05-01 11:10:02
40,101,2024-05-01 10:00:00,/home,2024-05-01 10:00:05
50,101,2024-05-01 10:25:00,/product,2024-05-01 10:25:03
Output:
user_id,session_count,median_session_duration_seconds,p95_pages_per_session
101,2,2100,4
Explanation: The sorted effective timestamps produce gaps of 25 and 15 minutes, then exactly 30 minutes, then 31 minutes; only the 31-minute gap starts a new session, so the two sessions contain 4 and 1 rows.
rows = [["10",101,2, 2100, 4
Five events for user 101. Event 20 has no event_ts, so its server_log_ts is used.
Example 2:
Input:
event_id,user_id,event_ts,url,server_log_ts
101,202,2024-06-02 08:00:00,/a,2024-06-02 08:00:01
102,202,2024-06-02 08:00:00,/b,2024-06-02 08:00:02
103,202,NULL,/c,2024-06-02 08:30:00
104,202,2024-06-02 09:00:01,/d,2024-06-02 09:00:03
105,202,2024-06-02 09:00:01,/e,2024-06-02 09:00:04
Output:
user_id,session_count,median_session_duration_seconds,p95_pages_per_session
202,2,900,3
Explanation: The first three rows belong to one session because both gaps are at most 30 minutes; the next two rows start a new session after a gap exceeding 30 minutes.
Example 3:
Input:
event_id,user_id,event_ts,url,server_log_ts
900,303,NULL,/start,2024-07-04 15:12:30
Output:
user_id,session_count,median_session_duration_seconds,p95_pages_per_session
303,1,0,1
Constraints:
event_id values are unique.event_ts may be NULL; server_log_ts is never NULL.rows = [["10",101,2, 2100, 4
Five events for user 101. Event 20 has no event_ts, so its server_log_ts is used.