SQL: what a CTE is and how to find duplicate records

-- ======================================================================
-- Q: What is a CTE? How do you find duplicate records?
--
-- A CTE (WITH) makes complex queries readable. It is ideal for cleaning legacy
-- data before a migration, which I did in practice. After the cleanup I add a
-- unique index on lower(email) so it does not happen again.
-- ======================================================================

WITH dup AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY id) AS rn
  FROM customer
)
SELECT * FROM customer WHERE id IN (SELECT id FROM dup WHERE rn > 1);