Claude suggested revision:
/* ============================================================
Video Studio Usage through CDM_MEDIA
Corrected version — fixes:
1. viewer identity bug (was resolving creator, not viewer)
2. removed redundant cdm_map.course join in video_summary
3. added media_deleted_time IS NULL filter to exclude deleted videos
4. changed ceil() to round() for duration_mins
5. note added re: stage VARIANT column reliability
============================================================ */
with node_course as (
select
ifnull(ih.hierarchy_name_seq, 'No Node') as ih_node,
ifnull(lt.name, 'No Term') as term,
lc.id as course_id,
lc.name as course_name,
lc.course_number,
mpc.media_container_id
from cdm_lms.course lc
left join cdm_lms.institution_hierarchy_course ihc
on ihc.course_id = lc.id
and ihc.primary_ind = 1
left join cdm_lms.institution_hierarchy ih
on ih.id = ihc.institution_hierarchy_id
left join cdm_lms.term lt
on lt.id = lc.term_id
-- Bridge to media container; kept here so video_summary can join on it
inner join cdm_map.course mpc
on mpc.lms_course_id = lc.id
)
-- select * from node_course limit 100; -- UNCOMMENT TO TEST
, video_summary as (
select
nc.ih_node,
nc.term,
nc.course_id,
nc.course_name,
nc.course_number,
mm.id as media_id,
-- NOTE: mm.stage is a VARIANT staging column; confirm field availability
-- in your CDM Media schema before relying on mimetype/language in reporting
mm.stage:mimetype::string as mime_type,
mm.stage:language::string as language,
round(mm.media_duration / 60, 1) as duration_mins, -- round vs ceil to avoid inflation
mm.media_size,
concat(lp.first_name, ' ', lp.last_name) as creator_name,
mm.media_created_time,
mm.media_deleted_time
from cdm_media.media mm
-- FIX: join node_course directly on container_id — no need for a second
-- cdm_map.course join here since node_course already resolved it
left join node_course nc
on nc.media_container_id = mm.container_id
left join cdm_map.person mpp
on mpp.media_person_id = mm.owner_person_id
inner join cdm_lms.person lp
on lp.id = mpp.lms_person_id
-- FIX: exclude deleted videos
where mm.media_deleted_time is null
)
-- select * from video_summary limit 100; -- UNCOMMENT TO TEST
, video_usage as (
select
nc.ih_node,
nc.term,
nc.course_id,
nc.course_name,
nc.course_number,
msa.container_id,
msa.media_id,
-- FIX: resolve viewer identity from msa.person_id (the watcher),
-- not from mm.owner_person_id (the creator)
msa.person_id,
concat(viewer_lp.first_name, ' ', viewer_lp.last_name) as viewer_name,
viewer_lp.email as viewer_email,
mm.media_duration,
count(msa.id) as view_count,
round(sum(msa.duration_sum) / 60) as minutes_viewed,
round(div0(sum(msa.duration_sum), mm.media_duration), 2) as pc_viewed,
min(msa.first_accessed_time)::date as first_access_date,
max(msa.first_accessed_time)::date as last_access_date
from cdm_media.session_activity msa
inner join cdm_media.media mm
on msa.media_id = mm.id
-- FIX: map the viewer (msa.person_id) — separate from the creator join above
left join cdm_map.person viewer_mpp
on viewer_mpp.media_person_id = msa.person_id
inner join cdm_lms.person viewer_lp
on viewer_lp.id = viewer_mpp.lms_person_id
left join node_course nc
on nc.media_container_id = msa.container_id
-- Exclude activity on deleted videos
where mm.media_deleted_time is null
group by all
)
-- select * from video_usage limit 100; -- UNCOMMENT TO TEST
/** UNCOMMENT (delete the first two dashes of) ONE OF THE FOLLOWING LINES AND RUN TO PRODUCE RESULTS **/
/* How many videos have been created per month? */
-- select date_trunc('month', media_created_time)::date as month, count(media_id) as video_count, sum(duration_mins) as total_mins from video_summary group by 1 order by 1;
/* Which nodes have the most videos? */
-- select ih_node, count(distinct course_id) as course_using_count, count(media_id) as video_count, sum(duration_mins) as total_duration from video_summary group by 1 order by course_using_count desc limit 25;
/* Which courses have the most videos? */
-- select ih_node, term, course_number, course_name, count(media_id) as video_count, sum(duration_mins) as total_duration from video_summary group by all order by video_count desc limit 25;
/* Which instructors have the most videos? */
-- select creator_name, count(media_id) as video_count, sum(duration_mins) as total_duration from video_summary group by 1 order by video_count desc limit 25;
/* Which videos have the most views?
NOTE: avg(pc_viewed) is a weighted average concern — these are already per-person
session aggregates, so treat avg_pc_viewed as an approximation. */
-- select ih_node, term, course_number, course_name, media_id, media_duration, sum(view_count) as total_views, avg(pc_viewed) as avg_pc_viewed from video_usage group by all order by total_views desc limit 25;
/* Which courses have the longest videos? */
-- select ih_node, term, course_number, course_name, count(media_id) as video_count, max(zeroifnull(duration_mins)) as max_duration from video_summary group by all order by max_duration desc limit 25;
/* Which students have the most usage? */
-- select viewer_name, viewer_email, count(distinct container_id) as active_course_count, count(distinct media_id) as active_media_count, sum(view_count) as total_views, sum(minutes_viewed) as total_minutes, max(pc_viewed) as max_pc_viewed from video_usage group by all order by total_views desc limit 25;
Claude suggested revision:
/* ============================================================
Video Studio Usage through CDM_MEDIA
Corrected version — fixes:
1. viewer identity bug (was resolving creator, not viewer)
2. removed redundant cdm_map.course join in video_summary
3. added media_deleted_time IS NULL filter to exclude deleted videos
4. changed ceil() to round() for duration_mins
5. note added re: stage VARIANT column reliability
============================================================ */
with node_course as (
select
ifnull(ih.hierarchy_name_seq, 'No Node') as ih_node,
ifnull(lt.name, 'No Term') as term,
lc.id as course_id,
lc.name as course_name,
lc.course_number,
mpc.media_container_id
from cdm_lms.course lc
left join cdm_lms.institution_hierarchy_course ihc
on ihc.course_id = lc.id
and ihc.primary_ind = 1
left join cdm_lms.institution_hierarchy ih
on ih.id = ihc.institution_hierarchy_id
left join cdm_lms.term lt
on lt.id = lc.term_id
-- Bridge to media container; kept here so video_summary can join on it
inner join cdm_map.course mpc
on mpc.lms_course_id = lc.id
)
-- select * from node_course limit 100; -- UNCOMMENT TO TEST
, video_summary as (
select
nc.ih_node,
nc.term,
nc.course_id,
nc.course_name,
nc.course_number,
mm.id as media_id,
-- NOTE: mm.stage is a VARIANT staging column; confirm field availability
-- in your CDM Media schema before relying on mimetype/language in reporting
mm.stage:mimetype::string as mime_type,
mm.stage:language::string as language,
round(mm.media_duration / 60, 1) as duration_mins, -- round vs ceil to avoid inflation
mm.media_size,
concat(lp.first_name, ' ', lp.last_name) as creator_name,
mm.media_created_time,
mm.media_deleted_time
from cdm_media.media mm
-- FIX: join node_course directly on container_id — no need for a second
-- cdm_map.course join here since node_course already resolved it
left join node_course nc
on nc.media_container_id = mm.container_id
left join cdm_map.person mpp
on mpp.media_person_id = mm.owner_person_id
inner join cdm_lms.person lp
on lp.id = mpp.lms_person_id
-- FIX: exclude deleted videos
where mm.media_deleted_time is null
)
-- select * from video_summary limit 100; -- UNCOMMENT TO TEST
, video_usage as (
select
nc.ih_node,
nc.term,
nc.course_id,
nc.course_name,
nc.course_number,
msa.container_id,
msa.media_id,
-- FIX: resolve viewer identity from msa.person_id (the watcher),
-- not from mm.owner_person_id (the creator)
msa.person_id,
concat(viewer_lp.first_name, ' ', viewer_lp.last_name) as viewer_name,
viewer_lp.email as viewer_email,
mm.media_duration,
count(msa.id) as view_count,
round(sum(msa.duration_sum) / 60) as minutes_viewed,
round(div0(sum(msa.duration_sum), mm.media_duration), 2) as pc_viewed,
min(msa.first_accessed_time)::date as first_access_date,
max(msa.first_accessed_time)::date as last_access_date
from cdm_media.session_activity msa
inner join cdm_media.media mm
on msa.media_id = mm.id
-- FIX: map the viewer (msa.person_id) — separate from the creator join above
left join cdm_map.person viewer_mpp
on viewer_mpp.media_person_id = msa.person_id
inner join cdm_lms.person viewer_lp
on viewer_lp.id = viewer_mpp.lms_person_id
left join node_course nc
on nc.media_container_id = msa.container_id
-- Exclude activity on deleted videos
where mm.media_deleted_time is null
group by all
)
-- select * from video_usage limit 100; -- UNCOMMENT TO TEST
/** UNCOMMENT (delete the first two dashes of) ONE OF THE FOLLOWING LINES AND RUN TO PRODUCE RESULTS **/
/* How many videos have been created per month? */
-- select date_trunc('month', media_created_time)::date as month, count(media_id) as video_count, sum(duration_mins) as total_mins from video_summary group by 1 order by 1;
/* Which nodes have the most videos? */
-- select ih_node, count(distinct course_id) as course_using_count, count(media_id) as video_count, sum(duration_mins) as total_duration from video_summary group by 1 order by course_using_count desc limit 25;
/* Which courses have the most videos? */
-- select ih_node, term, course_number, course_name, count(media_id) as video_count, sum(duration_mins) as total_duration from video_summary group by all order by video_count desc limit 25;
/* Which instructors have the most videos? */
-- select creator_name, count(media_id) as video_count, sum(duration_mins) as total_duration from video_summary group by 1 order by video_count desc limit 25;
/* Which videos have the most views?
NOTE: avg(pc_viewed) is a weighted average concern — these are already per-person
session aggregates, so treat avg_pc_viewed as an approximation. */
-- select ih_node, term, course_number, course_name, media_id, media_duration, sum(view_count) as total_views, avg(pc_viewed) as avg_pc_viewed from video_usage group by all order by total_views desc limit 25;
/* Which courses have the longest videos? */
-- select ih_node, term, course_number, course_name, count(media_id) as video_count, max(zeroifnull(duration_mins)) as max_duration from video_summary group by all order by max_duration desc limit 25;
/* Which students have the most usage? */
-- select viewer_name, viewer_email, count(distinct container_id) as active_course_count, count(distinct media_id) as active_media_count, sum(view_count) as total_views, sum(minutes_viewed) as total_minutes, max(pc_viewed) as max_pc_viewed from video_usage group by all order by total_views desc limit 25;