The duplicate-rows query you re-Google every six weeks

There is a query nobody memorises and everybody needs: find the rows that are duplicated. A customer signed up twice, an import ran twice, a join fanned out and doubled every row. The answer has been the same for thirty years — GROUP BY the suspect columns, HAVING COUNT(*) > 1 — and yet if you are not writing SQL daily you look it up every single time, because the shape is just unusual enough to not stick.

-- Which emails appear more than once?
SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

The trap is not difficulty — it is that the shape changes under you

It bites in quiet ways. Group by the wrong columns and you under- or over-count — the grain of the query is the definition of "duplicate," and it is easy to pick the wrong one. And the moment you want the whole duplicate row rather than just the duplicated key, the query you Googled stops being the query you need. GROUP BY collapses each group to one summary row; to keep every offending row you reach for a window function instead:

-- Keep the full rows, tag the extras
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at
         ) AS rn
  FROM customers
) t
WHERE rn > 1;

Same question — "where are my duplicates?" — two structurally different queries, and which one you want depends on whether you need the count or the rows. It is a yes/no question wearing a SQL costume, and the costume changes every time.

Ask in English — then read the SQL it ran

This is exactly the case for asking in plain English and reading the SQL it generates. Not because SQL is beneath you — because the grain matters here and you want to verify it. "Which customers appear more than once by email?" should hand you back both the rows and the GROUP BY email HAVING COUNT(*) > 1 it ran, so you can confirm it grouped on the column you meant before you trust the count. A chat model can write you that query; it cannot run it against your data, and if you paste rows into a prompt and ask it to count, it will confidently hallucinate the tally.

(That is the half we built nlqdb for: ask the duplicate question in English over a Postgres it provisions, or one you already run via a signed-in connect, and get the rows plus the compiled SQL. Honest split — it reports duplicates with a read-only query; which row to keep and how to merge is a write you run deliberately, and matching is exact, not fuzzy.)