Compute early signals for user retention
Company: Chime
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are given two tables that track signups and user transactions.
## Tables
### `users`
- `user_id` (STRING, **PK**) — unique user
- `signup_ts` (TIMESTAMP) — time the user signed up (assume UTC)
### `transactions`
- `transaction_id` (STRING, **PK**)
- `user_id` (STRING, **FK → users.user_id**)
- `transaction_ts` (TIMESTAMP) — time of transaction (UTC)
- `amount` (NUMERIC) — transaction amount
## Task
You want an **early signal** that predicts **Day-30 retention**.
1) Define an **early engagement metric** as the number of transactions a user makes in the first **7 days after signup**:
- `early_txn_cnt` = count of `transactions` where `transaction_ts >= signup_ts` and `transaction_ts < signup_ts + 7 days`.
2) Define **Day-30 retention** as whether the user has **at least one transaction** in the window **[signup + 30 days, signup + 37 days)**.
- `is_retained_d30` ∈ {0,1}
3) Write a SQL query that outputs retention performance by early-engagement bucket:
- Bucket `early_txn_cnt` into: `0`, `1`, `2`, `3+`
## Required output
Return one row per bucket with:
- `early_txn_bucket`
- `users_in_bucket`
- `retained_users`
- `retention_rate` (= `retained_users` / `users_in_bucket`, as a decimal)
## Notes / assumptions
- Include users with zero transactions.
- If a user has transactions before `signup_ts`, ignore them.
- If multiple transactions occur in the retention window, the user still counts as retained once.
Overview: This question evaluates the ability to compute cohort-based early engagement metrics and Day-30 retention using time-windowed joins, aggregation, bucketing, and handling edge cases such as users with zero or pre-signup transactions.
Read the full Chime Data Scientist interview experience this question came from
You are given two tables: users and transactions.
Define an "early signal" of user engagement as the number of DISTINCT days a user made at least one transaction during their first 7 days after signup (inclusive): signup_date through signup_date + 6 days.
Define a user as "retained" if they made at least one transaction during days 8–30 after signup (inclusive): signup_date + 7 days through signup_date + 29 days.
Assume the "current date" is 2025-06-01. To ensure a full 30-day observation window, only include users with signup_date <= 2025-05-02.
Task:
1) Compute each included user's early distinct transaction days (0, 1, 2, ...).
2) Bucket users into:
- '0' for 0 early active days
- '1' for 1 early active day
- '2+' for 2 or more early active days
3) For each bucket, output:
- early_active_days_7d_bucket
- users (number of users in the bucket)
- retained_users (number of retained users)
- retention_rate (retained_users / users as a decimal)
Return one row per bucket.
Tables
users(user_id INT, signup_date DATE, country VARCHAR(2))
transactions(txn_id INT, user_id INT, txn_ts TIMESTAMP, amount DECIMAL(10,2))
Hints
- Use conditional aggregation to count distinct transaction dates in the first 7 days after signup.
- Compute retention as an existence check (MAX of a 0/1 flag) over the day 8–30 window.
Community answers
Answer by SS
WITH early_engagement AS (
-- Step 1: Count transactions in first 7 days per user
SELECT
u.user_id,
COUNT(t.transaction_id) AS early_txn_cnt
FROM users u
LEFT JOIN transactions t
ON u.user_id = t.user_id
AND t.transaction_ts >= u.signup_ts
AND t.transaction_ts < u.signup_ts + INTERVAL '7 days'
GROUP BY u.user_id
),
day30_retention AS (
-- Step 2: Flag if user has at least one txn in day 30-37 window
SELECT
u.user_id,
MAX(CASE
WHEN t.transaction_ts >= u.signup_ts + INTERVAL '30 days'
AND t.transaction_ts < u.signup_ts + INTERVAL '37 days'
THEN 1 ELSE 0
END) AS is_retained_d30
FROM users u
LEFT JOIN transactions t
ON u.user_id = t.user_id
GROUP BY u.user_id
),
combined AS (
-- Step 3: Join both together
SELECT
e.user_id,
e.early_txn_cnt,
d.is_retained_d30,
-- Step 4: Bucket early_txn_cnt
CASE
WHEN e.early_txn_cnt = 0 THEN '0'
WHEN e.early_txn_cnt = 1 THEN '1'
WHEN e.early_txn_cnt = 2 THEN '2'
ELSE '3+'
END AS early_txn_bucket
FROM early_engagement e
JOIN day30_retention d
ON e.user_id = d.user_id
)
-- Step 5: Aggregate by bucket
SELECT
early_txn_bucket,
COUNT(user_id) AS users_in_bucket,
SUM(is_retained_d30) AS retained_users,
ROUND(SUM(is_retained_d30) * 1.0 / COUNT(user_id), 4) AS retention_rate
FROM combined
GROUP BY early_txn_bucket
ORDER BY early_txn_bucket;
Answer by Code4Ever
select * from