Coding questions · Coinbase
Compute adoption, latency, and cross-region transactions
This question evaluates proficiency in SQL-based data manipulation and analytics, including calculating cohort adoption and transaction rates, performing date arithmetic for time-to-first-transaction, identifying cross-region transactions, and computing distributional percentiles.
Invented schema:
users(user_id INT PRIMARY KEY, user_region STRING, adopted_at TIMESTAMP NULL)
transactions(txn_id INT PRIMARY KEY, user_id INT, txn_ts TIMESTAMP, txn_region STRING)
Sample rows:
users
user_id | user_region | adopted_at
1 | US | 2025-07-15 10:00:00
2 | US | null
3 | EU | 2025-08-02 09:12:00
4 | APAC | 2025-08-20 18:30:00
5 | EU | 2025-08-25 12:00:00
6 | US | 2025-08-30 23:59:00
transactions
txn_id | user_id | txn_ts | txn_region
101 | 1 | 2025-08-01 08:00:00 | US
102 | 1 | 2025-08-16 10:00:00 | CA
103 | 3 | 2025-08-10 12:10:00 | EU
104 | 4 | 2025-08-25 07:45:00 | APAC
105 | 4 | 2025-08-26 07:45:00 | US
106 | 5 | 2025-08-30 07:45:00 | EU
107 | 6 | 2025-09-01 00:10:00 | US
Write ANSI SQL for the following: A) Adoption_rate and transaction_rate per user_region for the month 2025-08-01 to 2025-08-31 (inclusive). Definitions: adoption_rate = users with adopted_at in August 2025 divided by total users in that region; transaction_rate = distinct users in that region having ≥1 transaction with txn_ts in August 2025 divided by total users in that region. Return region, adoption_rate, transaction_rate. B) For each user, compute days_to_first_txn = DATE_DIFF(first transaction timestamp, adopted_at) in days. Only include users with non-null adopted_at and first transaction timestamp ≥ adopted_at. Return user_id and days_to_first_txn, and also a separate query that returns the p10, p50, p90 of days_to_first_txn. C) Define a cross-region sale as any transaction where txn_region ≠ the user’s first_txn_region (the region of the earliest transaction in the user’s lifetime, not limited to August). Return all transactions with txn_ts in 2025-08-01..2025-08-31 flagged with is_cross_region plus columns: txn_id, user_id, txn_ts, txn_region, first_txn_region.
Adoption rate and transaction rate per region (August 2025)
You are given two tables: users and transactions.
For the month 2025-08-01 to 2025-08-31 (inclusive), compute the following per users.user_region:
- adoption_rate = (# users whose adopted_at is in August 2025) / (total # users in that region)
- transaction_rate = (# distinct users in that region with >= 1 transaction whose txn_ts is in August 2025) / (total # users in that region)
Return: region, adoption_rate, transaction_rate. Use decimal rates (not percentages).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_region | VARCHAR(10) | NOT NULL |
| adopted_at | TIMESTAMP |
Sample data:
| adopted_at | user_id | user_region |
|---|---|---|
| 2025-07-15 10:00:00 | 1 | US |
| NULL | 2 | US |
| 2025-08-02 09:12:00 | 3 | EU |
| 2025-08-20 18:30:00 | 4 | APAC |
| 2025-08-25 12:00:00 | 5 | EU |
| 2025-08-30 23:59:00 | 6 | US |
Table transactions
| column | type | constraints |
|---|---|---|
| txn_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| txn_ts | TIMESTAMP | NOT NULL |
| txn_region | VARCHAR(10) | NOT NULL |
Sample data:
| txn_id | txn_region | txn_ts | user_id |
|---|---|---|---|
| 101 | US | 2025-08-01 08:00:00 | 1 |
| 102 | CA | 2025-08-16 10:00:00 | 1 |
| 103 | EU | 2025-08-10 12:10:00 | 3 |
| 104 | APAC | 2025-08-25 07:45:00 | 4 |
| 105 | US | 2025-08-26 07:45:00 | 4 |
| 106 | EU | 2025-08-30 07:45:00 | 5 |
| 107 | US | 2025-09-01 00:10:00 | 6 |
Expected output
One row per user_region with adoption_rate and transaction_rate for 2025-08-01..2025-08-31 (inclusive).
| adoption_rate | region | transaction_rate |
|---|---|---|
| 1 | APAC | 1 |
| 1 | EU | 1 |
| 0.3333 | US | 0.3333 |
Hints
- Compute denominators (total users per region) once, then LEFT JOIN numerator aggregates.
- Use an inclusive August filter by using [>= 2025-08-01) and (< 2025-09-01).
Days from adoption to first transaction + p10/p50/p90
Using users and transactions, compute days from adoption to each user's first transaction and the p10, p50, and p90 percentiles of those day counts. Return one combined PostgreSQL result set with columns result_set, user_id, days_to_first_txn, p10, p50, and p90. Use result_set = 'user_days' for per-user rows and result_set = 'percentiles' for the aggregate percentile row; columns that do not apply should be NULL. Only include users whose first transaction timestamp is on or after adopted_at.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_region | VARCHAR(10) | NOT NULL |
| adopted_at | TIMESTAMP |
Sample data:
| adopted_at | user_id | user_region |
|---|---|---|
| 2025-07-15 10:00:00 | 1 | US |
| NULL | 2 | US |
| 2025-08-02 09:12:00 | 3 | EU |
| 2025-08-20 18:30:00 | 4 | APAC |
| 2025-08-25 12:00:00 | 5 | EU |
| 2025-08-30 23:59:00 | 6 | US |
Table transactions
| column | type | constraints |
|---|---|---|
| txn_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| txn_ts | TIMESTAMP | NOT NULL |
| txn_region | VARCHAR(10) | NOT NULL |
Sample data:
| txn_id | txn_region | txn_ts | user_id |
|---|---|---|---|
| 101 | US | 2025-08-01 08:00:00 | 1 |
| 102 | CA | 2025-08-16 10:00:00 | 1 |
| 103 | EU | 2025-08-10 12:10:00 | 3 |
| 104 | APAC | 2025-08-25 07:45:00 | 4 |
| 105 | US | 2025-08-26 07:45:00 | 4 |
| 106 | EU | 2025-08-30 07:45:00 | 5 |
| 107 | US | 2025-09-01 00:10:00 | 6 |
Expected output
Per-user days to first transaction plus one percentile summary row.
| days_to_first_txn | p10 | p50 | p90 | result_set | user_id |
|---|---|---|---|---|---|
| 16 | NULL | NULL | NULL | user_days | 1 |
| 8 | NULL | NULL | NULL | user_days | 3 |
| 4 | NULL | NULL | NULL | user_days | 4 |
| 4 | NULL | NULL | NULL | user_days | 5 |
| 1 | NULL | NULL | NULL | user_days | 6 |
| NULL | 2.2 | 4 | 12.8 | percentiles | NULL |
Hints
- Compute first transaction per user in a CTE.
- Use
percentile_contover the per-user day differences. - Use NULLs for columns that do not apply to the row type.
Flag cross-region transactions in August 2025
You are given two tables: users and transactions.
Define a user's first_txn_region as the txn_region of their earliest transaction in their lifetime (earliest txn_ts), not limited to August.
Define a cross-region sale as any transaction where txn_region != first_txn_region for that user.
Return all transactions with txn_ts in 2025-08-01 to 2025-08-31 (inclusive), with columns:
- txn_id, user_id, txn_ts formatted as
YYYY-MM-DD HH24:MI:SS, txn_region, first_txn_region, is_cross_region (1 if cross-region else 0).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_region | VARCHAR(10) | NOT NULL |
| adopted_at | TIMESTAMP |
Sample data:
| adopted_at | user_id | user_region |
|---|---|---|
| 2025-07-15 10:00:00 | 1 | US |
| NULL | 2 | US |
| 2025-08-02 09:12:00 | 3 | EU |
| 2025-08-20 18:30:00 | 4 | APAC |
| 2025-08-25 12:00:00 | 5 | EU |
| 2025-08-30 23:59:00 | 6 | US |
Table transactions
| column | type | constraints |
|---|---|---|
| txn_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| txn_ts | TIMESTAMP | NOT NULL |
| txn_region | VARCHAR(10) | NOT NULL |
Sample data:
| txn_id | txn_region | txn_ts | user_id |
|---|---|---|---|
| 101 | US | 2025-08-01 08:00:00 | 1 |
| 102 | CA | 2025-08-16 10:00:00 | 1 |
| 103 | EU | 2025-08-10 12:10:00 | 3 |
| 104 | APAC | 2025-08-25 07:45:00 | 4 |
| 105 | US | 2025-08-26 07:45:00 | 4 |
| 106 | EU | 2025-08-30 07:45:00 | 5 |
| 107 | US | 2025-09-01 00:10:00 | 6 |
Expected output
All August 2025 transactions with the user's first_txn_region and a 1/0 flag indicating whether the transaction region differs from that first region.
| first_txn_region | is_cross_region | txn_id | txn_region | txn_ts | user_id |
|---|---|---|---|---|---|
| US | 0 | 101 | US | 2025-08-01 08:00:00 | 1 |
| US | 1 | 102 | CA | 2025-08-16 10:00:00 | 1 |
| EU | 0 | 103 | EU | 2025-08-10 12:10:00 | 3 |
| APAC | 0 | 104 | APAC | 2025-08-25 07:45:00 | 4 |
| APAC | 1 | 105 | US | 2025-08-26 07:45:00 | 4 |
| EU | 0 | 106 | EU | 2025-08-30 07:45:00 | 5 |
Hints
- Compute the first transaction region over all transactions before applying the August filter.
- Use
ROW_NUMBERordered bytxn_ts, txn_idto make the first-region tie-break deterministic. - Use
TO_CHARto formattxn_tsfor PostgreSQL output.