Alex
26 lines
948 B
Plaintext
26 lines
948 B
Plaintext
CREATE MATERIALIZED VIEW platform_summary AS
|
|
with users_counts as (
|
|
select
|
|
COUNT(distinct u.id) as total_users,
|
|
COUNT(distinct lv.user_id) as users_with_views
|
|
from users u
|
|
left join lesson_views lv on lv.user_id = u.id
|
|
),
|
|
courses_lessons_count as (
|
|
select COUNT(distinct c.id) as total_courses,
|
|
COUNT(distinct l.id) as total_lessons
|
|
from courses c
|
|
left join lessons l on l.course_id = c.id
|
|
),
|
|
views_count as (
|
|
select COUNT(distinct lv.lesson_id) as lessons_with_views,
|
|
COUNT(distinct lv.id) as total_views
|
|
from lesson_views lv
|
|
)
|
|
select uc.total_users, clc.total_courses, clc.total_lessons, uc.users_with_views,
|
|
ROUND(1.00 * clc.total_lessons / nullif(clc.total_courses, 0), 2) as avg_lessons_per_course,
|
|
ROUND(1.00 * vc.total_views / nullif(clc.total_lessons, 0), 2) as avg_views_per_lesson
|
|
from users_counts uc
|
|
cross join courses_lessons_count clc
|
|
cross join views_count vc
|
|
; |