Understand viewer engagement.

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

Start project

The brief

The business question: Move from watch events to daily engagement, rolling trends, and signup-cohort retention.
What you’ll deliver: Daily activity metrics, rolling trends, viewer retention and top-performing videos.Exported as a report with business questions, checked SQL, result evidence and your written findings. Labeled Draft until every milestone has a finding.
Skills: Aggregation · Window functions · Retention
Scope: Advanced level · 4 milestones · 4 tables

Available data

Viewer signups and watch events. Repeated views are intentional; daily_metrics has missing days.

How the tables connect:watch_events.viewer_id → viewers.idwatch_events.video_id → videos.id

viewers4 columns · 8 rows · id, country, signup_at, referred_by

idintegerPK
countrytext
signup_attext
referred_byinteger

First 5 of 8 rows · original sample data

#	id	country	signup_at	referred_by
1	1	US	2025-01-01	NULL
2	2	US	2025-01-02	1
3	3	GB	2025-01-02	1
4	4	IN	2025-01-03	2
5	5	GB	2025-01-04	NULL

videos4 columns · 6 rows · id, creator_id, title, duration_seconds

idintegerPK
creator_idinteger
titletext
duration_secondsinteger

First 5 of 6 rows · original sample data

#	id	creator_id	title	duration_seconds
1	1	10	SQL in ten minutes	600
2	2	10	Understanding joins	900
3	3	11	A day outdoors	300
4	4	12	Window functions	720
5	5	12	Build a project	1200

watch_events5 columns · 26 rows · id, viewer_id, video_id, watched_at, …

idintegerPK
viewer_idinteger
video_idinteger
watched_attext
watch_secondsinteger

First 5 of 26 rows · original sample data

#	id	viewer_id	video_id	watched_at	watch_seconds
1	1	1	1	2025-01-01 10:00:00	300
2	2	1	2	2025-01-01 11:00:00	450
3	3	2	1	2025-01-02 12:00:00	600
4	4	3	3	2025-01-02 13:00:00	120
5	5	1	4	2025-01-02 14:00:00	700

daily_metrics2 columns · 5 rows · day, watch_minutes

daytextPK
watch_minutesinteger

First 5 of 5 rows · original sample data

#	day	watch_minutes
1	2025-01-01	30
2	2025-01-02	60
3	2025-01-04	90
4	2025-01-05	120
5	2025-01-07	60

Open a table to see its columns and sample rows. You do not need to read every table before starting a milestone.

Each run starts with the original sample data. Changes to tables do not carry over to later runs or milestones. Findings are optional for continuing — required only for a complete report.

Milestones

Start

Daily active users & engagement

Measure daily activity and user engagement.

Not started

Rolling averages & missing dates

Calculate rolling trends while accounting for missing dates.

Not started

Cohorts & exact-day retention

Measure whether users return on a specific day after joining.

Not started

Top N per group & ties

Find the leading records within each group and handle ties.

Not started

Project report

Loading saved progress…

Waiting for saved progress to load.