Deduplicate Streaming Records

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Deduplicate Streaming Records. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Write a query to deduplicate a streaming dataset where duplicate records can arrive up to 24 hours late.

Assume streaming_events contains a rolling input window. Use the latest ingested_at value as the watermark, include records ingested within the preceding 24 hours inclusively, and retain the earliest ingested row for each event_id. Use ingestion_id as the deterministic tie-breaker.

Output

One row per retained event_id, with columns event_id, event_time, ingested_at, and payload
Exclude records outside the 24-hour ingestion window and later duplicates
Order by event_id, then ingestion_id ascending

Schema

streaming_events

Column	Type	Description
ingestion_idPK	BIGINT	Unique identifier for the ingested row
event_id	VARCHAR(64)	Logical identifier used to identify duplicate events
event_time	TIMESTAMPTZ	Time at which the event occurred
ingested_at	TIMESTAMPTZ	Time at which the streaming system received the row
payload	TEXT	Optional event payload

Tablesstreaming_events

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results