Write SQL and Python for funnels/retention
Company: Coinbase
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
Given the following schema and small samples, answer Parts A–C. Assume timestamps are UTC and "today" is 2025-09-01. Schema: users(id INT, country STRING, hire_date DATE, visa_status STRING, is_remote BOOLEAN); sessions(session_id INT, user_id INT, session_start TIMESTAMP, session_end TIMESTAMP, device STRING); events(event_id INT, session_id INT, user_id INT, event_time TIMESTAMP, event_type STRING, metadata JSON).
Sample tables:
users
+----+---------+------------+------------+-----------+
| id | country | hire_date | visa_status| is_remote |
+----+---------+------------+------------+-----------+
| 1 | US | 2025-08-15 | H1B | 1 |
| 2 | CN | 2024-12-01 | GC | 0 |
| 3 | IN | 2025-06-01 | H1B | 1 |
+----+---------+------------+------------+-----------+
sessions
+------------+---------+---------------------+---------------------+--------+
| session_id | user_id | session_start | session_end | device |
+------------+---------+---------------------+---------------------+--------+
| 10 | 1 | 2025-08-31 09:00:00 | 2025-08-31 09:30:00 | web |
| 11 | 1 | 2025-09-01 10:00:00 | 2025-09-01 10:07:00 | mobile |
| 12 | 2 | 2025-09-01 11:00:00 | 2025-09-01 11:20:00 | web |
| 13 | 3 | 2025-08-25 12:00:00 | 2025-08-25 12:10:00 | web |
+------------+---------+---------------------+---------------------+--------+
events
+----------+------------+---------+---------------------+------------+------------------------+
| event_id | session_id | user_id | event_time | event_type | metadata |
+----------+------------+---------+---------------------+------------+------------------------+
| 100 | 10 | 1 | 2025-08-31 09:01:00 | login | {} |
| 101 | 10 | 1 | 2025-08-31 09:05:00 | view | {"page":"pricing"} |
| 102 | 10 | 1 | 2025-08-31 09:10:00 | purchase | {"amount":100} |
| 103 | 11 | 1 | 2025-09-01 10:01:00 | login | {} |
| 104 | 11 | 1 | 2025-09-01 10:02:00 | view | {"page":"home"} |
| 105 | 12 | 2 | 2025-09-01 11:05:00 | login | {} |
| 106 | 12 | 2 | 2025-09-01 11:10:00 | purchase | {"amount":50} |
| 107 | 13 | 3 | 2025-08-25 12:05:00 | login | {} |
+----------+------------+---------+---------------------+------------+------------------------+
Part A (SQL): For each date d in [2025-08-26, 2025-09-01], produce one row with: date d; distinct users who started a session that day; distinct users with a login event that day; distinct users with a purchase event that day where the purchase event’s (user_id, session_id, event_time) falls within that user’s session window on day d; conversion rates login→purchase and session→purchase (both as decimals). Treat multiple events by the same user on the same day as one. Ignore events that do not match both user_id and session_id or are outside the session time window. Part B (SQL): For each user, compute first_login_date and first_purchase_date (based only on events that match a valid session window as above) and output a within_7_days flag indicating whether first_purchase_date occurs within 7 days inclusive of first_login_date. Part C (Python): Using pandas DataFrames with the same schemas, implement a function retention_1d(as_of="2025-09-01") that returns the percentage of users whose first_login_date is in [2025-08-25, 2025-09-01] and who have any valid event (matching a session window) on the day exactly one calendar day after their first_login_date. Clearly document how you handle duplicate or orphaned events and time boundaries.
Overview: This question evaluates SQL and Python data-manipulation skills, including event-to-session joins, temporal window filtering, deduplication, JSON metadata handling, and computation of funnel and retention metrics like daily unique users and conversion rates.
Daily funnel metrics by date
Using the schema and sample data below, write a SQL query that, for each calendar date d in the range from 2025-08-26 to 2025-09-01 (inclusive), returns one row with the following columns:
1) event_date: the date d.
2) session_users: the number of distinct users who started at least one session on date d (based on sessions.session_start).
3) login_users: the number of distinct users who had at least one valid login event on date d.
4) purchase_users: the number of distinct users who had at least one valid purchase event on date d.
5) login_to_purchase_rate: purchase_users divided by login_users as a decimal.
6) session_to_purchase_rate: purchase_users divided by session_users as a decimal.
Definitions and rules:
- A **valid event** is an event whose (user_id, session_id) pair matches an existing row in sessions, and whose event_time falls between that session's session_start and session_end (inclusive).
- Only valid events should be used to compute login_users and purchase_users. Ignore any events that do not match both user_id and session_id or that fall outside the corresponding session window.
- Treat multiple login (or purchase) events by the same user on the same date as a single user for that metric.
- If the denominator for a conversion rate is 0 on a given date (e.g., no login_users or no session_users), return NULL for that conversion rate on that date.
- Return all dates in the range 2025-08-26 to 2025-09-01, even if counts are zero.
Tables
users(id INT, country VARCHAR(10), hire_date DATE, visa_status VARCHAR(10), is_remote BOOLEAN)
sessions(session_id INT, user_id INT, session_start TIMESTAMP, session_end TIMESTAMP, device VARCHAR(20))
events(event_id INT, session_id INT, user_id INT, event_time TIMESTAMP, event_type VARCHAR(20), metadata JSON)
Hints
- First build a calendar of all dates in the range, then left join aggregates onto it.
- Create a CTE of valid events by joining events to sessions on user_id and session_id and filtering event_time within the session window.
First login, first purchase, and 7-day conversion flag
Using the same schema and rules for valid events as in Question 1, write a SQL query that returns, for every user_id:
1) first_login_date: the date of the user's first valid login event (minimum event_time with event_type = 'login').
2) first_purchase_date: the date of the user's first valid purchase event (minimum event_time with event_type = 'purchase'), or NULL if the user has no valid purchase.
3) within_7_days: a boolean flag indicating whether first_purchase_date occurs within 7 days inclusive of first_login_date.
Details:
- Only consider **valid events**, defined as events whose (user_id, session_id) pair matches a row in sessions and whose event_time is between that session's session_start and session_end (inclusive).
- Compute first_login_date and first_purchase_date per user based only on these valid events.
- within_7_days should be TRUE if first_purchase_date is not NULL and first_purchase_date is between first_login_date and first_login_date + 7 days (inclusive). Otherwise it should be FALSE.
- Return one row for every user present in the users table.
Tables
users(id INT, country VARCHAR(10), hire_date DATE, visa_status VARCHAR(10), is_remote BOOLEAN)
sessions(session_id INT, user_id INT, session_start TIMESTAMP, session_end TIMESTAMP, device VARCHAR(20))
events(event_id INT, session_id INT, user_id INT, event_time TIMESTAMP, event_type VARCHAR(20), metadata JSON)
Hints
- Start by creating a CTE of valid events using an inner join between events and sessions with a time-window filter.
- Use grouped MIN() over filtered login and purchase events to derive first_login_date and first_purchase_date, then compare the two dates with an interval.
Compute 1-day retention rate from first login
Using the same schema and the same definition of valid events (as in Questions 1 and 2), write a SQL query to compute a 1-day retention metric as of the date 2025-09-01.
Definitions:
- A **valid event** is an event whose (user_id, session_id) pair matches a row in sessions and whose event_time is between that session's session_start and session_end (inclusive). Ignore any events that do not satisfy this.
- For each user, define first_login_date as the date of their first valid login event (minimum event_time where event_type = 'login').
Cohort and retention:
- Define the **cohort** as all users whose first_login_date is between 2025-08-25 and 2025-09-01 (inclusive).
- A user is considered **retained_1d** if they have at least one valid event of any type (login, view, purchase, etc.) on the calendar date exactly one day after their first_login_date, and that event date is not later than 2025-09-01.
- Treat multiple events for the same user on that day as a single retained user (i.e., retention is user-based, not event-based).
Output:
- Return a single row with three columns:
1) cohort_users: the number of users in the cohort.
2) retained_users: the number of cohort users who meet the 1-day retention definition above.
3) retention_1d: retained_users divided by cohort_users as a decimal, or NULL if cohort_users = 0.
Your query should explicitly enforce the session window rule for valid events and the date boundary as of 2025-09-01.
Tables
users(id INT, country VARCHAR(10), hire_date DATE, visa_status VARCHAR(10), is_remote BOOLEAN)
sessions(session_id INT, user_id INT, session_start TIMESTAMP, session_end TIMESTAMP, device VARCHAR(20))
events(event_id INT, session_id INT, user_id INT, event_time TIMESTAMP, event_type VARCHAR(20), metadata JSON)
Hints
- Reuse the first_login_date logic from Question 2 to build the cohort via a CTE, then join back to valid_events to find users with events exactly one day later.
- Remember to enforce the as-of cutoff date (2025-09-01) and to count users, not events, when computing retained_users.