Database Analyst · FULL OUTER JOIN · IS DISTINCT FROM
9. Reconcile billing and CRM
Concept: FULL OUTER JOIN · IS DISTINCT FROMshow
Reconciling two systems means finding keys missing from either side and keys whose values disagree. FULL OUTER JOIN keeps unmatched rows from both tables, and IS DISTINCT FROM compares values safely: NULL <> 29 is NULL (so the row is missed), but NULL IS DISTINCT FROM 29 is true.
Example (different data):
SELECT COALESCE(w.sku, s.sku) AS sku, w.qty, s.qty FROM warehouse_stock w FULL OUTER JOIN store_stock s ON s.sku = w.sku WHERE w.sku IS NULL OR s.sku IS NULL OR w.qty IS DISTINCT FROM s.qty;
Your task
RevOps closes the month by checking that billing and the CRM agree on every account's plan and MRR before reporting revenue to the board.
Compare billing_accounts with crm_accounts by account_id. Plans match if they're equal after trimming and lowercasing; mrr must be identical, and NULL on one side only is a mismatch. For every account that is missing from either side or doesn't match, return account_id, billing_plan, crm_plan, billing_mrr, crm_mrr (values as stored, NULL when the side is missing) and issue: 'missing_in_crm', 'missing_in_billing' or 'mismatch'. Sort by account_id.
Starting Postgres in your browser…
Checking your access…