Which active customer accounts share a normalised email?

Define the decision and input records

Normalise the comparison key with trim and lower; keep original values for investigation. Exclude missing and blank emails rather than grouping all unknown addresses into a fake duplicate cluster. The key is useful for finding candidates, but a shared mailbox may legitimately belong to several accounts.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE customer_accounts(id TEXT PRIMARY KEY, email TEXT, owner_id TEXT, active INTEGER);
CREATE TABLE staff(id TEXT PRIMARY KEY, active INTEGER);
INSERT INTO staff VALUES ('S1',1),('S2',0);
INSERT INTO customer_accounts VALUES ('C1',' Alex@example.test ','S1',1),('C2','alex@EXAMPLE.test','S1',1),('C3',NULL,NULL,1),('C4','bee@example.test','S2',1),('C5','old@example.test','S2',0);

Run the query and compare the result

SELECT LOWER(TRIM(email)) AS email_key,COUNT(*) AS account_count
FROM customer_accounts WHERE active=1 AND NULLIF(TRIM(email),'') IS NOT NULL
GROUP BY LOWER(TRIM(email)) HAVING COUNT(*)>1 ORDER BY email_key;

C1 and C2 produce alex@example.test, with an account count of two. C3’s missing address is not listed. Compare account ownership, order history and consent records before approving any merge; selecting the lowest ID is not an identity decision.

Apply the result within its limits

SQLite LOWER is sufficient for the ASCII sample, not a complete internationalised-email normalisation policy. Do not remove dots or plus tags across all providers. This read-only query does not decide which record survives.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.