sql/02_quality.sql

-- Data quality checks. Each row is one check: what was tested and how many-- accounts it flags. Nothing is dropped here; the model layer decides how to-- treat each issue and the report states it. SELECT 'Accounts' AS check_name, COUNT(*) AS accounts FROM accountsUNION ALLSELECT 'Defaults in the following month', SUM(default_next_month) FROM accountsUNION ALLSELECT 'Duplicate account rows',       COUNT(*) - (SELECT COUNT(*) FROM (           SELECT DISTINCT credit_limit, sex, education, marital_status, age,                  status_sep, status_aug, status_jul, status_jun, status_may, status_apr,                  bill_sep, bill_aug, bill_jul, bill_jun, bill_may, bill_apr,                  paid_sep, paid_aug, paid_jul, paid_jun, paid_may, paid_apr, default_next_month           FROM accounts))FROM accountsUNION ALLSELECT 'Education code not in the documentation (0, 5, 6)',       SUM(education NOT IN (1, 2, 3, 4)) FROM accountsUNION ALLSELECT 'Marital status code not in the documentation (0)',       SUM(marital_status NOT IN (1, 2, 3)) FROM accountsUNION ALLSELECT 'September status coded -2 (undocumented)', SUM(status_sep = -2) FROM accountsUNION ALLSELECT 'September status coded 0 (undocumented)', SUM(status_sep = 0) FROM accountsUNION ALLSELECT 'Negative September bill (credit balance)', SUM(bill_sep < 0) FROM accountsUNION ALLSELECT 'September bill above the credit limit', SUM(bill_sep > credit_limit) FROM accountsUNION ALLSELECT 'No bill and no payment in all six months',       SUM(bill_sep = 0 AND bill_aug = 0 AND bill_jul = 0 AND bill_jun = 0 AND bill_may = 0 AND bill_apr = 0           AND paid_sep = 0 AND paid_aug = 0 AND paid_jul = 0 AND paid_jun = 0 AND paid_may = 0 AND paid_apr = 0)FROM accountsUNION ALLSELECT 'Of which flagged as default next month',       SUM(default_next_month = 1           AND bill_sep = 0 AND bill_aug = 0 AND bill_jul = 0 AND bill_jun = 0 AND bill_may = 0 AND bill_apr = 0           AND paid_sep = 0 AND paid_aug = 0 AND paid_jul = 0 AND paid_jun = 0 AND paid_may = 0 AND paid_apr = 0)FROM accounts;