Streaming Platform Data Analysis Scenarios Questions

Data analysis scenarios for subscription video streaming platforms, covering streaming and engagement metrics (DAU/MAU, watch time, completion rate, session length), user retention and cohort analysis, content consumption patterns, recommendation system evaluation, A/B test design and analysis, and data visualization and storytelling in the streaming domain.

HardTechnical

68 practiced

You need to standardise metric definitions and ensure consistent computation of core KPIs (e.g., monthly active users, churn) across multiple product teams. Propose a metric governance framework: tooling, semantic layer, documentation standards, testing, owner roles, and an enforcement workflow for proposed metric changes.

Sample Answer

Situation: Multiple product teams define the same KPIs differently (MAU, churn), causing conflicting dashboards and poor decision-making.

Proposal (framework I’d implement as BI Analyst):

1. Tooling- Semantic layer: dbt for transformations + a metric layer (dbt metrics or Lightdash/LookML/Looker Metrics) to centralize definitions.- Catalog & lineage: Collibra/Alation or open-source Amundsen for discoverability and lineage.- Testing & monitoring: Great Expectations + Monte Carlo or Airflow + custom checks.- CI/CD: GitHub/GitLab for PRs, automated dbt tests, and deployment pipelines.

2. Semantic layer & registry- Single source of truth: a metrics registry in the semantic layer (name, SQL, tags, owner, version, sensitivity).- Expose metrics via BI tool connectors so dashboards reference the metric, not raw SQL.

3. Documentation standards- For each metric: formal definition (business intent), formula, cohort/window rules, data sources, update cadence, examples, edge cases, and expected slack sample queries.- Store docs in the catalog and link from dashboards.

4. Testing & validation- Unit tests (dbt tests) for logic, integration tests comparing metric outputs across environments, anomaly detection alerts post-deploy.- Backfill/backtest process when logic changes.

5. Owner roles- Metric Owner (product BI or product PM): responsible for correctness and proposals.- Data Steward (central data team): ensures schema/lineage integrity.- Approver Committee: cross-functional reps (BI, Product, Engineering, Legal for PII) that approve changes.

6. Enforcement workflow for changes- Proposer opens a metric-change PR in Git with change rationale, impact analysis, test cases, and affected dashboards.- Automated CI runs tests; change is deployed to staging.- Approval required from Metric Owner + one Approver Committee member.- Post-approve: staggered deploy to prod with monitoring; auto-notify downstream dashboard owners of change and link to migration plan.- If regression detected, automated rollback and incident review within 48 hours.

Result: consistent KPI answers, faster audits, fewer conflicts, traceable ownership, and safe iterative improvements. This balances product autonomy with centralized governance.

MediumTechnical

84 practiced

Design an operational approach to detect and remediate missing play_events in analytics (for example due to SDK failures or ingestion pipeline issues). Describe checks, monitoring, alerting thresholds, automated reconciliation queries, and a backfill strategy.

Sample Answer

Situation: As a BI analyst, I need reliable play_event data (e.g., video plays) for dashboards that drive product and marketing decisions. SDK or ingestion failures can cause missing events and misleading KPIs.

Operational approach:

1) Checks (daily automated)- Ingestion-level sanity: row counts per hour/day vs. expected (see baselines).- Schema/field completeness: % of events missing critical fields (user_id, event_type, timestamp).- Behavioral baselines: rolling-7-day median play_events per active_user and per region.- Upstream heartbeat: last SDK heartbeat timestamp and delivery latency distribution.

2) Monitoring & alerting thresholds- Volume drop alert: current 1-hour event count < 50% of 1-hour rolling median → Severity P1.- Gradual decline: 24-hour total < 75% of 7-day baseline → P2.- Field-loss: critical-field missing rate > 5% in last 6 hours → P1.- Latency spike: median ingestion latency increases > 3x baseline → P2.Alerts push to Slack + PagerDuty depending on severity.

3) Automated reconciliation queries- Hourly SQL comparing source vs. warehouse aggregated counts and user-IDs:

sql

