Which active customer accounts have no active owner?

Define the decision and input records

The join accepts only active staff while the WHERE clause limits the customer side to active accounts. That preserves an account whose named owner is inactive, instead of accidentally dropping it. The issue label separates a missing assignment from an unusable assignment because their follow-up actions differ.

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 c.id,c.owner_id,
 CASE WHEN c.owner_id IS NULL THEN 'unassigned' ELSE 'inactive_or_missing' END AS issue
FROM customer_accounts c LEFT JOIN staff s ON s.id=c.owner_id AND s.active=1
WHERE c.active=1 AND s.id IS NULL ORDER BY c.id;

C3 is unassigned and needs an ownership decision. C4 refers to inactive S2 and needs reassignment. C5 is inactive and is intentionally excluded. For a handover, confirm the receiving colleague and transfer only the authorised customer work.

Apply the result within its limits

This checks coverage, not workload, availability or access rights. A system with teams, multiple owners or temporary deputies needs explicit responsibility rules and a different relationship table.

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.