Robinhood·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

Robinhood analytics engineer interview with a pretty gnarly SQL question involving edit logs and temporal filtering. The kind of problem where you think you have it and then realize you missed a condition halfway through.

Questions Asked (1)

Given a users table and a state-change edit log table (joined on id and shard), write a query to find users who: started with a state other than CA, had at least one edit log entry changing their state to CA before 2021-01-01, and whose current state is still CA as of that date. Return id, shard, and name.

Data ModelingProduct Analytics & Metrics

Author's notes

Three separate conditions and I kept collapsing two of them together in my head.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a CTE to find the earliest state-change edit log entry for each user (id, shard) and filter for entries where the new state is 'CA' and the change timestamp is before 2021-01-01. Then join this back to the users table to ensure the user's initial state (from the users table) was not 'CA' and their current state as of that date is still 'CA'. Finally, select id, shard, and name.

Pro tip: Clarify the schema: the users table likely has a 'state' column representing the current state, and the edit log has 'old_state' and 'new_state' columns. Also, consider that a user might have multiple state changes; you need the first change to CA before the cutoff and no subsequent changes away from CA before the cutoff.

1. Understand the schema and requirements

Identify the relevant columns: users table (id, shard, name, state), edit log table (id, shard, timestamp, old_state, new_state). Clarify that 'started with a state other than CA' means the user's initial state (before any edits) was not CA, and 'current state is still CA as of that date' means as of 2021-01-01, the user's state was CA.

2. Find the first state change to CA before the cutoff

For each user (id, shard), find the earliest edit log entry where new_state = 'CA' and timestamp < '2021-01-01'. Ensure that this is indeed the first change to CA (i.e., no earlier change to CA).

3. Ensure no subsequent change away from CA before the cutoff

Check that there are no edit log entries after the first change to CA and before 2021-01-01 where new_state != 'CA'. This ensures the user's state remained CA as of the cutoff date.

4. Join with users table and filter initial state

Join the filtered edit log entries with the users table on id and shard. Filter for users whose initial state (from users table) is not 'CA'. Also, ensure the users table's current state is 'CA' (or rely on the edit log to confirm).

5. Select and return the required columns

Return id, shard, and name for the qualifying users.

Key Points to Mention

Use of window functions (e.g., ROW_NUMBER() or MIN() with GROUP BY) to find the first change to CA.
Handling of composite keys (id, shard) in joins and partitions.
Consideration of time zones and timestamp precision for the cutoff date.
Ensuring that the user's initial state is not CA, which might be derived from the users table or the earliest edit log entry.
Checking for no subsequent changes away from CA before the cutoff date.
Performance considerations: indexing on (id, shard, timestamp) and filtering early.

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