Write SQL to analyze CA residency history

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. 2) **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`. 3) **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)`.