Google·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say

Jun 2026Remote

Summary

Got a technical screen for a Data Scientist role at Google and the SQL question they threw at me was genuinely one of the harder ones I've seen. Single query, no temp tables, identity merges, gap logic, churn cutoffs. I survived but I'm not sure I'd call it clean.

Questions Asked (1)

Given three PostgreSQL tables for user profiles, events, and identity merges, write a single SQL query using only CTEs and window functions to compute monthly cohort retention for cohorts from January through March 2025, covering months k=0 through k=3. The query must consolidate merged user identities (treating all child events as belonging to the parent), filter to eligible US non-employee non-test users, define activity as having at least one app_open or purchase event in a calendar month, and apply a retention rule where a user is retained in month k only if the gap between their first qualifying event that month and their previous non-refund event is 35 days or fewer. Additionally, once a user exceeds a 60-day gap, they should not be counted as retained in any subsequent month.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures

Author's notes

I stared at this for a solid minute before writing anything.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and business rules, then break the problem into logical CTEs: identity resolution, user eligibility, activity aggregation, and retention calculation. Use window functions to compute first activity, previous activity, and gap-based retention flags, ensuring the 60-day rule is applied cumulatively. Finally, pivot the results into a cohort retention matrix.

Pro tip: Explicitly state your assumptions about the merge logic (e.g., parent-child relationships, cycles) and the retention rule (e.g., gap measured from previous non-refund event, not previous activity). This shows you think about edge cases and data quality, which is crucial for a production-grade query.

1. Clarify schema and business rules

Ask about table structures, merge semantics (e.g., parent-child direction, cycles), and definitions of eligible users, activity, and refund events. Confirm the retention rule details: gap threshold, reference event, and cumulative 60-day rule.

2. Resolve identities and filter users

Use recursive CTEs to map all child identities to their ultimate parent, then join to user profiles to filter for US, non-employee, non-test users. Ensure events from merged identities are attributed to the parent.

3. Aggregate monthly activity and compute gaps

For each user and month, determine if they had a qualifying event (app_open or purchase). Use window functions to find the first qualifying event per month and the previous non-refund event, then compute the gap in days.

4. Apply retention logic and cumulative 60-day rule

Flag a user as retained in month k if the gap between their first qualifying event that month and their previous non-refund event is ≤35 days. Use a running window to track if a 60-day gap has occurred; if so, mark all subsequent months as not retained.

5. Pivot to cohort retention matrix

For cohorts Jan-Mar 2025, compute retention for k=0 to k=3 by joining the cohort definition to the retention flags. Use conditional aggregation to pivot months into columns.

Key Points to Mention

Recursive CTE for identity resolution, handling potential cycles and multiple levels of merges.
Window functions: FIRST_VALUE, LAG, and running MAX to compute first activity, previous event, and cumulative gap flag.
Definition of activity: at least one app_open or purchase event in a calendar month, and exclusion of refund events for gap calculation.
Eligibility filters: US location, non-employee, non-test users, applied after identity resolution.
Retention rule: gap ≤35 days from previous non-refund event, and cumulative 60-day rule that permanently disqualifies a user after a large gap.
Cohort assignment: based on first qualifying activity month, restricted to Jan-Mar 2025, and retention measured for k=0 to k=3.

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