MySQL web customer activity analysis question

user_12162096 0
Viewed 32

I created a very simple toy table:
enter image description here

Use the following MySQL script to create the same table

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `sessionid` int NOT NULL,
  `timestamp` datetime DEFAULT NULL,
  `url` varchar(45) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;


LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'2020-01-01 05:00:00','../1/..',1),(1,'2020-01-01 05:02:00','../2/..',2),(1,'2020-01-01 05:08:00','../1/..',3),(1,'2020-01-01 05:12:00','../4/..',4),(2,'2020-01-01 05:00:00','../1/..',5),(2,'2020-01-01 06:00:00','../2/..',6),(3,'2020-01-01 05:00:00','../2/..',7),(3,'2020-01-01 05:03:00','../1/..',8),(3,'2020-01-01 05:04:00','../2/..',9);
UNLOCK TABLES;

I'd like to query:

(1) In 10 minutes time span, which sessionid has visited url ../1/.. then visited url ../2/..?
Answer: session 1, 3

(2) In 10 minutes time span, which sessionid has visited url ../1/.. then visited url ../2/.., and finally landed back to url ../1/..?
Answer: session 1

I would appreciate it if you can write in MySQL. Provide code or simply give some strategy thoughts about solving this will be great.

mysql sql database
1 Answers

You can use window functions. For instance, for "2":

select t.*
from (select t.*,
             min(case when url like '%/2/%' then timestamp end) over (partition by id order by timestamp desc) as next_2,
             min(case when url like '%/3/%' then timestamp end) over (partition by id order by timestamp desc) as next_3
      from t
     ) t
where url like '%/1/%' and
      next_2 < timestamp + interval 10 minute;
user_11440350
Related