Build a data-quality exceptions report with columns customerid, name, issuetype. One row per issue: 'missingscore' for…
SQLLevel: IntermediateServicing and risk analytics
Build a data-quality exceptions report with columns customer_id, name, issue_type. One row per issue: 'missing_score' for customers with a NULL credit score, and 'no_account' for customers with no accounts. A customer with both issues appears twice. KYC work is largely finding exactly these gaps.
Table definitions (from the source exercise):
CREATE TABLE customers(customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT, credit_score INTEGER);
CREATE TABLE accounts(account_id INTEGER PRIMARY KEY, customer_id INTEGER, product_type TEXT, open_date TEXT, credit_limit INTEGER, status TEXT);
Hints
- Distinguish a missing value from zero, an empty string and false.
- Test comparison, aggregation and filtering on a tiny missing-value example.
Answer
Approach: Two SELECTs with a literal issue_type column, glued with UNION ALL. The second one is the anti-join pattern.
SELECT customer_id, name, 'missing_score' AS issue_type
FROM customers
WHERE credit_score IS NULL
UNION ALL
SELECT c.customer_id, c.name, 'no_account' AS issue_type
FROM customers c
LEFT JOIN accounts a ON a.customer_id = c.customer_id
WHERE a.account_id IS NULL;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT