Calculate Adoption and Transaction Rates, Identify Cross-Region Sales
Company: Coinbase
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
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.
1. result_set = 'metrics': one KPI row with base_users, adopted_users, users_with_txn, adoption_rate, and transaction_rate.
2. 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.
3. 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.
Tables
user_txn(user_id INTEGER, user_region VARCHAR(2), adopted_at DATE, transacted_at DATE, transacted_region VARCHAR(2), timestamp TIMESTAMP)
Hints
- Build the shared adoption and first-transaction CTEs once.
- Use the first transaction region as the baseline for cross-region detection.