Alex
28 lines
1.2 KiB
Plaintext
28 lines
1.2 KiB
Plaintext
CREATE MATERIALIZED VIEW users_cohort_analysis AS
|
|
with needed_users as (
|
|
select distinct u.id as user_id,
|
|
u.registration_date as reg_date, e.course_id, lv.id as view_id,
|
|
lv.lesson_id as viewed_lesson_id,
|
|
FIRST_VALUE(lv.viewed_at) over (partition by lv.user_id order by lv.viewed_at) as first_view_date
|
|
from users u
|
|
left join enrollments e on u.id = e.user_id
|
|
left join lesson_views lv on e.user_id = lv.user_id
|
|
order by user_id
|
|
),
|
|
CTE as (
|
|
select
|
|
TO_CHAR(reg_date, 'YYYY-MM') as cohort_month,
|
|
COUNT(distinct user_id) as users_in_cohort,
|
|
COUNT(distinct case when user_id in (select user_id from enrollments) then user_id end) as enrolled_users,
|
|
COUNT(distinct case when user_id in (select user_id from lesson_views) then user_id end) as active_users,
|
|
COUNT(course_id) as courses_count,
|
|
extract('days' from AVG(first_view_date - reg_date)) as avg_time_to_first_view
|
|
from needed_users
|
|
group by cohort_month
|
|
)
|
|
select cohort_month, users_in_cohort, enrolled_users, active_users,
|
|
ROUND(100.0 * active_users / users_in_cohort, 2) as retention_rate,
|
|
courses_count::numeric / users_in_cohort as avg_courses_per_user,
|
|
avg_time_to_first_view
|
|
from CTE
|
|
; |