Dmitry Medvedev
82 lines
2.3 KiB
SQL
82 lines
2.3 KiB
SQL
--Активность пользователей
|
|
|
|
--CREATE MATERIALIZED VIEW user_activity_summary AS
|
|
|
|
--CTE кол-во курсов на которые записан пользователь
|
|
WITH enrollment_count AS (
|
|
SELECT
|
|
pe.user_id,
|
|
COUNT(*) AS enrollment_count
|
|
FROM pet_enrollments pe
|
|
GROUP BY pe.user_id
|
|
),
|
|
--CTE просмотренные уникальные уроки пользователем (кол-во)
|
|
viewed_lessons_count AS (
|
|
SELECT
|
|
plv.user_id,
|
|
DENSE_RANK() OVER(
|
|
PARTITION BY plv.user_id
|
|
ORDER BY plv.lesson_id
|
|
) AS viewed_lessons_count
|
|
FROM pet_lesson_views plv
|
|
),
|
|
|
|
--CTE дата и время последнего просмотра урока
|
|
last_view AS (
|
|
SELECT
|
|
plv.user_id,
|
|
LAST_VALUE(plv.viewed_at) OVER (
|
|
PARTITION BY plv.user_id
|
|
ORDER BY plv.viewed_at
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
|
|
) AS last_view
|
|
FROM pet_lesson_views plv
|
|
),
|
|
|
|
--CTE дата и время первого просмотра урока
|
|
first_view AS (
|
|
SELECT
|
|
plv.user_id,
|
|
FIRST_VALUE(plv.viewed_at) OVER (
|
|
PARTITION BY plv.user_id
|
|
ORDER BY plv.viewed_at
|
|
) AS first_view
|
|
FROM pet_lesson_views plv
|
|
),
|
|
|
|
--CTE кол-во курсов доступных пользователю
|
|
lesson_user_all_open AS (
|
|
SELECT
|
|
pe.user_id,
|
|
pe.course_id,
|
|
COUNT(*) AS lesson_user_all_open
|
|
FROM pet_enrollments pe
|
|
LEFT JOIN pet_lessons pl ON pl.course_id = pe.course_id
|
|
GROUP BY pe.user_id, pe.course_id
|
|
ORDER BY user_id, course_id
|
|
)
|
|
|
|
SELECT
|
|
pu.id AS user_id,
|
|
pu.name AS name,
|
|
ec.enrollment_count AS enrollment_count,
|
|
MAX(vlc.viewed_lessons_count) AS viewed_lessons_count,
|
|
last_view,
|
|
first_view,
|
|
ROUND(MAX(vlc.viewed_lessons_count) * 100.0 / lesson_user_all.lesson_user_all_open2, 2) || '%' AS engagement_rate
|
|
|
|
FROM pet_users pu
|
|
LEFT JOIN enrollment_count ec ON pu.id = ec.user_id
|
|
LEFT JOIN viewed_lessons_count vlc ON pu.id = vlc.user_id
|
|
LEFT JOIN last_view lv ON pu.id = lv.user_id
|
|
LEFT JOIN first_view fv ON pu.id = fv.user_id
|
|
LEFT JOIN (
|
|
SELECT
|
|
user_id,
|
|
SUM(luao.lesson_user_all_open) AS lesson_user_all_open2
|
|
FROM lesson_user_all_open luao
|
|
GROUP BY user_id
|
|
) AS lesson_user_all ON lesson_user_all.user_id = pu.id
|
|
|
|
GROUP BY pu.id, ec.enrollment_count, last_view, first_view, lesson_user_all.lesson_user_all_open2
|
|
ORDER BY pu.id; |