dr0zd
84 lines
2.7 KiB
SQL
84 lines
2.7 KiB
SQL
CREATE MATERIALIZED VIEW dm_user_activity_summary as
|
|
with enrollment_counts as(
|
|
select user_id,
|
|
count(course_id) as enrollment_count
|
|
from enrollments e
|
|
group by user_id
|
|
),views as(
|
|
select user_id,
|
|
count(distinct lesson_id) as viewed_lessons_count,
|
|
min(viewed_at) as first_view,
|
|
max(viewed_at) as last_view
|
|
from lessons_views
|
|
group by user_id
|
|
), total_lessons as(
|
|
select user_id,
|
|
count(*) as total_lessons
|
|
from enrollments e
|
|
join lessons using(course_id)
|
|
group by user_id
|
|
order by user_id
|
|
), engagment_rate as (
|
|
select tl.user_id, round(v.viewed_lessons_count*100.0/nullif(total_lessons), 2) as engagment_rate
|
|
from total_lessons tl
|
|
join views v using(user_id)
|
|
)
|
|
select u.id, u.name, enrollment_count, viewed_lessons_count, last_view, first_view, engagment_rate
|
|
from users u
|
|
join enrollment_counts ec on u.id = ec.user_id
|
|
join views v on u.id = v.user_id
|
|
join engagment_rate er on u.id = er.user_id
|
|
order by u.id
|
|
/*
|
|
--первая попытка сделать витрину, делал избыточно с проверкой каждого подзапроса, поэтому забыл убрать order by, а также больше практиковался в оконных функциях
|
|
CREATE MATERIALIZED VIEW user_activity_summary as
|
|
with enrollment_counts as(
|
|
select distinct user_id,
|
|
count(course_id) over (partition by user_id) as enrollment_count
|
|
from enrollments e
|
|
order by user_id
|
|
), viewed_lessons_count as (
|
|
select
|
|
user_id, count(distinct lesson_id) as viewed_lessons_count
|
|
from lessons_views lv
|
|
group by user_id
|
|
order by user_id
|
|
), first_views as(
|
|
select
|
|
user_id,
|
|
viewed_at,
|
|
row_number() over (partition by user_id order by viewed_at asc) as rnf
|
|
from lessons_views
|
|
), last_views as(
|
|
select
|
|
user_id,
|
|
viewed_at,
|
|
row_number() over (partition by user_id order by viewed_at desc) as rnl
|
|
from lessons_views
|
|
), result_views as(
|
|
select user_id,
|
|
fw.viewed_at as first_view,
|
|
lw.viewed_at as last_view
|
|
from first_views fw
|
|
join last_views lw using(user_id)
|
|
where rnf = 1 and rnl = 1
|
|
), total_lessons as(
|
|
select user_id,
|
|
count(*) as total_lessons
|
|
from enrollments e
|
|
join lessons using(course_id)
|
|
group by user_id
|
|
order by user_id
|
|
), engagment_rate as (
|
|
select tl.user_id, round(vlc.viewed_lessons_count*100.0/total_lessons, 2) as engagment_rate
|
|
from total_lessons tl
|
|
join viewed_lessons_count vlc using(user_id)
|
|
)
|
|
select u.id, u.name, enrollment_count, viewed_lessons_count, last_view, first_view, engagment_rate
|
|
from users u
|
|
join enrollment_counts ec on u.id = ec.user_id
|
|
join viewed_lessons_count vlc on u.id = vlc.user_id
|
|
join result_views rv on u.id = rv.user_id
|
|
join engagment_rate er on u.id = er.user_id
|
|
order by u.id
|
|
*/ |