← Coinbase Interview Insights

Coinbase·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say
Jul 2026Remote

Summary

SQL-heavy technical screen for a DS role at Coinbase. Three questions, all back-to-back, all on the same invented schema. No behavioral stuff at all which surprised me, just pure query writing under time pressure.

Questions Asked (3)

Q1

Given a users table and a transactions table, write SQL to compute adoption rate and transaction rate per region for August 2025. Adoption rate is the share of users in that region who adopted in August, transaction rate is the share who made at least one transaction in August.

Product Analytics & MetricsData Modeling
Author's notes

Took me longer than it should have to realize I needed to keep all users in the denominator, not just the ones with activity.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the definitions of 'adopted' and 'transaction' and the table schemas. Then, use conditional aggregation to compute the numerator and denominator for each region, ensuring you filter for August 2025. Finally, calculate the rates as percentages or decimals.

Pro tip: Always state your assumptions about the data model, such as whether 'adopted' means a user's first transaction or a specific event, and confirm the date range boundaries. This shows attention to detail and prevents misinterpretation.

1. Clarify definitions and schema

Ask clarifying questions about what 'adopted' means (e.g., first transaction, sign-up, or specific event) and confirm the columns in users and transactions tables, including region and date fields.

2. Filter for August 2025

Restrict transactions to August 2025 using a date filter (e.g., transaction_date >= '2025-08-01' AND transaction_date < '2025-09-01').

3. Compute adoption rate per region

For each region, calculate the number of users who adopted in August divided by the total number of users in that region. Use a LEFT JOIN or subquery to include all users.

4. Compute transaction rate per region

For each region, calculate the number of users who made at least one transaction in August divided by the total number of users in that region.

5. Combine and format results

Join the two metrics by region and output the rates as decimals or percentages, ensuring clear column names.

Key Points to Mention

  • Define 'adopted' clearly—likely a user's first transaction or a specific adoption event.
  • Use conditional aggregation (e.g., COUNT(DISTINCT CASE WHEN ...)) to compute distinct users per metric.
  • Ensure the denominator is all users in the region, not just active ones, to reflect true adoption/transaction rates.
  • Handle potential NULLs or missing regions by using LEFT JOINs from the users table.
  • Consider timezone if transaction timestamps are in UTC and 'August' is defined in a specific timezone.
  • Output rates as percentages with appropriate rounding for readability.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q2

For each user, calculate the number of days between their adoption date and their first transaction. Only include users where adopted_at is not null and the first transaction happened after adoption. Then write a second query returning the p10, p50, and p90 of that value.

Product Analytics & MetricsAlgorithms & Data Structures
Author's notes

The per-user query was straightforward.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, write a query that joins users to their transactions, filters for adopted_at IS NOT NULL and transaction_date > adopted_at, and computes the minimum transaction date per user. Then calculate the day difference between that first transaction date and adopted_at, and finally compute the p10, p50, and p90 percentiles using a window function or percentile_cont.

Pro tip: Clarify whether 'days between' should be inclusive or exclusive and whether to use calendar days or 24-hour periods; also confirm if the percentile calculation should be over all users or only those with a first transaction after adoption.

1. Filter and identify first transaction

Filter users where adopted_at IS NOT NULL and transactions occur after adopted_at. Then find the earliest transaction date per user using MIN() or ROW_NUMBER().

2. Compute days between adoption and first transaction

Calculate the difference in days between the first transaction date and adopted_at, ensuring the result is non-negative and only includes valid users.

3. Calculate percentiles

Use PERCENTILE_CONT(0.10), PERCENTILE_CONT(0.50), and PERCENTILE_CONT(0.90) with WITHIN GROUP (ORDER BY days_diff) to get p10, p50, and p90.

4. Handle edge cases and validate

Consider timezone differences, null values, and whether to include users with no transactions. Validate results by checking counts and distribution.

Key Points to Mention

  • Use of CTEs or subqueries to first get first transaction per user
  • Filtering conditions: adopted_at IS NOT NULL AND transaction_date > adopted_at
  • Date difference calculation: DATEDIFF or equivalent, ensuring positive values
  • Percentile calculation using PERCENTILE_CONT or APPROX_PERCENTILE for large datasets
  • Handling of timezones and date truncation if necessary
  • Performance considerations: indexing on user_id and transaction_date

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q3

Define a cross-region sale as any transaction where the transaction region differs from the user's earliest-ever transaction region. Write a query that returns all August 2025 transactions with a flag indicating whether each is a cross-region sale, plus the user's first transaction region.

Data ModelingProduct Analytics & Metrics
Author's notes

This one was actually fun.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify each user's earliest-ever transaction region using a window function or subquery. Then, join this back to the August 2025 transactions and compare the transaction region to the earliest region to flag cross-region sales. Finally, output the transaction details, the flag, and the user's first transaction region.

Pro tip: Clarify the definition of 'region' (e.g., country, state) and confirm whether the earliest transaction should be determined before or including August 2025. Also, consider edge cases like users with no prior transactions before August 2025.

1. Identify the user's earliest transaction region

Use a window function like ROW_NUMBER() partitioned by user, ordered by transaction timestamp, to find the region of the first transaction for each user.

2. Filter August 2025 transactions

Select all transactions where the transaction date falls within August 2025.

3. Join earliest region to August transactions

Join the earliest region data to the August transactions on user ID, ensuring each August transaction gets the user's first region.

4. Flag cross-region sales

Create a boolean flag that is true when the transaction region differs from the user's first transaction region.

5. Output required columns

Return the transaction details, the cross-region flag, and the user's first transaction region.

Key Points to Mention

  • Use of window functions (e.g., ROW_NUMBER, FIRST_VALUE) to determine earliest transaction region.
  • Handling of ties in transaction timestamps (e.g., if multiple transactions occur at the same earliest time).
  • Definition of 'region' and ensuring consistency in region naming.
  • Consideration of users whose first transaction occurs in August 2025 (flag should be false).
  • Efficiency: avoid correlated subqueries; use joins or window functions for performance.
  • Edge cases: users with no transactions before August 2025, null regions, or multiple regions in first transaction.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.