Deduplicate customers, keep latest record

Medium38% solve itAsked at Amazon, Walmart, Swiggy

The customers table receives a daily change feed, so the same customer_id appears many times. Return exactly one row per customer — the one with the newest updated_at. If two rows share a timestamp, keep the higher source_id.

Input schema

customers customer_id bigint full_name string email string source_id int updated_at timestamp

Example

customer_id full_name source_id updated_at 101 A Sharma 2 2024-03-01 09:00 101 A Sharma 5 2024-03-04 11:20 102 R Iyer 1 2024-02-27 08:10 Output 101 A Sharma 5 2024-03-04 11:20 102 R Iyer 1 2024-02-27 08:10

Constraints

Hidden dataset contains up to 50,000,000 rows
collect() and toPandas() are blocked
Output column order must match the input schema
Wall clock limit 30 seconds, 4 GB memory

Example 1basic duplicates

Input · customers

email	full_name	source_id	updated_at	customer_id
a@x.com	A Sharma	2	2024-03-01T09:00:00Z	101
a@x.com	A Sharma	5	2024-03-04T11:20:00Z	101
r@x.com	R Iyer	1	2024-02-27T08:10:00Z	102

Output

email	full_name	source_id	updated_at	customer_id
a@x.com	A Sharma	5	2024-03-04T11:20:00Z	101
r@x.com	R Iyer	1	2024-02-27T08:10:00Z	102

Example 2tie on updated_at keeps the higher source_id

Input · customers

email	full_name	source_id	updated_at	customer_id
k@x.com	K Rao	3	2024-05-01T00:00:00Z	7
k2@x.com	K Rao	9	2024-05-01T00:00:00Z	7

Output

email	full_name	source_id	updated_at	customer_id
k2@x.com	K Rao	9	2024-05-01T00:00:00Z	7

Topics

window functionsdeduplicationpartitioning