find_duplicate.sql

-- Delete all duplicate entries that match 'PARTITION BY' section and are 'completed' and 'finished_at' falls between timestamps
DELETE FROM edge_scores AS a
WHERE a.id IN (
  SELECT id
  FROM (
         SELECT
           id,
           ROW_NUMBER()
           OVER (
             PARTITION BY user_id, assignment_id, finished_at::DATE, asset_id, score, completed
             ORDER BY id) AS rnum
         FROM edge_scores
         WHERE completed = TRUE
           AND finished_at between finished_at - interval '3 min' and finished_at + interval '3 min'
         ORDER BY id DESC) t
  WHERE t.rnum > 1
  ORDER BY id DESC);

-- Selects duplicate entries
SELECT *
FROM (
       SELECT
         *,
         ROW_NUMBER()
         OVER (
           PARTITION BY user_id, assignment_id, finished_at::DATE, asset_id, score, completed
           ORDER BY id) AS rnum
       FROM edge_scores
         WHERE completed = TRUE
         AND finished_at between finished_at - interval '5 min' and finished_at + interval '5 min'
       ORDER BY id DESC) t
WHERE t.rnum > 1
ORDER BY id DESC;