-- hourly reconciliation: source events vs warehouse
WITH src AS (
  SELECT date_trunc('hour', event_ts) AS hr, count(*) AS src_cnt
  FROM raw_ingest.play_events_src
  WHERE event_ts >= now() - interval '48 hours'
  GROUP BY 1
),
wh AS (
  SELECT date_trunc('hour', event_ts) AS hr, count(*) AS wh_cnt
  FROM analytics.play_events
  WHERE event_ts >= now() - interval '48 hours'
  GROUP BY 1
)
SELECT s.hr, s.src_cnt, coalesce(w.wh_cnt,0) AS wh_cnt, (coalesce(w.wh_cnt,0)::float / s.src_cnt) AS ratio
FROM src s LEFT JOIN wh w USING (hr)
ORDER BY hr DESC;

- Daily user-level reconciliation to find missing user-hours and top affected cohorts.

4) Automated remediation- If ratio < threshold (e.g., <0.9) for recent hours, trigger: - Re-run ingestion for affected time windows (idempotent) via orchestration (Airflow). - Notify SDK/telemetry owners with evidence (query outputs, sample event IDs).- Maintain runbook mapping alerts → remediation steps and owners.

5) Backfill strategy- Prioritize windows by business impact (last 48–72 hours first).- Use immutable raw backups or S3 raw files to reprocess; if only partial source, run best-effort reconstruction with join to related events and sessionization.- Track backfill jobs in a metadata table with status, rows inserted, and dashboards impacted; once completed, trigger downstream refresh of materialized views and BI extracts.- For persistent SDK bugs, patch and deploy SDK, then re-ingest historical raw files and run data integrity checks.

Metrics & governance- Track MTTR (median time to detect and remediate), percent of dashboards affected, and recurrence rate.- Monthly review with engineering to close root causes and reduce manual backfills.

This approach combines automated detection, prioritized remediation, and measurable backfills so BI reports remain trustworthy and incidents shrink over time.

MediumTechnical

109 practiced

Compare offline and online evaluation metrics for a recommendation algorithm aimed at increasing total watch minutes. Discuss precision/recall, NDCG, MAP as offline proxies, and online KPI alignment (watch minutes, retention). Explain pitfalls when offline metrics diverge from online outcomes and methods to reduce the gap.

Sample Answer

Situation: You're evaluating a recommender whose business goal is to increase total watch minutes. Offline metrics (precision/recall, NDCG, MAP) are useful proxies but have limits; as a BI analyst you need to translate them to expected impact on online KPIs (watch minutes, retention).

Comparison / what they measure:- Precision/Recall: measure relevance/binary hit-rate on held-out interactions. Good for "does the user click/watch at least once" but ignore watch duration and sequential effects.- NDCG: ranks items by graded relevance; rewards putting highly relevant items early. Better proxy when position matters and when relevance correlates with duration.- MAP: average precision across queries/users; emphasizes correct ranking of relevant items across the list, useful for consistent relevance but still ignores time spent.

Online KPI alignment:- Watch minutes depend on both selection and engagement length — a clicked item may yield 30s or 30min. Retention depends on long-term satisfaction and cohort effects (e.g., repeated good recommendations).

Pitfalls when offline diverges from online:- Label bias: training/eval labels reflect past exposure, not true preference.- Exposure / position bias: historical position effects inflate metrics.- Short-session clicks dominating labels lead to misleadingly high precision but low minutes.- Distribution shift between offline test set and live traffic.

Methods to reduce the gap:- Use enriched labels: weight interactions by watch duration (e.g., minutes-weighted relevance) or use graded labels for NDCG.- Session-aware and sequential offline simulations: evaluate predicted session watch-time, not just clicks.- Counterfactual / causal approaches: inverse propensity scoring to correct exposure bias.- Build surrogate offline KPIs that map to business metrics (predicted watch minutes per impression) and validate with small A/B tests.- Continuous validation: ship small, time-boxed experiments and instrument dashboards that compare offline predictions vs. real watch minutes/retention by cohort.- Monitor uplift and long-term metrics (D7/D30 retention) not just immediate watch minutes.

Practical BI actions:- Create dashboards showing correlation between offline metrics and online watch minutes by cohort, content-type, and user segments.- Add experiment telemetry: predicted vs. observed watch minutes, click-to-watch-duration distribution, and propensity-corrected lifts to quickly detect divergence and guide model iteration.

EasyTechnical

90 practiced

When aggregating play events across global users, how do you handle timezones correctly? Discuss event-time vs ingestion-time, best practices for storing timestamps, converting to local user time for daily metrics, and pitfalls when users travel frequently.

Sample Answer

