Parcel scans after the watermark

Parcel scans after the watermark

You are a data engineer at a logistics platform. Every night an incremental pipeline picks up fresh parcel scans from the parcel_scans table and appends them to the warehouse. The position of the last successful load (the watermark) is stored in the etl_state table — in the row with source_name = 'parcel_scans' (the table may hold rows for other sources too). Your job is to build the next increment: the scans the pipeline has not seen yet, i.e. those whose time is strictly greater than the watermark. Scans with an unknown time (scan_time IS NULL) are considered not ready and must not be included. Return columns: - scan_id — scan identifier; - parcel_code — parcel code; - hub_code — sorting hub code; - scan_time — scan time (timestamp, as stored); - status — parcel status (may be NULL — output as is). Sorting: by scan_time ascending, ties broken by scan_id ascending. A scan whose time equals the watermark exactly is NOT part of the result.

Expected output sample

This is what a correct answer looks like — its row count is its own, it doesn't have to match the schema tables. Your answer must use the same column names as this sample — alias them with AS if needed.

scan_idparcel_codehub_codescan_timestatus
8PKG-1006HUB-MUC2025-06-10T12:00:01arrived
9PKG-1003HUB-BER2025-06-10T15:20:00delivered
10PKG-1007HUB-HAM2025-06-10T18:45:00arrived
PostgreSQLv16

Your query result will appear here

Focus radio
Paused · SomaFM · Fluid