Compute daily net users from event logs
Company: Tubi
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Given an event log, write SQL to compute, for the target date 2025‑09‑01 ("today"), the counts of new users, retained users, churn users, and net users, overall and by device_platform. Use these precise definitions: New user = device_id whose first‑ever event_date is 2025‑09‑01; Retained user = device_id active on both 2025‑08‑31 and 2025‑09‑01; Churn user = device_id active on 2025‑08‑31 but not active on 2025‑09‑01; Net users = New + Retained − Churn. Assume UTC dates (DATE(ts) in UTC). Output columns: event_date, device_platform, new_users, retained_users, churn_users, net_users. Use standard SQL with CTEs (no window functions required). Test against the sample below.
Schema:
- events(device_id STRING, event_name STRING, ts TIMESTAMP, device_platform STRING)
Sample rows (UTC):
+----------+-------------+---------------------+---------------+
|device_id |event_name |ts |device_platform|
+----------+-------------+---------------------+---------------+
|u1 |app_open |2025-08-31T23:50:00Z |applemobile |
|u1 |click |2025-09-01T01:00:00Z |applemobile |
|u2 |app_open |2025-08-31T15:00:00Z |appletv |
|u2 |purchase |2025-09-02T03:00:00Z |appletv |
|u3 |app_open |2025-09-01T10:00:00Z |desktop |
|u4 |app_open |2025-08-31T18:00:00Z |desktop |
|u5 |app_open |2025-09-01T16:00:00Z |applemobile |
|u6 |app_open |2025-08-30T12:00:00Z |applemobile |
|u6 |app_open |2025-08-31T12:00:00Z |applemobile |
+----------+-------------+---------------------+---------------+
Clarify any assumptions you need (e.g., how to treat users that switch device_platform between days) and produce a single query that returns the required aggregates.
Quick Answer: This question evaluates SQL-based data manipulation skills focused on daily aggregation, cohort and retention calculations, deduplicating event records, and grouping metrics by device_platform.