<!-- persona-dash wiki source
     doc_id:   database/chapter-5-data-quality-part-2
     title:    Chapter 5: Data Quality Part 2
     subtitle: Duplicates
-->

### Duplicates

**Duplicates** = Two or more rows that describe the same real thing. The same person entered twice for example.

"The same thing" is the decision we make ourselves, and we have to state it. What does "Same person" mean? Same name and same date of birth? Same email? The data cannot tell us which definition is right - it can only tell us how many duplicates each definition produces.

#### Step 1: Which values repeat

Groups that appear more than once:

```SQL
SELECT first_name, last_name, date_of_birth, COUNT(*) AS n
FROM tutorial_library.member
GROUP BY first_name, last_name, date_of_birth,
HAVING COUNT (*) > 1
ORDER BY n DESC;
```

```GROUP BY``` collapses rows that share the same values into one output row per group, so we can count or total each group.

```WHERE``` vs ```HAVING```: 
```WHERE``` filters rows *before* grouping. ```HAVING``` filters groups *after* grouping. The duplicate test has to be in ```HAVING``` because the count doesn't exist until the grouping has happened.

#### Step 2: Get the actual rows back

Grouping tells us a duplicate exists but throws away the individual rows. A window function gets both in one pass:

```SQL
SELECT * 
FROM (
  SELECT m.*,
  COUNT(*) OVER (PARTITION BY first_name, last_name, date_of_birth) AS group_size,
  ROW_NUMBER() OVER (PARTITION BY first_name, last_name, date_of_birth ORDER BY member_id) AS copy_no
FROM tutorial_library.member m
) t
WHERE group_size > 1
ORDER BY last_name, first_name, copy_no;
```

```OVER (PARTITION BY...)``` means "Do this calculation separately within each group, and keep every row in the output." That last part is the difference from ```GROUP BY```, which collapses each group into one row.

```ROW_NUMBER()``` Numbers the rows within each group: 1,2,3. Combined with ```ORDER BY member_id```, row 1 is the earliest record. Rows numbered 2 and above are the extra copies.

#### Step 3: Decide what counts as "The Same"

Exact matching misses most duplicates. ```JOHN SMITH``` and ```John Smith``` and ```john smith``` ar ethe same person and different strings. Clean up the values before comparison.

```SQL
SELECT LOWER(TRIM(first_name)) AS f,
       LOWER(TRIM(last_name)) AS l,
       date_of_birth, 
       COUNT(*) AS n,
       GROUP_CONCAT(member_id ORDER BY member_id) AS ids
FROM tutorial_library.member
GROUP BY f, l, date_of_birth
HAVING COUNT(*) > 1
ORDER BY n DESC;
```

```LOWER()``` makes everything lower case so cases stop mattering. ```GROUP CONCAT()``` returns the member IDs in each group as a comma-separated list, so we can quote exact row identifiers without running a second query.