30 Days Of LeetCode Database Final Day - 3673. Find Zombie Sessions

1Table: app_events
2 
3+------------------+----------+
4| Column Name      | Type     | 
5+------------------+----------+
6| event_id         | int      |
7| user_id          | int      |
8| event_timestamp  | datetime |
9| event_type       | varchar  |
10| session_id       | varchar  |
11| event_value      | int      |
12+------------------+----------+
13event_id is the unique identifier for this table.
14event_type can be app_open, click, scroll, purchase, or app_close.
15session_id groups events within the same user session.
16event_value represents: for purchase - amount in dollars, for scroll - pixels scrolled, for others - NULL.
17Write a solution to identify zombie sessions, sessions where users appear active but show abnormal behavior patterns. A session is considered a zombie session if it meets ALL the following criteria:
18 
19The session duration is more than 30 minutes.
20Has at least 5 scroll events.
21The click-to-scroll ratio is less than 0.20 .
22No purchases were made during the session.
23Return the result table ordered by scroll_count in descending order, then by session_id in ascending order.
24 
25****

11. Session ကြာချိန်က မိနစ်သုံးဆယ်ကျော်ရမယ်။
22. Scroll event က ငါးခု အနိမ့်ဆုံးဖြစ်ရမယ်။
33. Click to scroll ratio က 0.20 (click count / scroll count )ထက်ငယ်ရမယ်။
44. Session အတွင်း purchase မလုပ်ထားရဘူး။

1Example:
2 
3Input:
4 
5app_events table:
6 
7+----------+---------+---------------------+------------+------------+-------------+
8| event_id | user_id | event_timestamp     | event_type | session_id | event_value |
9+----------+---------+---------------------+------------+------------+-------------+
10| 1        | 201     | 2024-03-01 10:00:00 | app_open   | S001       | NULL        |
11| 2        | 201     | 2024-03-01 10:05:00 | scroll     | S001       | 500         |
12| 3        | 201     | 2024-03-01 10:10:00 | scroll     | S001       | 750         |
13| 4        | 201     | 2024-03-01 10:15:00 | scroll     | S001       | 600         |
14| 5        | 201     | 2024-03-01 10:20:00 | scroll     | S001       | 800         |
15| 6        | 201     | 2024-03-01 10:25:00 | scroll     | S001       | 550         |
16| 7        | 201     | 2024-03-01 10:30:00 | scroll     | S001       | 900         |
17| 8        | 201     | 2024-03-01 10:35:00 | app_close  | S001       | NULL        |
18| 9        | 202     | 2024-03-01 11:00:00 | app_open   | S002       | NULL        |
19| 10       | 202     | 2024-03-01 11:02:00 | click      | S002       | NULL        |
20| 11       | 202     | 2024-03-01 11:05:00 | scroll     | S002       | 400         |
21| 12       | 202     | 2024-03-01 11:08:00 | click      | S002       | NULL        |
22| 13       | 202     | 2024-03-01 11:10:00 | scroll     | S002       | 350         |
23| 14       | 202     | 2024-03-01 11:15:00 | purchase   | S002       | 50          |
24| 15       | 202     | 2024-03-01 11:20:00 | app_close  | S002       | NULL        |
25| 16       | 203     | 2024-03-01 12:00:00 | app_open   | S003       | NULL        |
26| 17       | 203     | 2024-03-01 12:10:00 | scroll     | S003       | 1000        |
27| 18       | 203     | 2024-03-01 12:20:00 | scroll     | S003       | 1200        |
28| 19       | 203     | 2024-03-01 12:25:00 | click      | S003       | NULL        |
29| 20       | 203     | 2024-03-01 12:30:00 | scroll     | S003       | 800         |
30| 21       | 203     | 2024-03-01 12:40:00 | scroll     | S003       | 900         |
31| 22       | 203     | 2024-03-01 12:50:00 | scroll     | S003       | 1100        |
32| 23       | 203     | 2024-03-01 13:00:00 | app_close  | S003       | NULL        |
33| 24       | 204     | 2024-03-01 14:00:00 | app_open   | S004       | NULL        |
34| 25       | 204     | 2024-03-01 14:05:00 | scroll     | S004       | 600         |
35| 26       | 204     | 2024-03-01 14:08:00 | scroll     | S004       | 700         |
36| 27       | 204     | 2024-03-01 14:10:00 | click      | S004       | NULL        |
37| 28       | 204     | 2024-03-01 14:12:00 | app_close  | S004       | NULL        |
38+----------+---------+---------------------+------------+------------+-------------+
39Output:
40 
41+------------+---------+--------------------------+--------------+
42| session_id | user_id | session_duration_minutes | scroll_count |
43+------------+---------+--------------------------+--------------+
44| S001       | 201     | 35                       | 6            |
45+------------+---------+--------------------------+--------------+
46Explanation:
47 
48Session S001 (User 201):
49Duration: 10:00:00 to 10:35:00 = 35 minutes (more than 30) 
50Scroll events: 6 (at least 5) 
51Click events: 0
52Click-to-scroll ratio: 0/6 = 0.00 (less than 0.20) 
53Purchases: 0 (no purchases) 
54S001 is a zombie session (meets all criteria)
55Session S002 (User 202):
56Duration: 11:00:00 to 11:20:00 = 20 minutes (less than 30) 
57Has a purchase event 
58S002 is not a zombie session 
59Session S003 (User 203):
60Duration: 12:00:00 to 13:00:00 = 60 minutes (more than 30) 
61Scroll events: 5 (at least 5) 
62Click events: 1
63Click-to-scroll ratio: 1/5 = 0.20 (not less than 0.20) 
64Purchases: 0 (no purchases) 
65S003 is not a zombie session (click-to-scroll ratio equals 0.20, needs to be less)
66Session S004 (User 204):
67Duration: 14:00:00 to 14:12:00 = 12 minutes (less than 30) 
68Scroll events: 2 (less than 5) 
69S004  is not a zombie session 
70The result table is ordered by scroll_count in descending order, then by session_id in ascending order.
71 
72 

1**SQL**
2 
3 
4WITH session_state AS (
5    SELECT 
6        session_id,
7        user_id,
8        TIMESTAMPDIFF(
9            MINUTE, 
10            MIN(event_timestamp),
11            MAX(event_timestamp)) 
12            AS session_duration_minutes,
13        SUM(
14            CASE 
15            WHEN event_type = 'scroll' THEN 1 ELSE 0
16            END
17        ) AS scroll_count,
18        SUM(
19            CASE 
20            WHEN event_type = 'click' THEN 1 ELSE 0
21            END
22        ) AS click_count,
23        SUM(
24            CASE 
25            WHEN event_type = 'purchase' THEN 1 ELSE 0
26            END
27        ) AS purchase_count
28    FROM app_events GROUP BY session_id, user_id
29)
30SELECT 
31    session_id,
32    user_id,
33    session_duration_minutes,
34    scroll_count
35FROM session_state 
36WHERE 
37    session_duration_minutes > 30 AND
38    scroll_count >= 5 AND
39    click_count / NULLIF(scroll_count, 0) < 0.20 AND
40    purchase_count = 0
41ORDER BY scroll_count DESC, session_id ASC;

11. Session ကြာချိန်က မိနစ်သုံးဆယ်ကျော်ရမယ်။
22. Scroll event က ငါးခု အနိမ့်ဆုံးဖြစ်ရမယ်။
33. Click to scroll ratio က 0.20 (click count / scroll count )ထက်ငယ်ရမယ်။
44. Session အတွင်း purchase မလုပ်ထားရဘူး။