Skip to content
RED SAGSkills
medium30 ptsDeduplicationWindow FunctionsStrings

Deduplicate Marketing Leads

Lead forms produced duplicates. Two leads are the same person when their emails match after trimming spaces and lowercasing. Leads with a NULL email are discarded.

Keep one lead per person: the earliest created_at, and if that ties, the smallest lead_id. Show the email in its normalised form (trimmed, lowercase).

Return columns lead_id, email, source. Row order does not matter.

Schema

CREATE TABLE leads (
  lead_id INTEGER PRIMARY KEY,
  email TEXT,
  source TEXT NOT NULL,
  created_at TEXT NOT NULL
);

Sample data

leads

lead_idemailsourcecreated_at
1maya@corp.comwebinar2026-03-01 10:00:00
2 Maya@Corp.com google_ads2026-02-20 09:00:00
3liam@shop.aelinkedin2026-03-02 12:00:00
4NULLwebinar2026-03-02 13:00:00
5noor@bank.aereferral2026-03-03 08:30:00
6LIAM@shop.aewebinar2026-03-05 16:00:00
Hint 1

Partition by the normalised email LOWER(TRIM(email)).

Hint 2

Filter out NULL emails before ranking, or every NULL lead lands in one group.

Good practice for: Data Engineer, Digital Marketing Specialist, Data Analyst, Backend Developer

SQL (SQLite)

Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.