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.
Overview: 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.
You are given an event log for a product and must compute daily user lifecycle metrics for the target date 2025-09-01 (treated as "today"). Use UTC calendar dates derived from the event timestamp (DATE(ts) in UTC).
The metrics you need to compute are:
- **New users**: device_id whose first-ever event_date is **2025-09-01**.
- **Retained users**: device_id that is active on **both** 2025-08-31 and 2025-09-01.
- **Churn users**: device_id that is active on **2025-08-31 but not active on 2025-09-01**.
- **Net users**: New + Retained − Churn.
By **"active on a date"** we mean having at least one event whose DATE(ts) equals that calendar date.
By **device_platform** we mean:
- For **new** and **retained** users: use the device_platform from their activity on **2025-09-01**.
- For **churn** users: use the device_platform from their activity on **2025-08-31**.
Assumptions:
- Each device_id generates at most one device_platform per calendar date (i.e., no user switches platforms within the same day).
- A device_id may have multiple events on a given date; treat this as a single active user for that date.
Output the following columns:
- event_date (DATE) — this should be 2025-09-01 for all rows.
- device_platform (VARCHAR) — per-platform rows, plus an overall row where device_platform = 'ALL'.
- new_users (INT)
- retained_users (INT)
- churn_users (INT)
- net_users (INT)
Your task: Write a single **standard SQL** query (you may use CTEs; no window functions are required) that computes these metrics **overall and by device_platform** for the target date 2025-09-01 using the schema and sample data below.
Tables
events(device_id VARCHAR(50), event_name VARCHAR(50), ts TIMESTAMP, device_platform VARCHAR(50))
Hints
- Start by converting timestamps to calendar dates and deduplicating to one row per device_id, date, and device_platform.
- Use separate CTEs to compute: (1) each user's first-ever event_date, (2) who was active on 2025-08-31, and (3) who was active on 2025-09-01, then classify users into new, retained, and churn and aggregate by device_platform.