Compute monthly CRR with merges and gaps

Q: Compute monthly CRR with merges and gaps

This is a Data Manipulation (SQL/Python) interview question from Google for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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 | +---------------+----------------+---------------------+

Compute monthly CRR with merges and gaps

Comments (0)