Find duplicate invoices

← Problems15. Find duplicate invoicesEasySQL

00:00 / 20:00

Find duplicate invoices

Easy54% solve itAsked at Walmart, Deloitte

An upstream loader retried without idempotency, so some invoices were inserted more than once. Two rows are duplicates when vendor_id, invoice_number and amount all match. Return one row per duplicate group with the number of copies and the earliest loaded_at.

Input schema

invoices invoice_id bigint vendor_id bigint invoice_number string amount numeric loaded_at timestamp

Constraints

Groups with a single row are not duplicates and must not appear
Order by copies descending, then vendor_id, then invoice_number
Up to 40,000,000 rows

Example 1one duplicated invoice

Input · invoices

amount	loaded_at	vendor_id	invoice_id	invoice_number
250	2024-05-02 01:10:00	7	1	INV-1001
250	2024-05-03 01:10:00	7	2	INV-1001
99.5	2024-05-02 02:00:00	8	3	INV-2001

Output

vendor_id	invoice_number	amount	copies	first_loaded_at
7	INV-1001	250	2	2024-05-02 01:10:00

Example 2no duplicates returns nothing

Input · invoices

amount	loaded_at	vendor_id	invoice_id	invoice_number
10	2024-05-01 00:00:00	7	1	INV-1
10	2024-05-01 00:00:00	7	2	INV-2

Output

no rows

Topics

deduplicationaggregation