clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 921301 fiddles created (11996 in the last week).

CREATE TABLE t (`userID` varchar(6), `tStamp` datetime, `status` varchar(6)) ; INSERT INTO t (`userID`, `tStamp`, `status`) VALUES ('Jason', '2017-10-18 03:20:00', 'idle'), ('Brown', '2017-10-18 03:20:28', 'idle'), ('Brown', '2017-10-18 03:25:28', 'idle'), ('Brown', '2017-10-18 04:00:28', 'active'), ('Brown', '2017-10-18 04:10:28', 'active'), ('Brown', '2017-10-18 04:35:28', 'idle'), ('Brown', '2017-10-18 04:45:28', 'idle') ;
 hidden batch(es)


SELECT userID, tStamp, status, CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status THEN 0 ELSE 1 END AS rst FROM t;
userID tStamp status rst
Brown 2017-10-18 03:20:28 idle 1
Brown 2017-10-18 03:25:28 idle 0
Brown 2017-10-18 04:00:28 active 1
Brown 2017-10-18 04:10:28 active 0
Brown 2017-10-18 04:35:28 idle 1
Brown 2017-10-18 04:45:28 idle 0
Jason 2017-10-18 03:20:00 idle 1
 hidden batch(es)


WITH ct AS ( SELECT userID, tStamp, status, CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status THEN 0 ELSE 1 END AS rst FROM t ) SELECT userID, CONCAT("From ", status, " to ", LEAD(status) OVER (PARTITION BY userID ORDER BY tStamp)) status, timestampdiff(MINUTE, tStamp, LEAD(tStamp) OVER (PARTITION BY userID ORDER BY tStamp)) tDiff FROM ct WHERE rst = 1;
userID status tDiff
Brown From idle to active 40
Brown From active to idle 35
Brown
Jason
 hidden batch(es)