Clear handling of timezones is critical for accurate user-level daily metrics. Key concepts: event-time (the timestamp when the user action actually happened) vs ingestion-time (when the event arrived to your system). Always prefer event-time for analytics because ingestion-time can be delayed or reordered.

Best practices:- Store both: persist the raw event_time (ISO 8601 with timezone or UTC + offset) and ingestion_time. Example: event_time = "2025-06-01T23:45:00-07:00", event_time_utc = "2025-06-02T06:45:00Z", ingestion_time = "2025-06-02T06:50:12Z".- Normalize and store UTC for query efficiency, but keep the original timezone/offset or the user’s timezone preference so you can reconstruct local time.- For daily metrics by user local day, convert event_time_utc to the user’s current timezone (or stored home timezone) and bucket by local date. Use a reliable timezone DB (IANA tz) to account for DST.

Handling traveling users:- Decide business rule: use the user's account timezone, the device-reported timezone at event_time, or the geolocation at event_time. Document trade-offs.- Common choice for BI: default to user's primary account timezone for consistent lifetime metrics; use device timezone for session/behavior analysis where local context matters.- When users change timezones frequently, tag events with both event_local_offset and resolved IANA zone at event_time; when bucketing, use the offset/zone present at the moment of the event.

Pitfalls:- Relying only on local date at ingestion causes mis-bucketed events if ingestion delayed across midnight.- Ignoring DST and historical timezone changes leads to incorrect bucketing.- Using only ingestion_time hides late-arriving events; use event-time windowing with allowed lateness in pipelines.

Operational tips:- Implement event-time windowing with configurable lateness in ETL.- Validate with audits: compare counts by event_time vs ingestion_time and monitor late arrivals.- Document the chosen rule in dashboards so stakeholders understand how “daily” is defined.

MediumTechnical

65 practiced

You run an experiment testing 3 different artwork thumbnails for the same title to see if they increase start rate and play-through rate. Design the A/B test: define primary and secondary metrics, sample size / power calculation assumptions, randomization unit, guardrail metrics, and how you'd interpret results if variants increase starts but reduce completion.

Sample Answer

Primary metric:- Start rate (percentage of users who start playback after seeing the thumbnail). This aligns directly with the test objective.

Secondary metrics:- Play-through rate / completion rate (percentage of started plays that reach a defined completion threshold, e.g., 90% or episode end).- Time-to-start (latency from impression to start).- Engagement downstream: total watch time per user in session, next-content starts within 24h.

Randomization unit:- Randomize at the user level (user_id) to avoid within-session contamination and ensure consistent experience across exposures. Use hashing of user_id + experiment id for deterministic assignment.

Sample size / power assumptions:- Baseline start rate p0 (estimate from recent data), e.g., 6%.- Minimum Detectable Effect (MDE): choose business-significant lift, e.g., +10% relative (to 6% → 6.6% absolute 0.6pp).- Power 80%, alpha 0.05, two-sided.- Use standard proportions formula or online calculator to compute N per arm; with p0=0.06, p1=0.066, N ≈ ~150k users/arm (example—compute precisely with current p0 and MDE).

Guardrail metrics:- Completion/play-through rate (must not degrade beyond a tolerance, e.g., -3% relative).- Session-level churn/exit rate, ads views (if relevant), revenue per user, error rates.- Monitor metric deltas with sequential checks (pre-registered) and safety thresholds to stop early.

Analysis plan & interpretation:- Primary analysis: compare start rates using two-sided z-test (or logistic regression controlling for stratifiers like device/region/time).- Adjust for multiple comparisons (3 variants vs control) using Bonferroni or FDR.- If variants increase starts but reduce completion: measure net value — e.g., additional starts × average completion value vs loss from reduced completion (watch time, subscriptions, ad revenue). If increased starts lead to more short-abandon plays that hurt downstream engagement or revenue, consider rejecting variant despite higher starts.- Consider heterogeneity: segment by content length, device, user tenure. If variants only harm completion in certain segments, target rollout to segments with net-positive impact or iterate thumbnails.- Report results with confidence intervals, absolute and relative deltas, and recommended action (rollout, iterate, or abort) backed by business impact calculation.

Unlock Full Question Bank

Get access to hundreds of Streaming Platform Data Analysis Scenarios interview questions and detailed answers.

Join thousands of developers preparing for their dream job.