Data Quality and Validation Questions

Both forms answer "which rows are duplicates" but only the window-function form also tells you which single row to keep, so use `GROUP BY`/`HAVING` for a quick existence check and `ROW_NUMBER()` when you need to actually resolve to one canonical row.

GROUP BY / HAVING form

```sql
SELECT email, COUNT() AS n
FROM accounts
GROUP BY email
HAVING COUNT() > 1;
```

ROW_NUMBER window-function form

```sql
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
FROM accounts
)
SELECT account_id, email, created_at FROM ranked WHERE rn = 1;
```
The `PARTITION BY email` groups rows the same way `GROUP BY` would, but `ROW_NUMBER()` additionally assigns a rank within each group, so filtering to `rn = 1` (with `ORDER BY created_at DESC` to break ties by recency) keeps exactly one row per email and discards the rest, which the `GROUP BY` form alone cannot express in a single pass. The same window-function query can be dropped straight into a `DELETE ... WHERE rn > 1` (via a CTE or subquery, depending on the engine) to physically remove the duplicates.

Worked example

Given `accounts` with two rows for `a@x.com` (created 2026-01-01 and 2026-01-02) and one row each for `b@x.com` and `c@x.com`: the `GROUP BY` form returns one row, `(a@x.com, 2)`. The `ROW_NUMBER` form returns three rows: `a@x.com`'s later (2026-01-02) record plus `b@x.com` and `c@x.com`, correctly keeping the most recent `a@x.com` row and dropping the older one.

Trade-offs and pitfalls

An exact-string duplicate definition misses near-duplicates caused by casing or whitespace (`'A@x.com'` vs `'a@x.com '`); normalize with `LOWER(TRIM(email))` in the partition key if that's a real risk in your data. Ties on the ORDER BY column (two rows with the identical `created_at`) make the `rn = 1` choice arbitrary unless you add a deterministic tie-breaker, like the primary key, as a second ORDER BY term. Finally, this check generalizes well: parametrize the table name and key columns and you can run the same query across many tables rather than writing one bespoke check per table.

Guard the denominator explicitly and pick a deliberate default for the zero case, rather than letting the database either error or silently return NULL, since a silent NULL is easy to miss downstream in a dashboard that just shows a blank tile.

Approach

```sql
SELECT campaign_id, conversions, impressions,
CASE WHEN impressions = 0 THEN 0.0 ELSE ROUND(conversions * 100.0 / impressions, 2) END AS conversion_rate_pct,
(impressions = 0) AS zero_denominator
FROM camp;
```
Returning an explicit `0.0` plus a separate `zero_denominator` boolean flag is more honest than either erroring or silently returning NULL: a dashboard consuming this can choose to grey out the tile using the flag, rather than displaying a bare `0.00%` that looks identical to "the rate really is zero."

Worked example (verified by execution)

Given campaigns with (10 conversions, 1000 impressions), (0, 0), and (3, 0): the query returns `1.0%` with `zero_denominator = false` for the first, and `0.0%` with `zero_denominator = true` for the other two, correctly distinguishing "no data yet" from "a real, low rate."

Trade-offs and pitfalls

For a campaign with a genuinely tiny number of impressions (say 3 conversions out of 4 impressions), a raw ratio is technically defined but statistically meaningless; add-one (Laplace) smoothing, computing `(conversions + 1) / (impressions + 2)`, pulls small-sample estimates toward a more conservative middle value and avoids reporting a wildly overconfident 75% rate off of 4 data points. Whether smoothing is appropriate depends on the audience: a raw rate is more transparent for an engineer debugging a specific campaign, while a smoothed rate is safer for an automated ranking or alerting system that shouldn't be fooled by small-sample noise.