Tubi·Data Scientist·Technical Phone Screen
- 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.
“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.”