Drill 4 of 12 in Data engineering questions
0

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…