George
35 lines
1.1 KiB
SQL
35 lines
1.1 KiB
SQL
-- Витрина активности пользователей
|
|
CREATE MATERIALIZED VIEW user_activity_summary as
|
|
with
|
|
lessons_cte as (
|
|
select
|
|
user_id,
|
|
count (*) as viewed_lessons_count,
|
|
max(viewed_at) as last_view,
|
|
min(viewed_at) as first_view
|
|
from lesson_views
|
|
group by user_id
|
|
),
|
|
enrollments_cte as (
|
|
select user_id, count(*) as enrollment_count
|
|
from enrollments as e
|
|
group by user_id
|
|
),
|
|
engagement_cte as (
|
|
select user_id, count(*) as possible_lessons
|
|
from lessons as l
|
|
join enrollments as en on l.course_id = en.course_id
|
|
group by user_id
|
|
)
|
|
select
|
|
id as user_id,
|
|
name,
|
|
e.enrollment_count,
|
|
l.viewed_lessons_count,
|
|
l.last_view,
|
|
l.first_view,
|
|
round(l.viewed_lessons_count::decimal * 100 / nullif(en.possible_lessons, 0), 2) as engagement_rate
|
|
from users as u
|
|
join lessons_cte as l on u.id = l.user_id
|
|
join engagement_cte as en on u.id = en.user_id
|
|
join enrollments_cte as e on u.id = e.user_id; |