SERGEY_KONEV
29 lines
741 B
SQL
29 lines
741 B
SQL
/*
|
|
- ВИТРИНА АНАЛИЗА НЕАКТИВНЫХ ПОЛЬЗОВАТЕЛЕЙ
|
|
- ВСЕ УЧАСТВУЮЩИЕ ТАБЛИЦЫ DISTRIBUTED BY (user_id)
|
|
- ИТОГОВОЕ ПРЕДСТВАЛЕНИЕ ТАК ЖЕ DISTRIBUTED BY (user_id)
|
|
*/
|
|
|
|
|
|
CREATE MATERIALIZED VIEW IF NOT EXISTS dm.inactive_users_summary AS
|
|
-- EXPLAIN ANALYZE
|
|
DISTRIBUTED BY (user_id)
|
|
WITH t1 AS (
|
|
SELECT
|
|
user_id,
|
|
count(DISTINCT course_id) AS enrollments_count
|
|
FROM core.enrollments
|
|
GROUP BY user_id
|
|
)
|
|
SELECT
|
|
u.user_id,
|
|
u.name AS user_name,
|
|
u.email,
|
|
u.age,
|
|
coalesce(t1.enrollments_count, 0) AS enrollments_count,
|
|
u.registration_date
|
|
FROM core.dim_user u
|
|
LEFT JOIN t1 USING (user_id)
|
|
WHERE u.user_id NOT EXISTS (SELECT user_id FROM core.fact_lesson_views);
|
|
|