Coding questions · Coinbase
Write SQL for 30-day activity and D7 retention
This question evaluates a candidate's skill in SQL-based data manipulation and product-analytics competencies—specifically aggregation, deduplication to avoid double-counting, date arithmetic for time-windowed queries, cohort retention calculation, and percentage metrics—in the Data Manipulation (SQL/Python) domain.
You are given two tables.
Table: trades
user_id(BIGINT)trade_time(TIMESTAMP) — timestamp of the tradevolume(NUMERIC)asset(TEXT)
Table: users
user_id(BIGINT, PK)signup_date(DATE)
Assumptions:
trade_timeis stored in UTC.- “Last 30 days” is relative to
CURRENT_DATE(orCURRENT_TIMESTAMP) in UTC. - When counting users, avoid double-counting a user who has multiple trades.
Question 1 — 30-day trading intensity
Compute the percentage of active traders in the last 30 days who made at least 3 trades in the last 30 days.
- Define the denominator as: users with ≥ 1 trade in the last 30 days.
Required output (single row):
active_users_30dusers_with_3plus_trades_30dpct_users_with_3plus_trades_30d
Question 2 — 7-day retention (trading-based)
Compute D7 retention by signup cohort where a user is considered retained on D7 if they made ≥ 1 trade exactly on calendar day signup_date + 7.
- Only include cohorts where
signup_date <= CURRENT_DATE - 7(so D7 is observable).
Required output (one row per signup_date):
signup_datecohort_sizeretained_users_d7d7_retention_rate
30-day heavy-trader share (>=3 trades)
You are given a table of crypto trades: trades(user_id, trade_time, volume, asset).
Define the 30-day window as FROM 2025-05-03 TO 2025-06-01 (inclusive, by calendar date).
An "active user" is any user who made at least 1 trade in this window.
Write a SQL query to compute the proportion of active users who made at least 3 trades in this window.
Return a single row with:
- active_users
- users_with_3plus_trades
- proportion_3plus (users_with_3plus_trades / active_users as a decimal)
Assume PostgreSQL syntax is acceptable.
Table trades
| column | type | constraints |
|---|---|---|
| trade_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| trade_time | TIMESTAMP | NOT NULL |
| volume | DECIMAL(18,2) | NOT NULL |
| asset | VARCHAR(10) | NOT NULL |
Sample data:
| asset | trade_id | trade_time | user_id | volume |
|---|---|---|---|---|
| BTC | 1 | 2025-04-30 10:15:00 | 1 | 1000 |
| ETH | 2 | 2025-05-05 09:00:00 | 1 | 200 |
| BTC | 3 | 2025-05-10 12:30:00 | 1 | 150 |
| SOL | 4 | 2025-05-20 16:45:00 | 1 | 300 |
| BTC | 5 | 2025-05-04 11:10:00 | 2 | 50 |
| ETH | 6 | 2025-05-18 08:00:00 | 2 | 70 |
| BTC | 7 | 2025-05-03 00:05:00 | 3 | 20 |
| BTC | 8 | 2025-05-03 14:20:00 | 3 | 25 |
| ETH | 9 | 2025-06-01 23:59:00 | 3 | 10 |
| BTC | 10 | 2025-05-15 13:00:00 | 4 | 5 |
| ETH | 11 | 2025-06-02 09:00:00 | 5 | 100 |
| BTC | 12 | 2025-05-25 10:00:00 | 6 | 40 |
| BTC | 13 | 2025-05-26 10:00:00 | 6 | 45 |
| ETH | 14 | 2025-05-27 10:00:00 | 6 | 55 |
| SOL | 15 | 2025-05-28 10:00:00 | 6 | 60 |
Expected output
One row showing how many users traded at least once in the window, how many had >=3 trades in the window, and the resulting share.
| active_users | proportion_3plus | users_with_3plus_trades |
|---|---|---|
| 5 | 0.6 | 3 |
Hints
- First filter trades to the date window, then group by user_id to count trades.
- Define the denominator as the number of distinct users who traded at least once in the window.
D7 retention based on trading on day 7 after signup
You are given:
- users(user_id, signup_date)
- trades(user_id, trade_time, volume, asset)
Define D7 retention as: A user is "retained" if they make at least one trade on the calendar date equal to (signup_date + 7 days).
Compute the D7 retention rate for the signup cohort FROM 2025-05-01 TO 2025-05-25 (inclusive).
Return a single row with:
- cohort_start_date
- cohort_end_date
- total_users
- retained_users_d7
- d7_retention_rate
Assume PostgreSQL syntax is acceptable.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-05-01 | 1 |
| 2025-05-02 | 2 |
| 2025-05-10 | 3 |
| 2025-05-08 | 4 |
| 2025-05-20 | 5 |
| 2025-05-18 | 6 |
Table trades
| column | type | constraints |
|---|---|---|
| trade_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| trade_time | TIMESTAMP | NOT NULL |
| volume | DECIMAL(18,2) | NOT NULL |
| asset | VARCHAR(10) | NOT NULL |
Sample data:
| asset | trade_id | trade_time | user_id | volume |
|---|---|---|---|---|
| BTC | 1 | 2025-04-30 10:15:00 | 1 | 1000 |
| ETH | 2 | 2025-05-05 09:00:00 | 1 | 200 |
| BTC | 3 | 2025-05-10 12:30:00 | 1 | 150 |
| SOL | 4 | 2025-05-20 16:45:00 | 1 | 300 |
| BTC | 5 | 2025-05-04 11:10:00 | 2 | 50 |
| ETH | 6 | 2025-05-18 08:00:00 | 2 | 70 |
| BTC | 7 | 2025-05-03 00:05:00 | 3 | 20 |
| BTC | 8 | 2025-05-03 14:20:00 | 3 | 25 |
| ETH | 9 | 2025-06-01 23:59:00 | 3 | 10 |
| BTC | 10 | 2025-05-15 13:00:00 | 4 | 5 |
| ETH | 11 | 2025-06-02 09:00:00 | 5 | 100 |
| BTC | 12 | 2025-05-25 10:00:00 | 6 | 40 |
| BTC | 13 | 2025-05-26 10:00:00 | 6 | 45 |
| ETH | 14 | 2025-05-27 10:00:00 | 6 | 55 |
| SOL | 15 | 2025-05-28 10:00:00 | 6 | 60 |
Expected output
One row for the cohort (2025-05-01 to 2025-05-25) giving total users, how many traded exactly on signup_date + 7 days, and the retention rate.
| cohort_end_date | cohort_start_date | d7_retention_rate | retained_users_d7 | total_users |
|---|---|---|---|---|
| 2025-05-25 | 2025-05-01 | 0.3333333333 | 2 | 6 |
Hints
- Be explicit about the D7 definition (trade date equals signup_date + 7 days).
- A LEFT JOIN on the D7 date (or an EXISTS subquery) helps you flag retained users, then aggregate.