Revenue Reconciliation
Query Set

WITH payment_totals AS (
    SELECT
        invoice_id,
        SUM(amount_paid)  AS total_paid,
        MAX(payment_date) AS last_payment_date
    FROM payments
    GROUP BY invoice_id
),
credit_totals AS (
    SELECT
        invoice_id,
        SUM(memo_amount) AS total_credits
    FROM credit_memos
    GROUP BY invoice_id
)
SELECT
    a.account_name,
    i.invoice_id,
    i.amount_due,
    COALESCE(pt.total_paid, 0)                AS total_paid,
    COALESCE(ct.total_credits, 0)             AS credits_applied,
    i.amount_due
        - COALESCE(pt.total_paid, 0)
        - COALESCE(ct.total_credits, 0)       AS net_balance_due,
    ROUND(COALESCE(pt.total_paid,0) / NULLIF(i.amount_due,0) * 100, 2) AS pct_paid,
    CURRENT_DATE - i.due_date                 AS days_past_due
FROM invoices i
JOIN accounts a     ON i.account_id = a.account_id
LEFT JOIN payment_totals pt ON i.invoice_id = pt.invoice_id
LEFT JOIN credit_totals  ct ON i.invoice_id = ct.invoice_id
WHERE
    (i.amount_due
        - COALESCE(pt.total_paid, 0)
        - COALESCE(ct.total_credits, 0)) > 0.01
ORDER BY net_balance_due DESC;

WITH invoice_fingerprint AS (
    SELECT
        invoice_id, account_id,
        product_line, billing_period_start,
        billing_period_end, amount_due,
        -- Count invoices per account + product + period
        COUNT(*) OVER (
            PARTITION BY
                account_id, product_line,
                billing_period_start, billing_period_end
        )                          AS period_invoice_count
    FROM invoices
    WHERE status NOT IN ('void', 'written_off')
)
SELECT
    a.account_name,
    f.product_line,
    f.billing_period_start,
    f.billing_period_end,
    f.period_invoice_count         AS duplicate_count,
    SUM(f.amount_due)              AS total_billed,
    MIN(f.amount_due)              AS expected_charge,
    SUM(f.amount_due) - MIN(f.amount_due) AS overcharge_amount,
    STRING_AGG(f.invoice_id, ', ') AS invoice_ids
FROM invoice_fingerprint f
JOIN accounts a ON f.account_id = a.account_id
WHERE f.period_invoice_count > 1
GROUP BY
    a.account_name, f.product_line,
    f.billing_period_start, f.billing_period_end,
    f.period_invoice_count
ORDER BY overcharge_amount DESC;

WITH annualised_billing AS (
    SELECT
        account_id,
        SUM(amount_due) AS actual_arr_billed
    FROM invoices
    WHERE
        invoice_date >= DATE_TRUNC('year', CURRENT_DATE)
        AND status NOT IN ('void', 'written_off')
    GROUP BY account_id
)
SELECT
    a.account_name,
    a.tier,
    c.contracted_arr,
    ab.actual_arr_billed,
    ab.actual_arr_billed - c.contracted_arr    AS arr_variance,
    ROUND(
        (ab.actual_arr_billed - c.contracted_arr)
        / NULLIF(c.contracted_arr, 0) * 100, 2
    )                                          AS variance_pct,
    CASE
        WHEN ABS(ab.actual_arr_billed - c.contracted_arr)
             / NULLIF(c.contracted_arr,0) > 0.05 THEN 'REVIEW REQUIRED'
        WHEN ab.actual_arr_billed > c.contracted_arr THEN 'OVER-BILLED'
        WHEN ab.actual_arr_billed < c.contracted_arr THEN 'UNDER-BILLED'
        ELSE 'IN LINE'
    END                                        AS billing_status,
    -- Surface renewal risk at same time
    CASE
        WHEN c.contract_end <= CURRENT_DATE + INTERVAL '90 days'
             AND c.auto_renew = FALSE THEN 'RENEWAL RISK'
        ELSE NULL
    END                                        AS renewal_flag
FROM contracts c
JOIN accounts a           ON c.account_id = a.account_id
LEFT JOIN annualised_billing ab ON c.account_id = ab.account_id
WHERE c.contract_end >= CURRENT_DATE
ORDER BY ABS(arr_variance) DESC;

WITH aging AS (
    SELECT 'Aging Receivables' AS leakage_type,
        COUNT(DISTINCT invoice_id) AS record_count,
        SUM(amount_due) AS amount_at_risk
    FROM invoices
    WHERE status NOT IN ('paid','void','written_off')
      AND CURRENT_DATE > due_date
),
short_pay AS (
    SELECT 'Short Payments' AS leakage_type,
        COUNT(DISTINCT i.invoice_id) AS record_count,
        SUM(i.amount_due - COALESCE(p.total_paid,0)) AS amount_at_risk
    FROM invoices i
    LEFT JOIN (SELECT invoice_id, SUM(amount_paid) AS total_paid
               FROM payments GROUP BY invoice_id) p
        ON i.invoice_id = p.invoice_id
    WHERE i.status NOT IN ('void','written_off')
      AND COALESCE(p.total_paid,0) BETWEEN 0.01 AND i.amount_due - 0.01
),
duplicates AS (
    SELECT 'Duplicate Charges' AS leakage_type,
        COUNT(*) AS record_count,
        SUM(overcharge_amount) AS amount_at_risk
    FROM (
        SELECT SUM(amount_due) - MIN(amount_due) AS overcharge_amount
        FROM invoices
        WHERE status NOT IN ('void','written_off')
        GROUP BY account_id, product_line,
                 billing_period_start, billing_period_end
        HAVING COUNT(*) > 1
    ) dup
)
SELECT
    leakage_type,
    record_count,
    ROUND(amount_at_risk, 2)             AS amount_at_risk,
    ROUND(
        amount_at_risk / SUM(amount_at_risk) OVER () * 100, 1
    )                                    AS pct_of_total_risk
FROM (
    SELECT * FROM aging
    UNION ALL
    SELECT * FROM short_pay
    UNION ALL
    SELECT * FROM duplicates
) combined
ORDER BY amount_at_risk DESC;