Как посчитать AR aging в SQL

Зачем AR aging

AR aging — стандарт финансового отчёта по дебиторке. Показывает, сколько денег «ждут» в каждой возрастной категории. 0-30 days — норма. 90+ days — high risk write-off. Помогает приоритизировать collections и прогнозировать bad debt.

Возрастные buckets

Стандарт:

• 0-30 days — current
• 31-60 days — past due
• 61-90 days — overdue
• 90+ days — high risk

Некоторые компании используют 0-15 / 16-30 / 31-60 / 61-90 / 90+ для greater granularity.

Aging в SQL

WITH outstanding AS (
    SELECT
        invoice_id,
        customer_id,
        amount,
        invoiced_at,
        EXTRACT(EPOCH FROM (CURRENT_DATE - invoiced_at)) / 86400 AS days_outstanding
    FROM invoices
    WHERE paid_at IS NULL
      AND status IN ('open', 'overdue')
)
SELECT
    CASE
        WHEN days_outstanding <= 30  THEN '0-30 days'
        WHEN days_outstanding <= 60  THEN '31-60 days'
        WHEN days_outstanding <= 90  THEN '61-90 days'
        ELSE '90+ days'
    END AS aging_bucket,
    COUNT(*) AS invoices,
    SUM(amount) AS total_ar,
    SUM(amount) * 100.0 / SUM(SUM(amount)) OVER () AS pct_of_total
FROM outstanding
GROUP BY 1
ORDER BY MIN(days_outstanding);

Health distribution: 60% в 0-30, 25% в 31-60, 10% в 61-90, 5% в 90+. Skewed right = risk.

По customer

Top «aged» customers:

SELECT
    customer_id,
    customer_name,
    COUNT(*) AS open_invoices,
    SUM(amount) AS total_ar,
    SUM(CASE WHEN days_outstanding > 90 THEN amount ELSE 0 END) AS high_risk_amount,
    SUM(CASE WHEN days_outstanding > 90 THEN amount ELSE 0 END) * 100.0
    / NULLIF(SUM(amount), 0) AS high_risk_pct
FROM outstanding
JOIN customers USING (customer_id)
GROUP BY customer_id, customer_name
ORDER BY high_risk_amount DESC
LIMIT 20;

Top 20 — collections team priority list.

Прогноз bad debt

Используя historical rates на каждый bucket:

WITH historical_rates AS (
    SELECT '0-30 days' AS bucket, 0.005 AS write_off_rate UNION ALL
    SELECT '31-60 days', 0.02 UNION ALL
    SELECT '61-90 days', 0.10 UNION ALL
    SELECT '90+ days',   0.40
),
current_aging AS (
    SELECT
        bucket,
        SUM(amount) AS ar_amount
    FROM aged_invoices
    GROUP BY bucket
)
SELECT
    c.bucket,
    c.ar_amount,
    h.write_off_rate,
    c.ar_amount * h.write_off_rate AS estimated_bad_debt
FROM current_aging c
JOIN historical_rates h USING (bucket);

Sum estimated bad debt = ожидаемый bad debt allowance.

Частые ошибки

Ошибка 1. From invoice date vs due date. Aging стандартно от due_date (когда payment due), не от invoiced_at. Если у вас Net-30, payment due через 30 days.

Ошибка 2. Включать paid invoices. Filter paid_at IS NULL обязательно.

Ошибка 3. Игнорировать disputed. Customer disputed amount не считается «overdue» в обычном смысле. Categorize отдельно.

Ошибка 4. Один rate для всех customers. Enterprise customers с track record на 30 days delays — не risk. Small/new — другой rate.

Ошибка 5. Aging без context customer payment history. 90+ days от customer who всегда платит на 95 дней — норма (его pattern). 90+ от usual-30-days — flag.

Связанные темы

• Как посчитать DSO в SQL
• Как посчитать bad debt rate в SQL
• Как посчитать working capital в SQL
• Как посчитать payment success rate в SQL

FAQ

Aging from invoice date или due date?

Standard — from due date. Это «overdue» semantically.

Какая доля свыше 90 дней допустима?

Меньше 3% от total AR. Свыше 5% — внимание.

Bad debt write-off threshold?

US GAAP: после 180 days — обычно write-off. Зависит от accounting policy.

Collections strategy?

0-30: email reminder. 31-60: phone call. 61-90: collection agency warning. 90+: escalate.

Aging для prepaid?

Prepaid не в AR. Это deferred revenue.