Difficulty: Intermediate | Prerequisites: Basic SQL (SELECT, WHERE, JOIN, GROUP BY), Part 1 (Interview Process) Date: 2025 | Source: Interview Query – Coinbase Data Science Interview Guide
Tags: Coinbase, SQL, data querying, window functions, LAG, PARTITION BY, HAVING, JOIN, GROUP BY, CTE, common table expression, nested structures, JSON, lateral join, data scientist interview
SQL is one of the most heavily weighted skill areas in the Coinbase data scientist interview. The radar chart in the source material shows SQL as a top-tier requirement, equal to or exceeding statistics. Coinbase data scientists query large, complex datasets daily to extract user behaviour insights, track transaction patterns, and inform product decisions. Interview questions test not just whether you can write correct SQL, but whether you can handle window functions, conditional aggregation, multi-table joins, and nested data structures fluently.
Coinbase SQL questions focus on window functions (especially LAG with PARTITION BY), conditional aggregation with CASE statements, the HAVING clause for filtering aggregated data, and querying nested or complex data structures. Practise writing CTEs, chaining window functions, and building queries that combine multiple techniques in a single solution.
Common Table Expression (CTE)
A named temporary result set defined with the WITH keyword that you can reference within the main query. In simple terms, it is a way to break a complex query into readable, named steps.
Window function
A function that performs a calculation across a set of rows related to the current row, without collapsing them into a single output row. Think of it as a way to add computed columns (like "previous row's value" or "running total") without changing the number of rows.
LAG()
A window function that accesses the value of a column from a previous row within the same partition. Used to compare the current row to its predecessor.
PARTITION BY
A clause within a window function that divides the result set into partitions. The window function is applied independently within each partition. Think of it as GROUP BY for window functions, except the rows are not collapsed.
HAVING clause
A filter applied after GROUP BY aggregation. While WHERE filters individual rows before grouping, HAVING filters groups after aggregation.
Conditional aggregation
Using CASE expressions inside aggregate functions (SUM, COUNT) to count or sum only rows meeting specific conditions. A common pattern for pivoting data or computing multiple filtered metrics in a single query.
Nested data structures
Data stored in formats like JSON, arrays, or deeply related tables that require specialised techniques (JSON functions, lateral joins, UNNEST) to query effectively.
Problem: Given a user_experiences table, find the percentage of users who held the title "data analyst" immediately before holding "data scientist," with no other titles in between.
Table schema:
Column | Type |
|---|---|
id | INTEGER |
position_name | VARCHAR |
start_date | DATETIME |
end_date | DATETIME |
user_id | INTEGER |
Solution approach:
Step 1 – Use LAG to get the previous role for each user:
WITH added_previous_role AS (
SELECT user_id, position_name,
LAG(position_name)
OVER (PARTITION BY user_id ORDER BY start_date)
AS previous_role
FROM user_experiences
)
The LAG function, partitioned by user_id and ordered by start_date, gives each row the position_name from the user's chronologically previous role.
Step 2 – Filter to users whose current role is "Data Scientist" and previous role is "Data Analyst":
, experienced_subset AS (
SELECT *
FROM added_previous_role
WHERE position_name = 'Data Scientist'
AND previous_role = 'Data Analyst'
)
Step 3 – Calculate the percentage:
SELECT COUNT(DISTINCT experienced_subset.user_id) /
COUNT(DISTINCT user_experiences.user_id)
AS percentage
FROM user_experiences
LEFT JOIN experienced_subset
ON user_experiences.user_id = experienced_subset.user_id
Key technique: The LAG window function with PARTITION BY is the core tool here. Without it, you would need a self-join, which is more complex and slower.
Important note: The ORDER BY clause inside the window function is critical. The source solution omits it, but you should order by start_date to ensure "immediately before" is defined chronologically. Mention this in your interview.
Problem: Identify customers who placed more than three transactions each in both 2019 and 2020.
Table schemas:
transactions: id, user_id, created_at, product_id, quantity
users: id, name
Solution approach:
WITH transaction_counts AS (
SELECT u.id, u.name,
SUM(CASE WHEN YEAR(t.created_at) = 2019 THEN 1 ELSE 0 END) AS t_2019,
SUM(CASE WHEN YEAR(t.created_at) = 2020 THEN 1 ELSE 0 END) AS t_2020
FROM transactions t
JOIN users u ON u.id = t.user_id
GROUP BY u.id, u.name
HAVING t_2019 > 3 AND t_2020 > 3
)
SELECT name AS customer_name
FROM transaction_counts
Key techniques:
Conditional aggregation: The CASE WHEN inside SUM counts transactions per year without needing separate subqueries or multiple passes over the data.
HAVING: Filters after aggregation to keep only customers meeting the threshold in both years.
Single-pass efficiency: This pattern avoids joining the transactions table to itself, which would be slower on large datasets.
Problem: Given a complex dataset with nested structures, how would you extract specific information?
General approach:
Start by understanding the data schema and relationships between tables or collections.
Use SQL JOINs to combine related tables.
For nested arrays or JSON objects, use specialised functions:
JSON functions (JSON_EXTRACT, JSON_ARRAY_ELEMENTS) to access nested fields.
Lateral joins (LATERAL or CROSS APPLY) to flatten arrays so each element becomes a row.
UNNEST to expand array columns into individual rows.
Use subqueries or nested aggregation to work at different levels of granularity.
Performance considerations:
Consider indexing strategies on frequently queried columns.
Use partitioning for very large tables.
Review query execution plans to identify bottlenecks.
Problem: Identify users whose total portfolio value exceeds a threshold across multiple cryptocurrencies.
Solution:
SELECT user_id, SUM(portfolio_value) AS total_portfolio_value
FROM user_portfolio
GROUP BY user_id
HAVING total_portfolio_value > threshold_value;
Why HAVING and not WHERE:
WHERE filters rows before aggregation. It cannot reference aggregated values.
HAVING filters groups after aggregation. It operates on the results of aggregate functions like SUM, COUNT, or AVG.
If you need to filter on the sum of portfolio values, you must use HAVING.
LAG pattern for "previous row" comparisons:
LAG(column_name) OVER (PARTITION BY grouping_column ORDER BY ordering_column)
Conditional aggregation pattern:
SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS count_name
Percentage calculation pattern:
COUNT(DISTINCT subset.id) * 1.0 / COUNT(DISTINCT total.id)
(Multiply by 1.0 or cast to float to avoid integer division.)
"WHERE and HAVING are interchangeable." They are not. WHERE filters rows before grouping; HAVING filters groups after aggregation. Attempting to use WHERE on an aggregated column will produce an error.
"LAG always returns the previous row." LAG returns the previous row within the partition and ordering you define. If you omit ORDER BY, the result is undefined and unreliable.
"You need separate queries for each year in the transaction count problem." Conditional aggregation (CASE WHEN inside SUM) handles this in a single pass.
"Nested data always requires application-side processing." Most modern SQL databases provide native JSON functions and lateral joins that let you handle nested structures directly in SQL.
⚠️ SQL is one of the most heavily tested areas at Coinbase. Expect at least one or two pure SQL questions in the virtual interview rounds.
⚠️ Window functions (LAG, LEAD, ROW_NUMBER, RANK) appear frequently. Be comfortable using them with both PARTITION BY and ORDER BY.
⚠️ Know the difference between WHERE and HAVING cold. Interviewers will test whether you understand where in the query execution pipeline each one operates.
⚠️ For the career transition query (Q11), the ORDER BY inside the window function is essential for correctness. The source solution omits it, so mentioning it in an interview shows deeper understanding.
True or False: The HAVING clause filters rows before the GROUP BY operation.
Fill in the blank: LAG() is a _______ function that accesses a value from a previous row.
True or False: PARTITION BY inside a window function collapses rows into groups like GROUP BY does.
Fill in the blank: To avoid integer division when calculating a percentage in SQL, you can multiply by _______ or cast to a float type.
True or False: Conditional aggregation using CASE WHEN inside SUM is less efficient than writing separate subqueries for each condition.
Answers: 1. False (it filters after GROUP BY). 2. Window. 3. False (it partitions for calculation purposes but preserves individual rows). 4. 1.0. 5. False (conditional aggregation is typically more efficient because it scans the table once).
Q: Write a query to find the percentage of users who were "data analysts" immediately before becoming "data scientists."
A: Use a CTE with LAG(position_name) OVER (PARTITION BY user_id ORDER BY start_date) to get each user's previous role. Filter for rows where position_name is "Data Scientist" and previous_role is "Data Analyst." Calculate the percentage as the count of distinct qualifying users divided by the total count of distinct users.
Q: How would you find customers who made more than three transactions in both 2019 and 2020?
A: Join transactions to users. Use conditional aggregation: SUM(CASE WHEN YEAR(created_at) = 2019 THEN 1 ELSE 0 END) as t_2019, and the same for 2020. GROUP BY user, then HAVING t_2019 > 3 AND t_2020 > 3.
Q: Explain the difference between WHERE and HAVING.
A: WHERE filters individual rows before any grouping or aggregation takes place. HAVING filters groups after GROUP BY and aggregation. If you need to filter on the result of an aggregate function (e.g., SUM > 100), you must use HAVING.
Q: How would you query a JSON column containing nested user preferences?
A: Use the database's JSON functions (e.g., JSON_EXTRACT in MySQL, the -> and ->> operators in PostgreSQL) to access specific fields within the JSON. For arrays, use UNNEST or JSON_ARRAY_ELEMENTS with a lateral join to expand each element into its own row for analysis.
The SQL skills here feed directly into the scenario-based challenge (Part 1), where you will need to query a provided dataset to answer a business question. The conditional aggregation and HAVING patterns are relevant whenever you need to compute metrics for the statistics and hypothesis testing covered in Part 3. The nested data querying techniques connect to the feature engineering steps discussed in the machine learning questions in Part 5.
SQL interview questions, Coinbase SQL, window functions, LAG function, PARTITION BY, HAVING clause, GROUP BY, conditional aggregation, CASE WHEN, CTE, common table expression, nested data SQL, JSON querying, lateral join, UNNEST, data scientist SQL, transaction filtering, user experience query, portfolio analysis SQL