MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE learner_lessons (
  learnerlessonid INT,
  learnerid INT, 
  lessonid INT
);

CREATE TABLE learner_lesson_log (
  lessonlogid INT,
  learnerlessonid INT, 
  progress INT,
  maxprogress INT,
  interactionType VARCHAR(25),
  createdAt DATETIME
);
INSERT INTO learner_lessons
  VALUES
(1,24,42);

INSERT INTO learner_lesson_log
  VALUES
(1,1,0,15,'Start','2022-11-02 07:51:30'),               
(2,1,4,15,'Pause','2022-11-02 07:51:34'),
(3,1,4,15,'Play','2022-11-02 07:52:20'), 
(4,1,14,15,'Run','2022-11-02 07:52:30'),     
(5,1,15,15,'Stop','2022-11-02 07:52:31');

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM learner_lessons;
SELECT * FROM learner_lesson_log;


learnerlessonid
learnerid
lessonid


1
24
42




lessonlogid
learnerlessonid
progress
maxprogress
interactionType
createdAt


1
1
0
15
Start
2022-11-02 07:51:30

2
1
4
15
Pause
2022-11-02 07:51:34

3
1
4
15
Play
2022-11-02 07:52:20

4
1
14
15
Run
2022-11-02 07:52:30

5
1
15
15
Stop
2022-11-02 07:52:31


SELECT 
  c.learnerid AS "Learner ID",
  TIMESTAMPDIFF(SECOND, a.createdAt, NextDate) AS "Length of Interaction",
  a.createdAt AS "Start Timestamp"
FROM learner_lesson_log a 
  INNER JOIN learner_lessons c 
  ON c.learnerLessonId = a.learnerLessonId, 
LATERAL (
SELECT b.createdAt AS NextDate,
       b.interactionType AS NextType
                  FROM learner_lesson_log b
            INNER JOIN learner_lessons d 
                    ON b.learnerLessonId = d.learnerLessonId
                 WHERE b.learnerLessonId = a.learnerLessonId
                   AND d.learnerId = c.learnerId
                   AND b.createdAt > a.createdAt
                   AND b.interactionType IN ('Stop', 'Pause')
              ORDER BY b.createdAt ASC LIMIT 1
    ) bb
WHERE a.interactionType IN ('Start', 'Play') 
ORDER BY a.createdAt ASC;


Learner ID
Length of Interaction
Start Timestamp


24
4
2022-11-02 07:51:30

24
11
2022-11-02 07:52:20

lessonlogid	learnerlessonid	progress	maxprogress	interactionType	createdAt
1	1	0	15	Start	2022-11-02 07:51:30
2	1	4	15	Pause	2022-11-02 07:51:34
3	1	4	15	Play	2022-11-02 07:52:20
4	1	14	15	Run	2022-11-02 07:52:30
5	1	15	15	Stop	2022-11-02 07:52:31