Compute 30-day power users and 7-day retention
Company: Coinbase
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
# Compute 30-day power users and 7-day retention
You are interviewing for a Product/Risk Data Scientist role at a crypto exchange.
Assume all timestamps are in UTC.
### Tables
```sql
trades(
user_id BIGINT,
trade_time TIMESTAMP,
volume NUMERIC,
asset VARCHAR
)
users(
user_id BIGINT,
signup_date DATE
)
```
### Task 1 — 30-day “power trader” share
Define the last 30 days as `[CURRENT_DATE - INTERVAL '30 day', CURRENT_DATE)`.
Compute the **percentage of active traders** in the last 30 days who made **at least 3 trades** in that same 30-day window.
- **Active trader (denominator):** a user with **≥ 1 trade** in the last 30 days.
- **Numerator:** users with **trade_count ≥ 3** in the last 30 days.
Return one row with:
- `active_traders_30d`
- `power_traders_30d`
- `pct_power_traders_30d` (as a decimal between 0 and 1)
### Task 2 — 7-day retention (trade-based)
Define **7-day retention** as: a signed-up user makes **at least one trade on the 7th day after signup**, i.e. on the calendar date `signup_date + 7` (UTC date).
For each `signup_date` cohort, compute:
- `cohort_size`
- `retained_users_d7`
- `d7_retention_rate`
Notes:
- Include users even if they never trade.
- A user counts as retained if they have ≥1 trade whose UTC date equals `signup_date + 7`.
- Output should be grouped by `signup_date` and ordered by `signup_date` ascending.
### Clarifying Questions to Ask
- Clarify SQL dialect or Python library versions, date/time semantics, duplicate handling, and null handling.
- Define the grain of each intermediate result before aggregating.
- State expected output columns and ordering explicitly.
### What a Strong Answer Covers
- A query or pandas plan that matches the requested output grain.
- Correct joins, filters, grouping, window functions, and treatment of NULLs or duplicates.
- A brief explanation of why the result is correct and how it handles edge cases.
- Performance notes, indexes/partitioning, and validation queries when relevant.
### Follow-up Questions
- How would you test the query on a tiny hand-built dataset?
- What changes if duplicate events or late-arriving data are present?
- Which indexes, clustering, or partitions would help at production scale?
Overview: Compute 30-day power users and 7-day retention evaluates SQL or pandas logic, joins, grouping, window functions, null handling, edge cases, and validation in a realistic interview setting. A strong answer states assumptions, handles edge cases, explains trade-offs, and shows how to validate the result clearly.
Read the full Coinbase Data Scientist interview experience this question came from
Community answers
Answer by borisgersh
with trade_counts as (
select user_id
,count(*) trade_count
from trades
where trade_time < CURRENT_DATE and trade_time >= CURRENT_DATE - interval '30 days'
group by user_id
)
select count(case when trade_count>=1 then user_id end) as active_traders_30d
, count(case when trade_count>=3 then user_id end) as power_traders_30d
, count(case when trade_count>=3 then user_id end)/nullif(count(case when trade_count>=1 then user_id end),0) as pct_power_traders_30d
from trade_counts
Answer by borisgersh
Task 2:
with trades_7day as (
select user_id
,(trade_time::date - interval '7 days')::date as signup_date
,1 trade
from trades
),
user_trades as (
select distinct u.user_id,u.signup_date,t.trade
from users u
left join trades_7day t on t.user_id = u.user_id and t.signup_date = u.signup_date
)
select signup_date
,count(user_id) as cohort_size
,coalesce(sum(case when trade is not null then trade end),0)::int as retained_users_d7
,coalesce(sum(case when trade is not null then trade end)/count(user_id),0) as d7_retention_rate
from user_trades
group by 1
order by signup_date