Drill 9 of 10 in Database Analyst
0

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…