SQL Find Duplicate Rows
One of the most-asked interview questions — group by whatever defines a duplicate, keep only groups with more than one row.
What & Why
A duplicate is defined by whichever columns should have been unique. Group by those columns, count the rows in each group, and keep only groups where that count exceeds 1.
See How It Works
BUSINESS QUESTION
Find acquisition channels that appear on more than one campaign row.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
Watch GROUP BY reveal a repeated campaign channelStep 1 of 3
GROUP BY channel HAVING COUNT(*) > 1
| campaign_id | name | channel |
|---|---|---|
| 1 | Spring Launch | google_ads |
| 2 | Retention Webinar | |
| 3 | Finance Retargeting | |
| 4 | Enterprise Search | google_ads |
Start from the four real marketing.campaigns rows.
EXAMPLE QUERY
SELECT
channel,
COUNT(*) AS occurrences,
MIN(start_date) AS first_seen_at,
MAX(start_date) AS last_seen_at
FROM marketing.campaigns
GROUP BY channel
HAVING COUNT(*) > 1
ORDER BY occurrences DESC, channel;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario