Video Product Analytics

# Video Product Analytics

_Empty report_

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

- Sample database: Streamly (viewers, videos, watch_events, daily_metrics)
- Milestones completed (SQL + concept check): 0 of 4
- Findings written: 0 of 4
- Verified checks (Check answer passed): 0
- Exported: 2026-10-14T18:01:15.284Z

Findings are the learner’s own interpretation. Passing a SQL check does not verify the written finding.

## Milestone 1: Daily active users & engagement

Status: Not started

_No finding written yet._

### 1.1 Return day (YYYY-MM-DD) and active_viewers (distinct viewers) for every observed watch date.

_not attempted_

### 1.2 Return day, active_viewers and minutes_per_active_viewer, rounded to 2 decimals, for each observed watch date.

_not attempted_

## Milestone 2: Rolling averages & missing dates

Status: Not started

_No finding written yet._

### 2.1 Return day, watch_minutes and rolling_avg (2 decimals) for the current and previous 2 observed rows in daily_metrics, sorted by day. This task intentionally uses rows, not calendar days.

_not attempted_

### 2.2 Return day, watch_minutes and rolling_avg (2 decimals) for every day Jan 1–7 2025, treating absent daily_metrics days as zero. Average current and previous 2 calendar days; early windows use available days. Sort by day.

_not attempted_

## Milestone 3: Cohorts & exact-day retention

Status: Not started

_No finding written yet._

### 3.1 With observation cutoff Jan 15 2025, return cohort_day, cohort_size, retained_viewers and retention_pct (0–100, 2 decimals) for exact-day D1 retention. Include only mature cohorts.

_not attempted_

### 3.2 Use the same output and cutoff for exact-day D7 retention (signup date +7 days); include only mature cohorts.

_not attempted_

## Milestone 4: Top N per group & ties

Status: Not started

_No finding written yet._

### 4.1 Return creator_id, video_id and watch_seconds for the top 2 watched videos per creator, excluding videos with no events. Rank by total seconds descending, break ties by smallest video_id.

_not attempted_

### 4.2 Return creator_id, video_id and watch_seconds for each creator's most-watched video(s), including ties. Exclude videos with no events.

_not attempted_