Find Duplicates
What you're building
A query that finds members who borrowed the same book more than once β duplicate detection is a classic data-quality problem that shows up in analytics, ETL, and fraud detection.
Requirements
Return rows from loans where the same member_id borrowed the same book_id on multiple occasions. Show:
member_namebook_titleborrow_countβ how many times that member borrowed that book
Only include pairs with borrow_count > 1. Order by borrow_count descending.
Tables: members(id, name) Β· books(id, title) Β· loans(id, member_id, book_id, loan_date, return_date)
Hint: GROUP BY member_id, book_id then HAVING COUNT(*) > 1.
Stretch goals
- List all loan dates for each duplicate pair using
GROUP_CONCAT(loan_date). - Extend to find any member who has more than 2 active loans at once.
What this proves
HAVING-filtered aggregation for duplicate detection β a pattern that recurs in data engineering interviews, fraud analytics, and database health checks.
β‘ 60 XP
query.sqlRuns in your browser (SQLite/WASM)
Loading editorβ¦
Tests1 to pass
Member-book pairs borrowed more than once
Run your code to check these.