Build a replay-safe ledger load

Merge market_load_feed into market_ledger. For each external_id choose the highest source version, breaking ties by greatest feed id. Insert missing keys and replace a target only when the source version is at least its version. Return the entire final ledger.

what to return

Columns: external_idversionamount in this order
Rows: 3 rows · in the order asked
Watch for: Do not delete target-only or newer target rows. Z starts at version 9, amount 90 and must survive its stale feed record.
: Order by external_id. Your complete script is tested twice on the same database to check that it is safe to rerun. This is checked with both the sample data and different data.
: The feed is immutable; do not clear it or use a one-shot marker to bypass processing.
Skill: Implement an idempotent upsert that handles duplicate source versions and late stale records. Refresher: Idempotent incremental UPSERT & stale updates →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result3 rows · in this order

external_id	version	amount
X	2	25
Y	1	5
Z	9	90

all 3 rows

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.

market_load_feed4 columns · 5 rows

idinteger · key	external_idtext	versioninteger	amountinteger
1	X	1	10
2	X	2	20
3	X	2	25
4	Y	1	5
5	Z	2	999

all 5 rows

market_ledger3 columns · 2 rows

external_idtext · key	versioninteger	amountinteger
X	1	10
Z	9	90

all 2 rows

solution.sql

Hints and the answer are always available. Using them counts the question as solved with help.