Duplicate Detection Report — Solution & ExplanationProblems / Duplicate Detection Report / EditorialApproach

GROUP BY guest_email, checkin_date — the composite natural key. Alice appears four times in the table, but only three of her bookings share 2024-06-01; her 2024-06-10 booking is legitimate.

HAVING COUNT(*) > 1 keeps only keys that occur multiple times. HAVING (not WHERE) is required because the predicate references an aggregate, which only exists after grouping.

Return the key plus COUNT(*) as dup_count, ordered by dup_count DESC, guest_email.

Key InsightDetection before deletion: reporting duplicates with GROUP BY key HAVING COUNT(*) > 1 is the standard audit query run *before* any destructive dedup, so the team can inspect what would be removed. Getting the natural key right is the whole game — grouping by guest_email alone or checkin_date alone both over-report (this dataset punishes each mistake). In PySpark, HAVING is expressed as a .filter() applied after .agg().Hints

The natural key is the PAIR (guest_email, checkin_date) — group by both columns. Grouping by email alone over-counts guests who booked several different dates.

WHERE cannot see aggregate results; HAVING COUNT(*) > 1 filters the groups after aggregation.

In PySpark there is no separate HAVING: groupBy both keys, agg F.count(...).alias('dup_count'), then .filter(F.col('dup_count') > 1).

Solve Duplicate Detection Report yourself →