 |
Extracting all data from study materials usage log
Use this template to play with LessonLog table data select
ll.event_date,
ll.user_id,
ll.site_id,
study.course_id
from
LessonLog ll
join
Users u on ll.user_id = u.user_id
join
(select l1.lesson_id as unit_id, l1.course_id, 2 as event_unit from Lessons l1
union select unit_id as unit_id, l2.course_id, 3 as event_unit from Units u1 join Lessons l2 on u1.lesson_id = l2.lesson_id
union select unitlink_id as unit_id, l3.course_id, 4 as event_unit from Unitlinks ul1 join Units u2 on u2.unit_id = ul1.unit_id join Lessons l3 on l3.lesson_id = u2.lesson_id) study
on study.unit_id = ll.unit_id and study.event_unit = ll.event_unit
|
