Skip to lesson
Exit
Capstone: Mock Loop1 / 2

1 min lesson

Drill B - usage → invoiced → recognized reconciliation

Talk through the example in "Drill B - usage → invoiced → recognized reconciliation", then name the result it is meant to produce.

Step 1 of 2

Drill B - usage → invoiced → recognized reconciliationThe three-way tie-out as one query

Reconcile rated usage against invoiced and recognized, per contract-month
WITH usage AS (
  SELECT contract_id, billing_period,
         SUM(rated_amount) AS usage_rated
  FROM usage_events_rated
  GROUP BY 1, 2
),
invoiced AS (
  SELECT contract_id, billing_period,
         SUM(line_amount) AS invoiced_amount
  FROM invoice_lines
  WHERE charge_type = 'usage'
  GROUP BY 1, 2
),
recognized AS (
  SELECT contract_id, billing_period,
         SUM(amount) AS recognized_amount
  FROM rev_subledger_events
  WHERE type = 'recognition'
  GROUP BY 1, 2
)
SELECT
  COALESCE(u.contract_id, i.contract_id, r.contract_id) AS contract_id,
  COALESCE(u.billing_period, i.billing_period, r.billing_period) AS billing_period,
  COALESCE(u.usage_rated, 0)       AS usage_rated,
  COALESCE(i.invoiced_amount, 0)   AS invoiced_amount,
  COALESCE(r.recognized_amount, 0) AS recognized_amount,
  COALESCE(u.usage_rated, 0)     - COALESCE(i.invoiced_amount, 0)   AS usage_vs_invoiced_diff,
  COALESCE(i.invoiced_amount, 0) - COALESCE(r.recognized_amount, 0) AS invoiced_vs_recognized_diff
FROM usage u
FULL OUTER JOIN invoiced i USING (contract_id, billing_period)
FULL OUTER JOIN recognized r USING (contract_id, billing_period)
WHERE ABS(COALESCE(u.usage_rated,0) - COALESCE(i.invoiced_amount,0)) > 0.01
   OR ABS(COALESCE(i.invoiced_amount,0) - COALESCE(r.recognized_amount,0)) > 0.01;
Watch out

The FULL OUTER JOIN and the COALESCE keys are deliberate. An inner join hides the scariest cases: usage that was metered but never invoiced or invoiced charges with no backing usage. Say that out loud - surfacing the rows that fall out of the join is the whole point of a reconciliation query.