Compute monthly CRR with merges and gaps
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given PostgreSQL tables user_profile(user_id, signup_ts, country, is_employee, is_test), user_events(user_id, event_ts, event_type, revenue, platform), and user_merges(child_user_id, parent_user_id, merged_ts). Using only CTEs and window functions, write a single SQL query that returns monthly cohort retention for cohorts Jan–Mar 2025 and months k=0..3 with columns: cohort_month (YYYY-MM-01), month_number (k), users_in_cohort, retained_users, crr = retained_users::decimal/users_in_cohort. Rules and edge cases: 1) Consolidate identities: if a child_user_id merges into a parent_user_id, treat ALL of the child’s events (before and after merged_ts) as belonging to the parent. 2) Eligible cohort users are those with country = 'US', is_employee = false, is_test = false; cohort is date_trunc('month', signup_ts) (UTC). 3) A user is ACTIVE in a calendar month if they have ≥1 event with event_type IN ('app_open','purchase') in that month; months containing only 'refund' events do not count as active. 4) A user is RETAINED in cohort month k if they are active in that month AND the gap between their first active event in that month and their previous non-refund event (on the consolidated identity’s timeline) is ≤ 35 days; compute gaps using LAG() ordered by event_ts. 5) After the first month where the gap from the prior non-refund event exceeds 60 days, do not count the user as retained in subsequent months. 6) Treat timestamps as UTC; do not use temp tables. Include LAG() at least once. Output sorted by cohort_month, month_number. Small sample data:
user_profile
+---------+---------------------+---------+-------------+---------+
| user_id | signup_ts | country | is_employee | is_test |
+---------+---------------------+---------+-------------+---------+
| 1 | 2025-01-10 09:00:00 | US | f | f |
| 2 | 2025-01-20 12:00:00 | US | f | f |
| 3 | 2025-02-02 08:00:00 | US | f | f |
| 4 | 2025-02-15 10:00:00 | US | t | f |
| 5 | 2025-02-20 11:00:00 | GB | f | t |
+---------+---------------------+---------+-------------+---------+
user_events
+---------+---------------------+------------+---------+----------+
| user_id | event_ts | event_type | revenue | platform |
+---------+---------------------+------------+---------+----------+
| 1 | 2025-01-10 10:00:00 | app_open | 0.00 | ios |
| 1 | 2025-02-05 09:00:00 | purchase | 9.99 | ios |
| 1 | 2025-02-06 12:00:00 | refund | -9.99 | ios |
| 1 | 2025-03-20 08:00:00 | app_open | 0.00 | ios |
| 2 | 2025-01-21 12:00:00 | app_open | 0.00 | android |
| 2 | 2025-03-01 10:00:00 | app_open | 0.00 | android |
| 3 | 2025-02-10 14:00:00 | app_open | 0.00 | web |
| 3 | 2025-03-14 16:00:00 | app_open | 0.00 | web |
| 3 | 2025-05-20 09:00:00 | app_open | 0.00 | web |
| 4 | 2025-02-16 13:00:00 | app_open | 0.00 | ios |
| 5 | 2025-02-22 09:30:00 | app_open | 0.00 | android |
+---------+---------------------+------------+---------+----------+
user_merges
+---------------+----------------+---------------------+
| child_user_id | parent_user_id | merged_ts |
+---------------+----------------+---------------------+
| 2 | 1 | 2025-03-02 00:00:00 |
+---------------+----------------+---------------------+
Overview: This question evaluates advanced SQL data manipulation and cohort retention analysis skills, including identity consolidation via merge tables, time-series event classification, and use of CTEs and window functions (including LAG()).
Read the full Google Data Scientist interview experience this question came from
You are given three PostgreSQL tables:
- user_profile(user_id, signup_ts, country, is_employee, is_test)
- user_events(user_id, event_ts, event_type, revenue, platform)
- user_merges(child_user_id, parent_user_id, merged_ts)
Write ONE PostgreSQL SQL query (no temp tables) using ONLY CTEs and window functions that returns monthly cohort retention for cohorts Jan–Mar 2025 and month_number k = 0..3.
Output columns:
- cohort_month (DATE formatted as YYYY-MM-01)
- month_number (0..3)
- users_in_cohort
- retained_users
- crr = retained_users::decimal / users_in_cohort
Rules / edge cases:
1) Consolidate identities: if child_user_id merges into parent_user_id, treat ALL of the child’s events (before and after merged_ts) as belonging to the parent. (Assume merges can be chained; use the ultimate parent as the canonical user_id.)
2) Eligible cohort users: country = 'US', is_employee = false, is_test = false. The cohort_month is date_trunc('month', signup_ts) in UTC, computed on the canonical identity (use the earliest signup_ts among merged identities).
3) A user is ACTIVE in a calendar month if they have ≥ 1 event with event_type IN ('app_open','purchase') in that month. Months containing only 'refund' events do not count as active.
4) A user is RETAINED in a given month if they are active in that month AND the gap between their first active event in that month and their previous non-refund event (on the canonical identity’s timeline) is ≤ 35 days. Compute gaps using LAG() ordered by event_ts.
5) After the first month where that month-level gap exceeds 60 days, do not count the user as retained in subsequent months.
6) Treat all timestamps as UTC.
Return rows for all cohort_month in {2025-01-01, 2025-02-01, 2025-03-01} and month_number in {0,1,2,3}, sorted by cohort_month then month_number.
Tables
user_profile(user_id INT, signup_ts TIMESTAMP, country VARCHAR(2), is_employee BOOLEAN, is_test BOOLEAN)
user_events(user_id INT, event_ts TIMESTAMP, event_type VARCHAR(20), revenue DECIMAL(10,2), platform VARCHAR(20))
user_merges(child_user_id INT, parent_user_id INT, merged_ts TIMESTAMP)
Hints
- Use a recursive CTE to map each user_id to its ultimate parent_user_id (canonical identity), then join both profiles and events through that mapping.
- Compute previous non-refund event timestamps with LAG() on the canonical event timeline, then pick the first active event per (user, month) to measure the month-level gap.