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

Зачем DSO

Days Sales Outstanding — среднее число дней от выставления invoice до оплаты. Главный cash flow indicator для B2B. DSO 30 — отлично, 45 — норма, 90+ — cash тонут в receivables. Высокий DSO = need credit line или slowdown в expansion.

Формула

Стандарт (period-based):

DSO = (Average AR / Revenue) × Days in period

Или per-invoice basis:

DSO_per_invoice = paid_at − invoiced_at

DSO в SQL

WITH paid_invoices AS (
    SELECT
        invoice_id,
        amount,
        invoiced_at,
        paid_at,
        EXTRACT(EPOCH FROM (paid_at - invoiced_at)) / 86400 AS days_to_pay
    FROM invoices
    WHERE paid_at IS NOT NULL
      AND paid_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    COUNT(*) AS paid_invoices,
    AVG(days_to_pay) AS avg_dso,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_pay) AS median_dso,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY days_to_pay) AS p95_dso
FROM paid_invoices;

Median ≈ payment terms (30 или 60 days). P95 — worst case.

По customer

SELECT
    customer_id,
    COUNT(*) AS invoices,
    AVG(days_to_pay) AS avg_dso,
    SUM(amount) AS total_revenue
FROM paid_invoices_with_customer
WHERE paid_at >= CURRENT_DATE - INTERVAL '180 days'
GROUP BY customer_id
HAVING COUNT(*) >= 5
ORDER BY avg_dso DESC
LIMIT 20;

Топ DSO — chronic late payers. Collections team focus.

AR aging

Сколько в receivables разбито по возрасту:

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
FROM outstanding
GROUP BY 1
ORDER BY MIN(days_outstanding);

90+ days bucket — high-risk receivables. Часто здесь bad debt начинается.

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

Ошибка 1. Mean vs median DSO. Late outliers tail right. Median honestly.

Ошибка 2. Включать unpaid invoices. DSO считается на paid. Unpaid — отдельно через AR aging.

Ошибка 3. Не сегментировать на payment terms. Net-30 и Net-60 contracts имеют разные expectations. Сегментируйте.

Ошибка 4. Считать на one customer skewed sample. Один whale invoice $1M с 45 days делает DSO кажется хуже. Weight or filter.

Ошибка 5. Игнорировать FX / multi-currency. Multi-region — конверсия на consistent rate, не on payment date.

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

• Как посчитать bad debt rate в SQL
• Как посчитать working capital в SQL
• Как посчитать cash conversion cycle в SQL
• Как посчитать chargeback rate в SQL

FAQ

Какой DSO хороший?

B2C SaaS 1-7 days (auto-charge). B2B SMB 30-45. Enterprise 60-90.

DSO растёт — что значит?

Customers платят медленнее. Investigate: economy, your collections team, payment friction.

DSO vs payment terms?

Payment terms — что в contract (Net-30). DSO — фактический результат.

Average AR formula?

(Beginning AR + Ending AR) / 2 для period. Прецизионнее — daily average.

Cash discount помогает?

Yes — «2/10 net 30» (2% discount если pay within 10 days) ускоряет on 10-15 days.