Katkat
Back

Write SQL to analyze CA residency history

RobinhoodData Engineer · Technical Screen
HardData EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

This question evaluates a candidate's ability to write SQL for temporal state reconstruction, including correct joins on composite keys, interpreting edit/audit logs, and reasoning about inclusive/exclusive timestamps.

You have two SQL tables.

Table 1: user_info (current snapshot)

  • id (user id)
  • shard (partition column)
  • name
  • country
  • state
  • created_date

Table 2: user_info_edit_log (edit history)

  • id
  • shard
  • created_date (when the change happened)
  • table_name
  • field_name
  • old_value
  • new_value

Assumptions/constraints:

  • When joining the tables, you must join on both (id, shard).
  • For this question, you can assume table_name = 'user_info' and field_name = 'state' are the only relevant values (i.e., only state changes for user_info).
  • Output columns for all questions: (id, shard, name).
  • Interpret "as of 2021-01-01" as the user’s state at timestamp 2021-01-01 00:00:00 (inclusive of changes at that instant).

Write SQL for each of the following:

  1. Who always lived in CA as of 2021-01-01?
  • Meaning: their state on 2021-01-01 is CA, and there is no evidence they were ever in a non-CA state before that time.
  1. Who moved to CA before 2021-01-01 and lived in CA as of 2021-01-01?
  • Meaning: their state on 2021-01-01 is CA, and they changed from a non-CA state to CA at some time strictly before 2021-01-01.
  1. Who might have moved out of CA after 2021-01-01?
  • Meaning: they were in CA as of 2021-01-01, and there is a state-change record after 2021-01-01 indicating CA -> (not CA).