George
24 lines
902 B
SQL
24 lines
902 B
SQL
CREATE MATERIALIZED VIEW course_summary as
|
|
with
|
|
lw as (
|
|
select
|
|
user_id,
|
|
lesson_id,
|
|
viewed_at
|
|
from lesson_views
|
|
)
|
|
select
|
|
id as course_id,
|
|
title as course_title,
|
|
count(distinct e.user_id) as unique_users,
|
|
count(distinct l.lesson_id) as lessons_count,
|
|
count(distinct lw.viewed_at) as total_views,
|
|
round(count(distinct lw.viewed_at)::decimal / count(distinct e.user_id), 2) as avg_views_per_user,
|
|
min(lw.viewed_at) as first_viewed_at,
|
|
max(lw.viewed_at) as last_viewed_at
|
|
from
|
|
courses as c
|
|
join (select course_id, user_id from enrollments) as e on c.id = e.course_id
|
|
join (select id as lesson_id, course_id from lessons) as l on c.id = l.course_id
|
|
join lw on l.lesson_id = lw.lesson_id
|
|
group by id; |