Receipt Fraud Window Detection

Write a query to identify users who scanned receipts from the same retailer within a five-minute window, indicating potential fraud at Fetch.

Use the provided scan records and consider consecutive scans by the same user at the same retailer. Exclude scans with missing timestamps or unmatched users and retailers.

Output

1. One row per qualifying consecutive scan pair
2. Include user_id, user_name, retailer_id, retailer_name, both scan IDs and timestamps, and seconds_between
3. Order by user_id, then the later scan timestamp, then the later scan ID

Output details

• Return columns in this order: user_id, user_name, retailer_id, retailer_name, earlier_scan_id, later_scan_id, earlier_scanned_at, later_scanned_at, seconds_between.
• Treat the five-minute window as inclusive, allowing intervals exactly equal to 5 minutes.
• Order scans within each user and retailer by scanned_at ascending, breaking timestamp ties with scan_id ascending.
• Round seconds_between to the nearest whole second and return it as an integer.
• Sort the final results by user_id, later_scanned_at, and later_scan_id, all ascending.