Analyze Acquisition Channels for User Value and Retention
Company: Chime
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
acquisition
| user_id | acquire_channel | acquire_date |
| 101 | organic | 2023-01-05 |
| 102 | paid_search | 2023-01-06 |
| 103 | social_media | 2023-01-08 |
transactions
| transaction_id | user_id | amount | transaction_date |
| 9001 | 101 | 45.50 | 2023-01-10 |
| 9002 | 101 | 19.00 | 2023-02-05 |
| 9003 | 102 | 60.00 | 2023-02-12 |
| 9004 | 103 | 30.00 | 2023-03-01 |
##### Scenario
An e-commerce platform provided two tables: one logging the channel and date each user was acquired, another logging every purchase. Product managers want actionable insights on acquisition effectiveness and user value.
##### Question
Write a query that returns each acquire_channel together with the count of distinct users acquired from it. 2. For every acquire_channel, rank users by their cumulative spend and output the top three spenders per channel with their totals. 3. Build a monthly cohort table that shows, for each acquisition month, the percentage of users who make at least one purchase in any subsequent month (retention). 4. Within 90 days of each user’s acquire_date, compute average revenue per user (ARPU) by channel and identify the channel with the highest ARPU.
##### Hints
Expect multiple CTEs, DATE_DIFF/DATE_TRUNC, JOINs, window functions such as SUM() OVER and RANK().
Overview: This question evaluates proficiency in SQL/Python data manipulation and analytics, testing aggregation, cohort and retention analysis, ranking, joins and window-function usage along with interpretation of user-level revenue metrics.
Users per Acquisition Channel
For each acquire_channel, count distinct acquired users.
Tables
acquisition(user_id INTEGER, acquire_channel VARCHAR(50), acquire_date DATE)
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), transaction_date DATE)
Hints
- Use COUNT(DISTINCT user_id) grouped by channel
- Group by acquire_channel to aggregate
Top Spenders per Channel
For each acquisition channel, rank users by their total cumulative spend and return the top three spenders per channel.
Tables
acquisition(user_id INTEGER, acquire_channel VARCHAR(50), acquire_date DATE)
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), transaction_date DATE)
Hints
- Aggregate SUM(amount) per user per channel
- Use RANK() partitioned by acquire_channel ordered by total_spend DESC
Monthly Cohort Retention
## Monthly Cohort Retention
You are given two tables:
- **`acquisition`** — one row per acquired user: `user_id`, `acquire_channel`, `acquire_date`.
- **`transactions`** — one row per purchase: `transaction_id`, `user_id`, `amount`, `transaction_date`.
Build a **monthly cohort-retention table**. Group users into cohorts by their **acquisition month** (the calendar month of `acquire_date`). For each cohort and each positive **month offset** (1 = the month after acquisition, 2 = two months after, and so on), report how many users from that cohort were *retained* in that offset month.
A user is **retained at offset _k_** (for `k >= 1`) if they made **at least one purchase** in the calendar month that is exactly _k_ months after their acquisition month. (A purchase in the acquisition month itself is offset 0 and is **not** counted as retention.) Compute the month offset from the difference between the transaction's month and the acquisition month, not from raw day counts.
### Required output
Return one row per `(acquire_month, month_offset)` pair that has at least one retained user, with these columns:
- `acquire_month` — the first day of the cohort's acquisition month, formatted as `YYYY-MM-DD` text.
- `month_offset` — the integer month offset (`>= 1`).
- `retained_users` — the number of distinct users from the cohort retained at that offset.
- `cohort_size` — the total number of users acquired in that cohort month.
- `retention_pct` — `retained_users / cohort_size`, rounded to **4 decimal places**.
Sort the result by `acquire_month` ascending, then `month_offset` ascending.
Tables
acquisition(user_id INTEGER, acquire_channel VARCHAR(50), acquire_date DATE)
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), transaction_date DATE)
Hints
- Use DATE_TRUNC('month', col) to bucket both acquisition and transaction dates into months.
- In PostgreSQL there is no DATE_DIFF in months — derive the offset as (year_diff * 12 + month_diff) using EXTRACT(YEAR ...) and EXTRACT(MONTH ...).
90-Day ARPU by Channel
## 90-Day ARPU by Acquisition Channel
You are given two tables that describe how users were acquired and the revenue they generated.
- **`acquisition`** — one row per user: `user_id`, `acquire_channel` (e.g. `organic`, `paid_search`, `social_media`), and `acquire_date` (the date the user was acquired).
- **`transactions`** — one row per transaction: `transaction_id`, `user_id`, `amount`, and `transaction_date`.
For each acquisition channel, compute the **90-day ARPU** (Average Revenue Per User). A user's 90-day revenue is the sum of `amount` for that user's transactions whose `transaction_date` falls **on or after** the user's `acquire_date` and **on or before** `acquire_date + 90 days` (an inclusive 90-day window). Users with no qualifying transactions count as 0 revenue but must still be counted in the channel's user total.
Return one row per channel with the following columns:
- `acquire_channel` — the channel name.
- `users` — the number of users acquired through that channel.
- `revenue_90d` — the total 90-day revenue across that channel's users.
- `arpu_90d` — `revenue_90d / users`, rounded to 2 decimal places.
- `arpu_rank` — the channel's rank by `arpu_90d` descending (rank 1 = highest ARPU). Break ties alphabetically by `acquire_channel` (so tied channels receive distinct, deterministic ranks).
Order the result by `arpu_rank` ascending, then by `acquire_channel` ascending.
Tables
acquisition(user_id INTEGER, acquire_channel VARCHAR(50), acquire_date DATE)
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), transaction_date DATE)
Hints
- Left-join acquisition to transactions so users with zero qualifying transactions still appear, then sum amounts only inside the 90-day window with a CASE.
- In PostgreSQL, add 90 days with `acquire_date + INTERVAL '90 days'` (not DATE_ADD); guard the ARPU division with NULLIF(count, 0).