Interview Question Bank · DISTINCT ON / ROW_NUMBER · tie-breaker · deletes
4. Current state from a change log
MediumData engineeringQuestion #244
Concept: DISTINCT ON / ROW_NUMBER · tie-breaker · deletesshow
A change-data-capture log holds every insert, update and delete, so the current state of a key is its newest row. Rank each key's rows by change time, newest first, and break ties with the log sequence number so exactly one row wins. Drop deletes only after picking the winner: removing 'D' rows first would bring a deleted record back to life.
Example (different data):
SELECT * FROM ( SELECT DISTINCT ON (sku) sku, action, qty FROM stock_changes ORDER BY sku, changed_at DESC, seq DESC ) latest WHERE action <> 'remove';
Your task
Fernhill replicates its product catalog into the warehouse from a change-data-capture feed, de_cdc_products. Messages can be replayed out of order, so lsn alone doesn't tell you which change is newest.
Rebuild the current product table: for each product_id take the row with the latest updated_at (when two rows share updated_at, the higher lsn wins), and leave out products whose winning row is a delete (op 'D'). Return product_id, name, price and updated_at, sorted by product_id.
Starting Postgres in your browser…
Checking your access…