Quick Overview

This question evaluates a candidate's proficiency in SQL data aggregation and construction of cumulative metrics for streaming analytics, including handling alignment of records when identifiers may appear in only one dataset.

Aggregate Netflix metrics in SQL

Company: Meta

Role: Data Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

##### Question Netflix video-streaming analytics SQL: Write a simple aggregation (e.g., total watch-time per day). Build a cumulative metric: today’s metric = today + yesterday, but IDs can exist in only one side; use a FULL JOIN to align.

Overview: This question evaluates a candidate's proficiency in SQL data aggregation and construction of cumulative metrics for streaming analytics, including handling alignment of records when identifiers may appear in only one dataset.

Daily total watch-time

From the Netflix-style watch_events table, compute the total watch-time per calendar date. Return one row per date with columns watch_date and total_watch_seconds, ordered by watch_date ascending.

Tables

watch_events(event_id INTEGER, user_id INTEGER, content_id INTEGER, started_at TIMESTAMP, watch_seconds INTEGER)

Hints

  1. Convert started_at to a date to form the per-day buckets.
  2. SUM watch_seconds and GROUP BY the date column.

Two-day per-user cumulative watch-time with FULL JOIN

For a given 'today' date, compute each user's two-day watch-time equal to today's watch-time plus yesterday's watch-time. Users may appear on only one of the two days, so use a FULL OUTER JOIN to align per-user totals for the two dates. Using today = 2025-01-02, return user_id and two_day_watch_seconds.

Tables

watch_events(event_id INTEGER, user_id INTEGER, content_id INTEGER, started_at TIMESTAMP, watch_seconds INTEGER)

Hints

  1. First aggregate to per-user, per-day totals from watch_events.
  2. Parameterize the target date (today) and define yesterday as today - INTERVAL '1 day'.

Community answers

Answer by ginb

part 2 SELECT COALESCE(t.user_id, y.user_id) AS user_id, (COALESCE(t.seconds, 0) + COALESCE(y.seconds, 0)) AS two_day_watch_seconds FROM (SELECT user_id, SUM(watch_seconds) as seconds FROM logs WHERE date = '2025-01-02' GROUP BY 1) t FULL OUTER JOIN (SELECT user_id, SUM(watch_seconds) as seconds FROM logs WHERE date = '2025-01-01' GROUP BY 1) y ON t.user_id = y.user_id;

Loading coding console...