Delete duplicate rows keeping the most recent one

-- ======================================================================
-- Q: How do you delete duplicates while keeping the most recent record?
--
-- I actually did this in the ASP migration, where the email was not unique and
-- there were duplicate customers with different letter case. Before the DELETE,
-- however, I re-point the orders to the "surviving" customer (UPDATE orders SET
-- customer_id = ...), otherwise either the FK breaks or you lose history. I run
-- the SELECT first, count, and then run the DELETE inside a transaction.
-- ======================================================================

WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY lower(email)
                              ORDER BY updated_at DESC, id DESC) AS rn
    FROM customers
)
DELETE FROM customers c
USING ranked r
WHERE c.id = r.id AND r.rn > 1;

-- and then add protection so it does not happen again
CREATE UNIQUE INDEX CONCURRENTLY ux_customers_email_lower ON customers (lower(email));