IntermediateCTEs & Recursive

CTE for deduplication using ROW_NUMBER

The query

SQL

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC
    ) AS rn
  FROM customers
)
SELECT * FROM ranked WHERE rn = 1;

Tested against PostgreSQL 16

Note

“CTE + ROW_NUMBER is the standard deduplication pattern. Keeps latest record per duplicate group.

Tables referenced

customers6 cols