Question 22086 in Mysql Question posted in
The official MySQL documentation can be found here.

Get the last occurrence in IN clause of Mysql

I have a table for learner as learner_lesson

learnerlessonid learnerid   lessonid
1                 24          42

and another table as learner_lesson_log

lessonlogid learnerlessonid progress    maxprogress.    interactionType       createdAt
1                   1           0           15           Start             2022-11-02 07:50:30
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

What I am trying to get is the number of seconds watched by a learner. But, it could happen that a learner started a lesson and doesn’t complete it or pause it, comes back later and then complete the lesson. For example in the above example, a learner started a lesson and dropped it, came back again and started the lesson again before pausing it after 4 seconds. I want the result to look like

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

But with the query I have

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

I want the query to count the number of seconds only between Start -> Pause, Start->Stop, Play -> Pause, Play -> Stop combination. How can I achieve this result? This is the query that I have

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;

This is the fiddle

Tags:

2

Answers


  1. 0

    but a solution for version 8 would be fine as well

    WITH 
cte1 AS (
  SELECT *,
         (interactionType IN ('Start', 'Play')) * 4 +
         (interactionType IN ('Stop', 'Pause')) * 2 +
         (COALESCE(LAG(interactionType) OVER (PARTITION BY learnerlessonid
                                              ORDER BY createdAt), 
                   'Stop') IN ('Stop', 'Pause')) point_mark
  FROM learner_lesson_log
  ),
cte2 AS (
  SELECT *,
         TIMESTAMPDIFF(SECOND, 
                       createdAt,
                       LEAD(createdAt) OVER (PARTITION BY learnerlessonid
                                             ORDER BY createdAt)) delta
  FROM cte1 
  WHERE point_mark IN (2, 5)
  )
SELECT learner_lessons.learnerid `Learner ID`,   
       cte2.delta `Length of Interaction`,
       cte2.createdAt `Start Timestamp`
FROM learner_lessons
JOIN cte2 USING (learnerlessonid)
WHERE point_mark = 5
ORDER BY 1, 3

    step-by-step fiddle

    Login or Signup to reply.
  2. 0

    I think this works as expected on 5.7 –

    SELECT
  c.learnerid `Learner ID`,
  TIMESTAMPDIFF(SECOND, l1.createdAt, l2.createdAt) `Length of Interaction`,
  l1.createdAt `Start Timestamp`
FROM learner_lesson_log l1
INNER JOIN learner_lessons c 
  ON l1.learnerLessonId = c.learnerLessonId 
INNER JOIN learner_lesson_log l2 
  ON l1.learnerLessonId = l2.learnerLessonId
  AND l2.interactionType IN ('Pause', 'Stop')
  AND l2.createdAt = (
    SELECT MIN(createdAt)
    FROM learner_lesson_log
    WHERE learnerLessonId = l1.learnerLessonId
    AND interactionType IN ('Start', 'Play', 'Pause', 'Stop')
    AND createdAt > l1.createdAt
  )
WHERE l1.interactionType IN ('Start', 'Play') 
ORDER BY c.learnerid ASC, l1.createdAt ASC;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search