Time difference between start/stop interactions-CodePudding

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

CodePudding user response：

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

CodePudding user response：

select   learnerid
        ,max(createdAt)-min(createdAt) as Length_of_interaction
        ,min(createdAt)                as start_timestamp
from
(
select   *
        ,count(case when interaction in('Pause', 'Stop') then 1 end) over(partition by learnerid order by createdAt desc) as grp
from     learner_lessons l1 join learner_lesson_logs l2 using(learnerlessonid)
) l3
group by learnerid, grp
order by start_timestamp

learnerid	Length_of_interaction	start_timestamp
24	4	2022-11-02 07:51:30
24	11	2022-11-02 07:52:20

Fiddle

CodePudding user response：

Here's a solution for MySQL 5.7. We just extract the start and end time for each section. Then, without using a window function, we generate row_number and use integral math to pair up each start and and end time in a group.

select   learnerid
        ,max(flg)-min(flg) as Length_of_interaction
        ,min(flg)          as start_timestamp    
from
        (
         select    learnerlessonid
                  ,case when interaction not in('Run') then createdAt end as flg
         from      learner_lesson_logs l2 
         where     case when interaction not in('Run') then createdAt end is not null
         order by  createdAt
         ) t       
         join learner_lessons using(learnerlessonid) join (select @rn := 0) i
group by learnerid, ceiling((@rn := @rn   1)/2) 
order by start_timestamp

learnerid	Length_of_interaction	start_timestamp
24	4	2022-11-02 07:51:30
24	11	2022-11-02 07:52:20

Fiddle