Write SQL to analyze CA residency history

Q: Write SQL to analyze CA residency history

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.

Q: How do I approach Software Engineering Fundamentals interview questions?

Software Engineering Fundamentals questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master software engineering fundamentals interviews.

Question

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:

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.
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 .
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) .

Write SQL to analyze CA residency history

Quick Overview

Solution

Comments (0)