Coinbase·Data Scientist·Take-home Assignment·Senior

Senior

Jun 2026Remote

Summary

Coinbase data scientist take-home that was basically one giant multi-part SQL and Python problem. The schema was realistic enough but the question density was a lot for a single sitting. Felt more like a mini project than a typical OA.

Questions Asked (3)

Given a users, sessions, and events schema with sample data, write SQL to produce a daily summary for each date in a given range showing distinct active users, distinct users with login events, distinct users with valid purchase events (matched by user_id, session_id, and falling within the session time window), and the login-to-purchase and session-to-purchase conversion rates as decimals.

Product Analytics & MetricsData Modeling

Author's notes

The session window matching part is what trips you up if you're not careful.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by defining the date range and generating a complete date spine to ensure all dates are represented. Then, for each date, compute the distinct counts of active users, login users, and valid purchase users by joining sessions and events appropriately. Finally, calculate the conversion rates as decimals, handling division by zero.

Pro tip: Use a date spine to avoid missing dates with zero activity, and be explicit about how you handle sessions that span midnight—decide whether to attribute events to the session start date or the event date, and document your choice.

1. Generate date spine

Create a series of dates covering the entire range to ensure every date appears in the output, even if there is no activity.

2. Compute daily active users

Count distinct user_id from sessions (or events) for each date, depending on the definition of 'active'.

3. Compute login users

Count distinct user_id from events where event_type = 'login' for each date.

4. Compute valid purchase users

Join events (purchases) with sessions on user_id and session_id, ensuring the event timestamp falls within the session's start and end time; count distinct user_id per date.

5. Calculate conversion rates

Compute login-to-purchase as valid_purchase_users / login_users and session-to-purchase as valid_purchase_users / active_users, using NULLIF to avoid division by zero.

Key Points to Mention

Use of a date spine (e.g., GENERATE_SERIES or recursive CTE) to include all dates in the range.
Definition of 'active user'—clarify whether it's based on sessions or events, and ensure consistency.
Join conditions for valid purchases: match on user_id and session_id, and check event timestamp between session start and end.
Handling of sessions that span multiple days—decide on attribution logic (e.g., by session start date or event date).
Conversion rate calculation: use decimal division and NULLIF to prevent division by zero errors.
Consideration of time zones and date truncation if timestamps include time components.

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

Using the same valid-session-window logic, write SQL to compute each user's first login date and first purchase date, then flag whether the first purchase happened within 7 days inclusive of the first login.

Product Analytics & MetricsData Modeling

Author's notes

Straightforward once Part A is done.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a CTE to compute each user's first login and first purchase dates via MIN aggregation, then join these results and apply a DATEDIFF check to flag whether the first purchase occurred within 7 days inclusive of the first login. Ensure the logic mirrors the valid-session-window approach by using inclusive boundaries and handling users with no purchases.

Pro tip: Explicitly state that you're using an inclusive 7-day window (i.e., purchase_date <= login_date + 7 days) and mention how you'd handle users with no purchases—this shows attention to edge cases and metric definition rigor, which is critical at Coinbase.

1. Identify first login per user

Write a CTE that selects user_id and MIN(login_date) as first_login_date from the logins table, grouping by user_id.

2. Identify first purchase per user

Write a second CTE that selects user_id and MIN(purchase_date) as first_purchase_date from the purchases table, grouping by user_id.

3. Join first login and first purchase

LEFT JOIN the two CTEs on user_id to retain users who logged in but never purchased, ensuring first_purchase_date is NULL for them.

4. Compute flag for 7-day inclusive window

Use a CASE expression with DATEDIFF(day, first_login_date, first_purchase_date) BETWEEN 0 AND 7 to flag whether the first purchase happened within 7 days inclusive of the first login.

5. Final selection and ordering

Select user_id, first_login_date, first_purchase_date, and the flag; optionally order by user_id for readability.

Key Points to Mention

Use of MIN aggregation to get first event dates per user
LEFT JOIN to include users without purchases and avoid losing them
Inclusive window logic: DATEDIFF BETWEEN 0 AND 7 or purchase_date <= login_date + INTERVAL '7 days'
Handling NULL first_purchase_date in the flag calculation (e.g., CASE WHEN first_purchase_date IS NULL THEN 0 ELSE ...)
Consistency with the valid-session-window logic mentioned in the question (e.g., inclusive boundaries)
Potential need to filter for valid sessions or logins if the context requires it

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

In Python using pandas, implement a function that computes 1-day retention: given an as_of date, find users whose first login date falls in the 7-day window ending on that date, then return the percentage of those users who have any valid event exactly one calendar day after their first login. Document how you handle duplicate events and orphaned events.

Product Analytics & MetricsAlgorithms & Data Structures

Author's notes

The documentation requirement is the part I spent the most time on.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the definitions of 'valid event', 'first login', and how to handle duplicates and orphaned events. Then, outline a pandas-based solution: filter users by first login date in the 7-day window, compute their first login date, and check for any valid event exactly one day later. Finally, discuss edge cases and how to document your assumptions.

Pro tip: Mention that you would validate the retention calculation by manually checking a few user journeys, and that you would consider using a left join to ensure all users in the cohort are included, even those without a day-1 event.

1. Clarify definitions and assumptions

Define what constitutes a 'valid event', 'first login', and how to handle duplicates and orphaned events. State assumptions explicitly, such as whether orphaned events are ignored or cause errors.

2. Filter and compute first login dates

From the events data, filter to login events, drop duplicates to get unique user-date pairs, then compute the first login date per user. Then select users whose first login falls within the 7-day window ending on as_of.

3. Identify day-1 retained users

For each user in the cohort, check if they have any valid event exactly one calendar day after their first login. Use a merge or groupby to flag these users.

4. Calculate retention percentage

Compute the percentage as the number of retained users divided by the total number of users in the cohort, multiplied by 100. Handle division by zero if the cohort is empty.

5. Document handling of duplicates and orphaned events

Explain how duplicates are removed (e.g., drop_duplicates on user_id and event_date) and how orphaned events (events without a corresponding user or login) are treated (e.g., ignored or logged).

Key Points to Mention

Definition of 'valid event' and 'first login' (e.g., login event type, timestamp truncation to date).
Handling duplicates: deduplicate events by user and date to avoid double-counting.
Handling orphaned events: events with user_ids not in the user table or events before first login; decide to ignore or flag.
Use of pandas functions: groupby, merge, drop_duplicates, and date arithmetic (e.g., pd.DateOffset).
Edge cases: empty cohort, users with no events after first login, timezone considerations.
Performance considerations: filtering early, using efficient joins, and avoiding loops.

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