Tubi·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

SQL-heavy technical screen for a data scientist role at Tubi. One meaty question about computing user retention metrics from raw event logs, with a bunch of edge cases baked in to see if you'd actually think through the definitions.

Questions Asked (1)

Given a raw event log table, write SQL using CTEs to compute new users, retained users, churned users, and net users for a specific target date, broken down by device platform. Definitions are precise: new means first-ever event on that date, retained means active both the day before and on the target date, churned means active the day before but not on the target date, and net equals new plus retained minus churn.

Product Analytics & MetricsData Modeling

Author's notes

The definitions sound clean until you start writing the CTEs and realize you need to be careful about what 'first-ever event' means across all of history, not just the two days in scope.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions, then build CTEs to isolate active users on the target date and the day before, using first-ever event logic for new users. Finally, join these CTEs to compute new, retained, churned, and net users per platform, ensuring correct handling of edge cases like missing activity.

Pro tip: Always confirm the date boundaries and timezone assumptions, and explicitly state how you handle users with no prior activity (e.g., new users cannot be retained or churned). Also, consider using a calendar table or date spine to ensure all platforms are represented even if no activity occurred.

1. Clarify requirements and schema

Confirm the table structure, date column, user ID, platform, and event timestamp. Verify definitions of new, retained, churned, and net users, and the target date.

2. Identify active users per day

Create CTEs to get distinct users active on the target date and on the day before, grouped by platform. Use date functions to filter events.

3. Compute new users

Use a CTE to find users whose first-ever event (minimum event date) is the target date, grouped by platform.

4. Compute retained and churned users

Join the active users from the target date and day before: retained are those active on both days; churned are those active on the day before but not on the target date.

5. Combine metrics and compute net

Join the new, retained, and churned CTEs by platform, then calculate net as new + retained - churned. Handle missing platforms with COALESCE or a platform dimension table.

Key Points to Mention

Use of CTEs for modularity and readability, breaking down the problem into active users, new users, and retention/churn.
Correctly defining 'new' as first-ever event on the target date, which requires a subquery or window function to find the minimum event date per user.
Handling of users with no activity on the day before (they cannot be retained or churned) and ensuring no double-counting.
Joining CTEs on platform and date, and using LEFT JOINs or UNION to include all platforms even if no users in a category.
Computing net users as new + retained - churned, and validating that the sum of new and retained minus churn equals the total active users on the target date.
Considering performance implications: filtering early, using appropriate indexes, and avoiding unnecessary cross joins.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.