uwegWArz

12018-01-01 00:00:00 <-- valid
22018-01-01 00:15:00 <-- invalid
32018-01-01 00:31:00 <-- valid
42018-01-01 01:14:00 <-- valid
52018-01-01 01:17:00 <-- invalid
62018-01-01 01:25:00 <-- invalid
72018-01-01 01:43:00 <-- invalid
82018-01-01 01:45:00 <-- valid
9	
10DROP TABLE IF EXISTS work.test;
11CREATE TABLE work.test (id INT, event_datetime DATETIME);
12INSERT INTO work.test
13VALUES (123456789, '2017-12-08 15:24:29.297000000'),
14    (123456789, '2017-12-08 15:25:42.510000000'),
15    (123456789, '2017-12-08 15:28:49.023000000'),
16    (123456789, '2017-12-10 07:23:49.693000000'),
17    (123456789, '2017-12-10 07:25:03.487000000'),
18    (123456789, '2017-12-10 07:35:52.613000000'),
19    (123456789, '2017-12-10 07:45:52.613000000'),
20    (123456789, '2017-12-10 07:55:52.613000000'),
21    (123456789, '2017-12-10 08:05:52.613000000'),
22    (123456789, '2017-12-10 15:55:24.070000000'),
23    (123456789, '2017-12-10 15:55:57.063000000'),
24    (123456789, '2017-12-10 15:56:37.633000000'),
25    (123456789, '2017-12-17 09:00:41.543000000'),
26    (123456789, '2017-12-17 09:02:13.187000000'),
27    (123456789, '2017-12-17 09:02:47.370000000'),
28    (123456789, '2017-12-17 09:03:29.843000000'),
29    (123456789, '2017-12-17 09:03:56.667000000'),
30    (123456789, '2017-12-17 09:06:12.493000000'),
31    (123456789, '2017-12-17 09:07:26.113000000');
32
33
34SELECT
35    @last_session_datetime AS last_session_datetime,
36    @diff := timestampdiff(MINUTE, @last_session_datetime, s.event_datetime) AS diff,
37    if(@diff IS NULL OR @diff >= 30, 'valid', 'not valid') AS valid_30_minute_lapse,
38    @last_visit_datetime := if(@curr_customer_id = s.id AND timestampdiff(MINUTE, @last_visit_datetime, s.event_datetime) < 30, @last_visit_datetime, s.event_datetime) AS last_visit_datetime,
39    if(@last_visit_datetime = s.event_datetime, 'valid', 'not valid') AS valid_30_minute_expiration,
40    @curr_customer_id := s.id,
41    id,
42    event_datetime,
43    @last_session_datetime := s.event_datetime
44FROM work.test s
45JOIN (
46         SELECT
47             @curr_customer_id := 0,
48             @last_visit_datetime := '1900-01-01',
49             @last_session_datetime := NULL) a
50ORDER BY s.id, s.event_datetime