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