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
CodePudding user response：
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
CodePudding user response：
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;