Coding questions · Coinbase
Calculate Adoption and Transaction Rates, Identify Cross-Region Sales
This question evaluates data manipulation and analytics skills in SQL/Python, focusing on aggregation, event-time calculations, and identifying cross-region transaction behavior to compute adoption and transaction rates.
user_txn
+---------+-------------+------------+---------------+------------------+---------------------+
| user_id | user_region | adopted_at | transacted_at | transacted_region | timestamp |
+---------+-------------+------------+---------------+------------------+---------------------+
| 101 | US | 2023-01-05 | 2023-01-10 | US | 2023-01-10 09:00 |
| 101 | US | 2023-01-05 | 2023-02-01 | CA | 2023-02-01 12:30 |
| 202 | CA | 2023-01-07 | 2023-01-20 | CA | 2023-01-20 14:00 |
| 303 | UK | 2023-01-08 | NULL | NULL | NULL |
| 404 | US | 2023-01-09 | 2023-03-01 | UK | 2023-03-01 16:45 |
+---------+-------------+------------+---------------+------------------+---------------------+
Scenario
A user_txn table records user adoption dates and subsequent transactions across regions; the business wants adoption/transaction KPIs and insights on cross-region behavior.
Question
Compute overall adoption_rate (users with adopted_at) and transaction_rate (users with at least one transaction) for a given date range. For each adopted user, calculate the time in days from adoption to their first transaction. Identify cross-region sales: transactions where transacted_region differs from the region of the user’s first transaction, and list those transactions.
Hints
Use conditional aggregation for rates, MIN() OVER or subqueries for first transaction, and compare regions in a CTE.
Adoption and transaction KPIs with cross-region behavior
Using the user_txn table for the date range 2023-01-01 to 2023-03-31, return one combined result set with columns result_set, start_date, end_date, base_users, adopted_users, users_with_txn, adoption_rate, transaction_rate, user_id, user_region, adopted_at, first_txn_ts, first_txn_region, days_to_first_txn, transacted_at, transacted_region, and txn_ts.
- result_set = 'metrics': one KPI row with base_users, adopted_users, users_with_txn, adoption_rate, and transaction_rate.
- result_set = 'user_first_txn': one row per user whose adopted_at is in the date range, including first transaction timestamp/region and days_to_first_txn. Users with no transaction should have NULL first-transaction fields.
- result_set = 'cross_region_txns': transactions in the date range where transacted_region differs from that user's first transaction region.
Fields that do not apply to a row should be NULL.
Table user_txn
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | NOT NULL |
| user_region | VARCHAR(2) | NOT NULL |
| adopted_at | DATE | NOT NULL |
| transacted_at | DATE | |
| transacted_region | VARCHAR(2) | |
| timestamp | TIMESTAMP |
Sample data:
| adopted_at | timestamp | transacted_at | transacted_region | user_id | user_region |
|---|---|---|---|---|---|
| 2023-01-05 | 2023-01-10 09:00 | 2023-01-10 | US | 101 | US |
| 2023-01-05 | 2023-02-01 12:30 | 2023-02-01 | CA | 101 | US |
| 2023-01-07 | 2023-01-20 14:00 | 2023-01-20 | CA | 202 | CA |
| 2023-01-08 | NULL | NULL | NULL | 303 | UK |
| 2023-01-09 | 2023-03-01 16:45 | 2023-03-01 | UK | 404 | US |
Expected output
One combined result set for KPI metrics, per-user first-transaction details, and cross-region transactions in the 2023-01-01 to 2023-03-31 window.
| adopted_at | adopted_users | adoption_rate | base_users | days_to_first_txn | end_date | first_txn_region | first_txn_ts | result_set | start_date | transacted_at | transacted_region | transaction_rate | txn_ts | user_id | user_region | users_with_txn |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| NULL | 4 | 1 | 4 | NULL | 2023-03-31 | NULL | NULL | metrics | 2023-01-01 | NULL | NULL | 0.75 | NULL | NULL | NULL | 3 |
| 2023-01-05 | NULL | NULL | NULL | 5 | NULL | US | 2023-01-10 09:00 | user_first_txn | NULL | NULL | NULL | NULL | NULL | 101 | US | NULL |
| 2023-01-07 | NULL | NULL | NULL | 13 | NULL | CA | 2023-01-20 14:00 | user_first_txn | NULL | NULL | NULL | NULL | NULL | 202 | CA | NULL |
| 2023-01-08 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | user_first_txn | NULL | NULL | NULL | NULL | NULL | 303 | UK | NULL |
| 2023-01-09 | NULL | NULL | NULL | 51 | NULL | UK | 2023-03-01 16:45 | user_first_txn | NULL | NULL | NULL | NULL | NULL | 404 | US | NULL |
| 2023-01-05 | NULL | NULL | NULL | NULL | NULL | US | 2023-01-10 09:00 | cross_region_txns | NULL | 2023-02-01 | CA | NULL | 2023-02-01 12:30 | 101 | US | NULL |
Hints
- Build the shared adoption and first-transaction CTEs once.
- Use the first transaction region as the baseline for cross-region detection.
- Use typed NULLs when combining different result shapes.