Audit duplicate scans

For scanner events, return package_id, event_type, warehouse_id, copies and spread_minutes (minutes between the first and last copy, rounded to 0 decimals) for every package, event type and warehouse scanned more than once within 15 minutes of its first scan.

what to return

Columns: package_idevent_typewarehouse_idcopiesspread_minutes in this order
Rows: 2362 rows · any order
Watch for: Copies are scanner events with the same package_id, event_type and warehouse_id whose event_at is within 15 minutes of the earliest such event.
Skill: Profile duplicates before removing them. Refresher: Deduplicate event retries →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result2362 rows · any order

package_id	event_type	warehouse_id	copies	spread_minutes
1	warehouse_departure	4	2	11
3	picked_up	3	2	12
6	warehouse_arrival	22	2	13

first 3 of 2362 rows

data you can query

Logistics dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.

tracking_events9 columns · 100,515 rows

event_idnumber · unique id	package_idnumber · links to packages	event_typetext	event_atdate & time	recorded_atdate & time	warehouse_idnumber · links to warehouses	event_sourcetext	exception_codetext	driver_idnumber
1	6359	created	2025-01-01 00:03:46	2025-01-01 00:04:36	NULL	manual	NULL	NULL
2	10950	created	2025-01-01 00:33:12	2025-01-01 00:35:16	NULL	manual	NULL	NULL
3	2045	created	2025-01-01 00:35:00	2025-01-01 00:37:08	NULL	manual	NULL	NULL
4	5977	created	2025-01-01 00:47:34	2025-01-01 00:48:13	NULL	manual	NULL	NULL
5	4930	created	2025-01-01 00:55:09	2025-01-01 00:57:10	NULL	manual	NULL	NULL
6	6331	created	2025-01-01 01:34:23	2025-01-01 01:35:30	NULL	manual	NULL	NULL

event_idnumber · unique id: 1
package_idnumber · links to packages: 6359
event_typetext: created
event_atdate & time: 2025-01-01 00:03:46
recorded_atdate & time: 2025-01-01 00:04:36
warehouse_idnumber · links to warehouses: NULL
event_sourcetext: manual
exception_codetext: NULL
driver_idnumber: NULL

event_idnumber · unique id: 2
package_idnumber · links to packages: 10950
event_typetext: created
event_atdate & time: 2025-01-01 00:33:12
recorded_atdate & time: 2025-01-01 00:35:16
warehouse_idnumber · links to warehouses: NULL
event_sourcetext: manual
exception_codetext: NULL
driver_idnumber: NULL

event_idnumber · unique id: 3
package_idnumber · links to packages: 2045
event_typetext: created
event_atdate & time: 2025-01-01 00:35:00
recorded_atdate & time: 2025-01-01 00:37:08
warehouse_idnumber · links to warehouses: NULL
event_sourcetext: manual
exception_codetext: NULL
driver_idnumber: NULL

event_idnumber · unique id: 4
package_idnumber · links to packages: 5977
event_typetext: created
event_atdate & time: 2025-01-01 00:47:34
recorded_atdate & time: 2025-01-01 00:48:13
warehouse_idnumber · links to warehouses: NULL
event_sourcetext: manual
exception_codetext: NULL
driver_idnumber: NULL

event_idnumber · unique id: 5
package_idnumber · links to packages: 4930
event_typetext: created
event_atdate & time: 2025-01-01 00:55:09
recorded_atdate & time: 2025-01-01 00:57:10
warehouse_idnumber · links to warehouses: NULL
event_sourcetext: manual
exception_codetext: NULL
driver_idnumber: NULL

event_idnumber · unique id: 6
package_idnumber · links to packages: 6331
event_typetext: created
event_atdate & time: 2025-01-01 01:34:23
recorded_atdate & time: 2025-01-01 01:35:30
warehouse_idnumber · links to warehouses: NULL
event_sourcetext: manual
exception_codetext: NULL
driver_idnumber: NULL

first 6 of 100,515 rows · your queries use all of them

solution.sql

Hints and the answer are always available. Using them counts the question as solved with help.