Quick Overview

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.

Compute adoption, latency, and cross-region transactions

Company: Coinbase

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

Overview: 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.

Read the full Coinbase Data Scientist interview experience this question came from

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).

Tables

users(user_id INT, user_region VARCHAR(10), adopted_at TIMESTAMP)

transactions(txn_id INT, user_id INT, txn_ts TIMESTAMP, txn_region VARCHAR(10))

Hints

  1. Compute denominators (total users per region) once, then LEFT JOIN numerator aggregates.
  2. 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`.

Tables

users(user_id INT, user_region VARCHAR(10), adopted_at TIMESTAMP)

transactions(txn_id INT, user_id INT, txn_ts TIMESTAMP, txn_region VARCHAR(10))

Hints

  1. Compute first transaction per user in a CTE.
  2. Use `percentile_cont` over the per-user day differences.

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).

Tables

users(user_id INT, user_region VARCHAR(10), adopted_at TIMESTAMP)

transactions(txn_id INT, user_id INT, txn_ts TIMESTAMP, txn_region VARCHAR(10))

Hints

  1. Compute the first transaction region over all transactions before applying the August filter.
  2. Use `ROW_NUMBER` ordered by `txn_ts, txn_id` to make the first-region tie-break deterministic.

Loading coding console...