SQL data engineering interview problem. Difficulty: advanced. Pattern: Upsert. About 20 minutes. Part of the Pro drill bank.
accounts_target is the current state and accounts_staging holds incoming rows keyed by account_id. Do not change either table. Work on a copy: 1. Create a table named accounts_work with the same rows as accounts_target. 2. Apply accounts_staging to it: a key that already exists is updated when its plan or balance differs, a key that does not exist is inserted, and every other row is left alone. 3. Finish with a SELECT of the table. Columns: account_id, plan, balance. Order by account_id. The script must be safe to run twice.
Input: accounts_target account_id | plan | balance 1 | basic | 100 2 | pro | 250 3 | basic | 80 4 | pro | 500 accounts_staging account_id | plan | balance 2 | pro | 275 3 | basic | 80 5 | basic | 40 6 | pro | 900 Output: account_id | plan | balance 1 | basic | 100 2 | pro | 275 3 | basic | 80 4 | pro | 500 5 | basic | 40 6 | pro | 900 Why this passes: Account 2 changes to 275, account 3 is unchanged, accounts 5 and 6 are new, and accounts 1 and 4 are not in staging so they stay as they were.
Input: accounts_target account_id | plan | balance 1 | basic | 10 accounts_staging account_id | plan | balance 1 | pro | 10 2 | basic | 5 Output: account_id | plan | balance 1 | pro | 10 2 | basic | 5 Why this passes: One update and one insert.
Topics: lakebench, sql, merge, upsert, dml.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Apply staged rows to a copy of the target: update changed keys, insert new ones, leave the rest.
`accounts_target` is the current state and `accounts_staging` holds incoming rows keyed by `account_id`. Do not change either table. Work on a copy: 1. Create a table named `accounts_work` with the same rows as `accounts_target`. 2. Apply `accounts_staging` to it: a key that already exists is updated when its `plan` or `balance` differs, a key that does not exist is inserted, and every other row is left alone. 3. Finish with a SELECT of the table. Columns: `account_id`, `plan`, `balance`. Order by `account_id`. The script must be safe to run twice.