Coding questions · Tubi
Compute daily net users from event logs
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.
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.
Compute Daily Net Users by Device Platform from Event Logs
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.
Table events
| column | type | constraints |
|---|---|---|
| device_id | VARCHAR(50) | NOT NULL |
| event_name | VARCHAR(50) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| device_platform | VARCHAR(50) | NOT NULL |
Sample data:
| device_id | device_platform | event_name | ts |
|---|---|---|---|
| u1 | applemobile | app_open | 2025-08-31T23:50:00Z |
| u1 | applemobile | click | 2025-09-01T01:00:00Z |
| u2 | appletv | app_open | 2025-08-31T15:00:00Z |
| u2 | appletv | purchase | 2025-09-02T03:00:00Z |
| u3 | desktop | app_open | 2025-09-01T10:00:00Z |
| u4 | desktop | app_open | 2025-08-31T18:00:00Z |
| u5 | applemobile | app_open | 2025-09-01T16:00:00Z |
| u6 | applemobile | app_open | 2025-08-30T12:00:00Z |
| u6 | applemobile | app_open | 2025-08-31T12:00:00Z |
Expected output
One row per device_platform plus one overall row (device_platform = 'ALL') for event_date = 2025-09-01, showing counts of new, retained, churn, and net users based on the given definitions.
| churn_users | device_platform | event_date | net_users | new_users | retained_users |
|---|---|---|---|---|---|
| 3 | ALL | 2025-09-01 | 0 | 2 | 1 |
| 1 | applemobile | 2025-09-01 | 1 | 1 | 1 |
| 1 | appletv | 2025-09-01 | -1 | 0 | 0 |
| 1 | desktop | 2025-09-01 | 0 | 1 | 0 |
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.