remove_duplicates.sql

WITH device_details AS (
SELECT 'deviceA' AS unique_id, '{"data":"deviceA"}' AS data, '2022-02-03 11:00:00' as event_time
UNION ALL select 'deviceA', '{"data":"deviceA"}', '2022-02-03 11:00:00' --duplicate
UNION ALL select 'deviceB', '{"data":"deviceB"}', '2022-02-02 10:00:00'
UNION ALL select 'deviceA', '{"data":"deviceA"}', '2022-02-02 10:00:00' 
UNION ALL select 'deviceB', '{"data":"deviceB"}', '2022-02-02 10:00:00' --duplicate
UNION ALL select 'deviceC', '{"data":"deviceC"}', '2022-02-04 10:00:00'
UNION ALL select 'deviceB', '{"data":"deviceB"}', '2022-02-02 10:00:00' --duplicate
),

remove_duplicates as (
SELECT
unique_id 
, data
, event_time 
, ROW_NUMBER() OVER(PARTITION BY unique_id, event_time order by event_time) AS row_details
FROM device_details  
)

SELECT * FROM remove_duplicates WHERE row_details = 1