50 lines
2.4 KiB
SQL
50 lines
2.4 KiB
SQL
-- ### ✅ **Задание 2: Подготовь структуру витрины `lesson_popularity_summary`**
|
|
--
|
|
-- **Цель:** анализировать популярность каждого урока.
|
|
--
|
|
-- 📌 **Что нужно сделать:**
|
|
--
|
|
-- - Спроектируй таблицу, где каждая строка — один урок.
|
|
-- - Для каждого урока должны храниться:
|
|
-- - ID и название урока
|
|
-- - ID и название курса, к которому он относится
|
|
-- - Общее число просмотров
|
|
-- - Количество уникальных пользователей, посмотревших урок
|
|
-- - Дата первого и последнего просмотра
|
|
--
|
|
-- **Зачем это нужно:**
|
|
--
|
|
-- Эта витрина поможет выявлять наиболее популярные уроки и курсы, следить за актуальностью контента, понимать вовлечённость студентов по отдельным материалам.
|
|
SELECT l.lesson_id AS lesson_id,
|
|
l.title AS lesson_name,
|
|
c.course_id AS course_id,
|
|
c.title AS course_name,
|
|
countOrNull() AS total_view,
|
|
uniqOrNull(flv.user_id) AS unique_users,
|
|
min(flv.viewed_at) AS first_date,
|
|
max(flv.viewed_at) AS last_date
|
|
FROM pet_project.lessons l
|
|
LEFT JOIN pet_project.courses c ON c.course_id = l.course_id
|
|
LEFT JOIN pet_project.fact_lesson_views flv ON flv.lesson_id = l.lesson_id
|
|
GROUP BY l.lesson_id, l.title, c.course_id, c.title;
|
|
|
|
-- Создание мат view
|
|
CREATE MATERIALIZED VIEW pet_project.mv_lesson_popularity_summary
|
|
ENGINE = SummingMergeTree()
|
|
ORDER BY (lesson_id, course_id)
|
|
POPULATE
|
|
AS
|
|
SELECT l.lesson_id AS lesson_id,
|
|
any(l.title) AS lesson_name,
|
|
c.course_id AS course_id,
|
|
any(c.title) AS course_name,
|
|
count() AS total_views,
|
|
uniq(flv.user_id) AS unique_users,
|
|
min(flv.viewed_at) AS first_date,
|
|
max(flv.viewed_at) AS last_date
|
|
FROM pet_project.lessons l
|
|
LEFT JOIN pet_project.courses c ON c.course_id = l.course_id
|
|
LEFT JOIN pet_project.fact_lesson_views flv ON flv.lesson_id = l.lesson_id
|
|
GROUP BY l.lesson_id, c.course_id;
|
|
|