Find duplicate rows

-- Single-column dupes (e.g., duplicate emails).
SELECT email, COUNT(*) AS dup_count
FROM   users
GROUP  BY email
HAVING COUNT(*) > 1;

-- Multi-column dupes (e.g., same name + signup date).
SELECT name, created_at, COUNT(*) AS dup_count
FROM   users
GROUP  BY name, created_at
HAVING COUNT(*) > 1;

-- Whole-row dupes (group by every column).
SELECT id, email, name, created_at, COUNT(*) AS dup_count
FROM   users
GROUP  BY id, email, name, created_at
HAVING COUNT(*) > 1;

-- See the duplicate rows themselves (not just counts) — using ROW_NUMBER.
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
) ranked
WHERE rn > 1;

-- Duplicates after normalization (case-insensitive, whitespace-trimmed).
SELECT LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS dup_count
FROM   users
GROUP  BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

-- Duplicates within a recent window only.
SELECT email, COUNT(*) AS dup_count
FROM   users
WHERE  created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP  BY email
HAVING COUNT(*) > 1;

-- "True" duplicates including row count beyond the simple GROUP BY (every column).
WITH counted AS (
  SELECT u.*,
         COUNT(*) OVER (PARTITION BY id, email, name, created_at) AS row_count
  FROM users u
)
SELECT * FROM counted WHERE row_count > 1;

-- Find duplicate composite keys in a fact table (data quality check).
SELECT order_id, product_id, COUNT(*) AS dup_count
FROM   ecom_order_items
GROUP  BY order_id, product_id
HAVING COUNT(*) > 1;