SwadhyAI Notes

All SQL notes

👯Find duplicates

Define the business key first

Duplicates are multiset issues: group by the key vector you care about (often normalized, e.g. lower(email)), then filter with HAVING COUNT(*)> 1. Windows help list offending row ids; constraints prevent recurrence once you are clean.

SQL

Duplicate emails

SELECT lower(email) AS email_lc, COUNT(*) AS n
FROM users
GROUP BY lower(email)
HAVING COUNT(*) > 1;
✓Illustrative result set
email_lcn
ada@lab.example2
ops@lab.example3
SQL

List duplicate rows with ids

SELECT user_id, email
FROM (
  SELECT user_id, email,
         COUNT(*) OVER (PARTITION BY lower(email)) AS c
  FROM users
) t
WHERE c > 1
ORDER BY email, user_id;
✓Illustrative result set
user_idemail
12Ada@lab.example
44ada@lab.example

Delete without a keeper

  • Deleting duplicates without which row survives (max id, latest updated_at).
  • Treating join fan-out in a report as “duplicate data.”
  • ROW_NUMBER without partitioning on every business key column you care about.

Deterministic survivor

  • Dry-run with SELECT; document tie-breakers.
  • Add UNIQUE after cleanup; log duplicate rate as a data-quality metric.

Performance

High-cardinality groupings need memory, indexes on the dedupe key speed self-join deletes when you move from detection to remediation at scale.

TL;DR

To find duplicates, GROUP BY the natural or business key and HAVING COUNT(*) > 1, or use window functions like ROW_NUMBER() OVER (PARTITION BY …) to rank duplicates and keep one row. Decide whether “duplicate” means exact row twins or only repeated keys with conflicting payloads, cleanup SQL differs. Always snapshot or backup before mass deletes.