Time difference between start/stop interactions

I have two tables in my database that looks like this

learner_lessons

learnerlessonid    learnerid    lessonid 
      1                24           42

learner_lesson_logs

lessonlogid     learnerlessonid   progress   maxprogress    interaction   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

Basically, when a user clicks on a video it starts playing and the interaction is recorded as ‘Start’ and a timestamp is created accordingly. Now when a user pauses the video another interaction ‘Pause’ is created and the timestamp is recorded. The user might come back later and resume the video thus creating a ‘Play’ interaction. After every 10 seconds of the video if it isn’t paused another interaction ‘Run’ is logged in the database. Finally when the video ends ‘Stop’ interaction is created.

What I am aiming to achieve is the difference in timestamps when a video is ‘Started’ or ‘Played’ till the video is ‘Paused’ or ‘Stopped’. The interactions could be ‘Start’, and ‘Stop’ as well.

This is the query I am working on now

select ll.learnerId ,lll.createdAt,
       (case when interactionType = 'Start' or interactionType  = 'Play'
             then DATEDIFF(SECOND,
                           lll.createdAt,
                           (case when interactionType = 'Stop' or interactionType = 'Pause' then lll.createdAt end) over (order by lll.createdAt desc)
                          )
        end) as diff_minutes
from learner_lesson_log lll join learner_lessons ll on ll.learnerLessonId = lll.learnerLessonId 
order by lll.createdAt

But is throwing me the error

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(order by createdAt desc) ) end) as diff_minut’ at line 5

Error position: line: 4

I want the end result to look like this for each learner

learnerid      Length of interaction      start_timestamp
24                  4                     2022-11-02 07:51:30
24                  11                    2022-11-02 07:52:20

Answer

If you’re using MySQL version >= 8.0.14, you can use a LATERAL DERIVED TABLE to get the next createdAt date that you need to make your comparison in SECONDS between your interactionType's.

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;

Fiddle here.

For your version, MySQL version 5.7, use a subquery in the TIMESTAMPDIFF calculation:

SELECT 
  c.learnerid AS "Learner ID",
  TIMESTAMPDIFF(SECOND, a.createdAt, 
  (SELECT b.createdAt
                  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)) AS "Length of Interaction",
  a.createdAt AS "Start Timestamp"
FROM learner_lesson_log a 
INNER JOIN learner_lessons c 
  ON c.learnerLessonId = a.learnerLessonId 
WHERE a.interactionType IN ('Start', 'Play') 
ORDER BY a.createdAt ASC;

Fiddle here.

Result:

Learner ID	Length of Interaction	Start Timestamp
24	4	2022-11-02 07:51:30
24	11	2022-11-02 07:52:20