project 01 Video Product Analytics

Understand viewer engagement.

Measure video-platform activity, viewing trends, retention and popular content.

business question

Move from watch events to daily engagement, rolling trends, and signup-cohort retention.

you deliver

Daily activity metrics, rolling trends, viewer retention and top-performing videos. Exported as a report with your checked SQL, result evidence and written findings.

skills

Aggregation · Window functions · Retention

scope

4 milestones · 4 tables · advanced

milestones

1. 01Daily active users & engagementMeasure daily activity and user engagement.
2. 02Rolling averages & missing datesCalculate rolling trends while accounting for missing dates.
3. 03Cohorts & exact-day retentionMeasure whether users return on a specific day after joining.
4. 04Top N per group & tiesFind the leading records within each group and handle ties.