Robinhood·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL technical screen for an Analytics Engineer role at Robinhood. One question, pretty involved, centered on filtering users based on historical state changes across two tables. The kind of problem where you think you have it and then realize there's a subtle edge case you almost missed.

Questions Asked (1)

You have two tables joined on (id, shard): user_info with current user state, and user_info_edit_log tracking every field change. Write a query to find all users whose state has always been 'CA' as of 2021-01-01, meaning their current state is 'CA' and the edit log shows no record of them ever being in a different state before that date. Return id, shard, and name.

Data ModelingProduct Analytics & Metrics

Author's notes

My first instinct was just to filter user_info on state = 'CA' and call it a day.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and the exact requirement: current state is 'CA' and no edit log entry before 2021-01-01 shows a different state. Use a NOT EXISTS subquery to filter out users with any such log entry, then join to user_info to get the name. Alternatively, use a LEFT JOIN with a condition and filter for NULLs.

Pro tip: Mention that you would verify the data types and timezone of the edit timestamp, and consider indexing the edit log on (id, shard, field, timestamp) to make the anti-join efficient. Also, clarify whether 'before that date' includes the date itself or is strictly before.

1. Clarify requirements and schema

Confirm the meaning of 'always been CA as of 2021-01-01': current state is 'CA' and no edit log entry before that date shows a different state. Check the columns available in both tables and the timestamp format.

2. Filter current users with state 'CA'

Select from user_info where state = 'CA' to get the base set of users who currently have the desired state.

3. Exclude users with conflicting edit history

Use a NOT EXISTS subquery or LEFT JOIN to eliminate users who have any edit log entry before 2021-01-01 where the new state is not 'CA'.

4. Join to get user name and return required columns

Ensure the final query returns id, shard, and name by joining the filtered user_info with any necessary name source (if name is in user_info, no extra join needed).

5. Consider performance and edge cases

Discuss indexing, handling NULLs, and whether the edit log includes initial state changes. Also consider if a user could have multiple shards.

Key Points to Mention

Use of NOT EXISTS or LEFT JOIN ... IS NULL for anti-join pattern
Importance of filtering on the correct timestamp column and condition (before 2021-01-01)
Handling of NULL values in the edit log (e.g., if old_state or new_state can be NULL)
Potential need to deduplicate if a user has multiple rows in user_info (though likely unique on id, shard)
Indexing strategy for performance: composite index on (id, shard, field, timestamp)
Clarifying whether 'always been CA' includes the initial state or only changes after creation

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