Data-quality checks

• Has the columns check_name, problems
• Returns 6 rows
• Equals the expected table
• Does not change any data

-- Data-quality checks: one row per check, with how many rows break it.
-- 0 is good. Add a check for every rule your data must keep.
SELECT 'payments: an order paid more than once' AS check_name, count(*) AS problems
FROM (SELECT order_id FROM payments WHERE status = 'paid' GROUP BY order_id HAVING count(*) > 1) d
UNION ALL
SELECT 'orders: delivered but never paid', count(*)
FROM orders o
WHERE o.status = 'delivered' AND NOT EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id)
UNION ALL
SELECT 'orders: placed before the customer joined', count(*)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.ordered_at::date < c.joined_on
UNION ALL
SELECT 'customers: email missing or malformed', count(*)
FROM customers
WHERE email IS NULL OR email !~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$'
UNION ALL
SELECT 'customers: the same email twice', count(*)
FROM (SELECT lower(email) FROM customers GROUP BY 1 HAVING count(*) > 1) d
UNION ALL
SELECT 'stock: negative quantity', count(*)
FROM stock WHERE qty < 0
ORDER BY problems DESC, check_name;

-- The rows behind the first check: the orders paid twice.
SELECT p.order_id, count(*) AS payments, round(sum(p.amount_cents) / 100.0, 2) AS charged, min(p.paid_at) AS first_paid
FROM payments p
WHERE p.status = 'paid'
GROUP BY p.order_id
HAVING count(*) > 1
ORDER BY p.order_id;