George
24 lines
713 B
SQL
24 lines
713 B
SQL
create materialized view platform_summary as
|
|
with
|
|
lv_stats as (
|
|
select
|
|
count(distinct user_id) as users_with_views,
|
|
count(*)::decimal / nullif(count(distinct lesson_id), as avg_views_per_lesson
|
|
from lesson_views
|
|
)
|
|
select
|
|
total_users,
|
|
total_courses,
|
|
total_lessons,
|
|
users_with_views,
|
|
round((total_lessons::decimal / nullif(total_courses, 2)), 2) AS avg_lessons_per_course,
|
|
round(avg_views_per_lesson, 2) as avg_views_per_lesson
|
|
from
|
|
(
|
|
select
|
|
(select count(*) from users) as total_users,
|
|
(select count(*) from courses) as total_courses,
|
|
count(*) as total_lessons
|
|
from lessons
|
|
) as s
|
|
cross join lv_stats as lv; |