Find Duplicate Email Addresses - Medium SQL Distinct Interview Problem

Problem

Your data team suspects the contacts table has duplicate email addresses from multiple import jobs. Find every email that appears more than once and report how many times it appears. Return email and occurrence_count. Sort by occurrence_count descending, then by email ascending to break ties.

Schema

contacts

column	type
id	INTEGER
name	TEXT
email	TEXT
phone	TEXT

Sample Data

id	name	email	phone
1	Alice	alice@example.com	555-0001
2	Bob	bob@example.com	555-0002
3	Alicia	alice@example.com	555-0003
4	Robert	bob@example.com	555-0004
5	Bobby	bob@example.com	555-0005

Expected Output

email	occurrence_count
bob@example.com	3
alice@example.com	2

Explanation

bob@example.com appears three times and alice@example.com appears twice. Emails that appear only once (none in this sample) are excluded.

Hints