Database Analyst · Validation rules · OR · NULL-safe checks
4. Payments that break the rules
Concept: Validation rules · OR · NULL-safe checksshow
Data validation is a WHERE clause listing every way a row can be wrong, joined with OR. Each rule needs a NULL check of its own: NULL NOT LIKE '%@%' is NULL, not true, so a missing value slips past a pattern test unless you ask for IS NULL too.
Example (different data):
SELECT id, sku, qty, shipped_on FROM shipments WHERE qty <= 0 OR shipped_on > '2024-12-31' OR sku IS NULL OR sku NOT LIKE 'SKU-%';
Your task
The payments export taken on 2024-08-31 is about to be loaded into the ledger, and anything that breaks a business rule must go to a review queue first.
A payment is valid when its amount is greater than 0, paid_on is on or before 2024-08-31 (the export date), and payer_email is present and contains an @ with a dot somewhere after it (LIKE '%@%.%'). Return id, payer_email, amount and paid_on of every raw_payments row that breaks at least one rule, once per row. Sort by id.
Starting Postgres in your browser…
Checking your access…