tracksolid_timescale_grafana_prod/db_audit/checks/duplicate_positions.sql

-- Duplicate (imei, gps_time) pairs in position_history
-- Should always return 0 rows if ON CONFLICT DO NOTHING is working correctly
SELECT
    imei,
    gps_time,
    COUNT(*) AS duplicate_count
FROM tracksolid.position_history
WHERE gps_time > NOW() - INTERVAL '7 days'
GROUP BY imei, gps_time
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;