How to calculate AR aging in SQL
Contents:
What AR aging is and why finance reads it first
Accounts receivable aging is the report the CFO opens before any other working capital number. It takes every unpaid invoice, sorts each by how long it has been outstanding, and rolls the amounts into a small set of age buckets. The output is one table that tells you how much of your AR is healthy, how much is drifting, and how much is in serious trouble. At Stripe, Snowflake, and every other company that ships invoices, this is the first artifact in the weekly cash review.
The reason it matters is incentives. Sales celebrates when a contract is signed. Finance only celebrates when cash hits the bank. Between those two events sits a 30 to 120 day window where revenue is booked but not collected, and the aging report is the only number that honestly tracks that gap. When a Director of Finance at Databricks pings you on Slack asking "how much of AR is over 90 days before the board call", you need a SQL query that returns a clean answer.
This post walks through the SQL you ship: the base bucket query, the per-customer risk view, and the bad debt forecast that feeds the allowance for doubtful accounts. Every query is Postgres but translates cleanly to Snowflake, BigQuery, or Redshift.
The standard aging buckets
The default scheme that almost every accounting team in the United States uses splits AR into four buckets. Zero to thirty days is "current" — invoices in this window are still inside normal Net-30 terms and behave like working capital, not risk. Thirty-one to sixty days is "past due" — payment was expected and follow-up has started. Sixty-one to ninety days is "overdue" — the customer is materially late and the dunning sequence is escalating. Ninety days and beyond is "high risk", where historical write-off rates climb steeply.
Some companies operate with finer granularity. Marketplaces with weekly payout cycles often run 0-15, 16-30, 31-60, 61-90, and 90+. Enterprise SaaS firms with Net-60 or Net-90 terms extend to 120, 150, and 180 day buckets. The right scheme is whichever one separates "still inside the contracted terms" from "we have a collections problem" given your customer base. The one rule across schemes: boundaries are based on age past the due date, not age past the invoice date.
The data model we will use
For every example below, assume two tables — an invoices fact and a customers dimension.
invoices (invoice_id, customer_id, amount, currency,
invoiced_at, due_at, paid_at, status)
customers (customer_id, customer_name, segment,
account_owner, signed_at)status carries values like open, overdue, paid, disputed, voided. amount is in USD after FX conversion upstream. due_at is the contractual payment deadline (typically invoiced_at + 30 days for Net-30); paid_at is null until cash lands.
The base aging query
The base query has two parts. A CTE computes days past due for every unpaid invoice; a CASE expression then bins those days into the four buckets, paired with a SUM and a window function for the percent of total.
WITH outstanding AS (
SELECT
invoice_id,
customer_id,
amount,
invoiced_at,
due_at,
EXTRACT(EPOCH FROM (CURRENT_DATE - due_at)) / 86400
AS days_past_due
FROM invoices
WHERE paid_at IS NULL
AND status IN ('open', 'overdue')
AND due_at <= CURRENT_DATE
)
SELECT
CASE
WHEN days_past_due <= 30 THEN '0-30 days'
WHEN days_past_due <= 60 THEN '31-60 days'
WHEN days_past_due <= 90 THEN '61-90 days'
ELSE '90+ days'
END AS aging_bucket,
COUNT(*) AS open_invoices,
SUM(amount) AS total_ar,
ROUND(SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (), 2)
AS pct_of_total
FROM outstanding
GROUP BY 1
ORDER BY MIN(days_past_due);Three details matter. The filter due_at <= CURRENT_DATE excludes invoices still inside their terms. The window function SUM(SUM(amount)) OVER () grosses each bucket as a percent of the total without a second pass. The ORDER BY MIN(days_past_due) keeps buckets in natural order regardless of string sort.
A healthy distribution is roughly 60 percent in 0-30, 25 percent in 31-60, 10 percent in 61-90, and 5 percent in 90+. Skew to the right is the first signal of collection problems. Track the share of AR over 90 days — anything above 5 percent earns a slide in the next finance review.
Aging by customer
The bucket distribution is the headline. The follow-up is always "which customers are driving the 90+ pile?" Collections cannot dial a bucket — they need a ranked list of accounts. The query below pulls the top twenty customers by amount in the 90+ bucket and reports both the absolute exposure and the share of each customer's own AR that is high risk.
WITH outstanding AS (
SELECT
i.invoice_id,
i.customer_id,
i.amount,
EXTRACT(EPOCH FROM (CURRENT_DATE - i.due_at)) / 86400
AS days_past_due
FROM invoices i
WHERE i.paid_at IS NULL
AND i.status IN ('open', 'overdue')
AND i.due_at <= CURRENT_DATE
)
SELECT
c.customer_id,
c.customer_name,
c.segment,
c.account_owner,
COUNT(*) AS open_invoices,
SUM(o.amount) AS total_ar,
SUM(CASE WHEN o.days_past_due > 90 THEN o.amount
ELSE 0 END) AS high_risk_amount,
ROUND(
SUM(CASE WHEN o.days_past_due > 90 THEN o.amount
ELSE 0 END) * 100.0
/ NULLIF(SUM(o.amount), 0), 2
) AS high_risk_pct
FROM outstanding o
JOIN customers c USING (customer_id)
GROUP BY c.customer_id, c.customer_name, c.segment, c.account_owner
HAVING SUM(CASE WHEN o.days_past_due > 90 THEN o.amount
ELSE 0 END) > 0
ORDER BY high_risk_amount DESC
LIMIT 20;This is the list collections operates against. Including account_owner matters because escalation flows through whichever rep owns the relationship — a generic dunning email to an enterprise contact is the fastest way to lose a renewal. Including segment matters because the playbook differs: an SMB account at 120 days past due gets a final notice and service suspension; a strategic enterprise account with the same exposure gets a CFO-to-CFO call.
Two useful extensions: group the query by account_owner to see which reps carry the most exposure, and add a rolling count of how many quarters each customer has appeared on the top-twenty list. Repeat offenders deserve a credit policy review.
Forecasting bad debt from aging
Aging buckets feed directly into the allowance for doubtful accounts — the contra-asset that estimates how much AR will never be collected. Under both US GAAP and IFRS, the dominant approach is the aging method: apply a historical write-off rate to each bucket and sum. The query below assumes a historical_rates lookup captured from your own collections history.
WITH outstanding AS (
SELECT
amount,
CASE
WHEN EXTRACT(EPOCH FROM (CURRENT_DATE - due_at)) / 86400
<= 30 THEN '0-30 days'
WHEN EXTRACT(EPOCH FROM (CURRENT_DATE - due_at)) / 86400
<= 60 THEN '31-60 days'
WHEN EXTRACT(EPOCH FROM (CURRENT_DATE - due_at)) / 86400
<= 90 THEN '61-90 days'
ELSE '90+ days'
END AS bucket
FROM invoices
WHERE paid_at IS NULL
AND status IN ('open', 'overdue')
AND due_at <= CURRENT_DATE
),
historical_rates AS (
SELECT '0-30 days' AS bucket, 0.005 AS write_off_rate UNION ALL
SELECT '31-60 days', 0.020 UNION ALL
SELECT '61-90 days', 0.100 UNION ALL
SELECT '90+ days', 0.400
),
current_aging AS (
SELECT bucket, SUM(amount) AS ar_amount
FROM outstanding
GROUP BY bucket
)
SELECT
c.bucket,
c.ar_amount,
h.write_off_rate,
ROUND(c.ar_amount * h.write_off_rate, 2) AS estimated_bad_debt
FROM current_aging c
JOIN historical_rates h USING (bucket)
ORDER BY MIN(h.write_off_rate);The rates above are illustrative defaults for a B2B SaaS book. Real rates need to come from your own write-off history — typically the last twelve to twenty-four months. Backtest the table: take every invoice that was 31-60 days past due one year ago, count how much was ultimately written off versus paid, and use that as the empirical rate. Recompute quarterly.
The sum of estimated_bad_debt feeds the allowance journal entry; the delta between this quarter and last hits the income statement as bad debt expense. When the CFO sees a bump in bad debt expense, this is the first table they click.
Common pitfalls
The most common mistake on aging reports is computing days outstanding from invoiced_at rather than due_at. If your terms are Net-30 and an invoice was issued thirty days ago, days-from-invoice is thirty but days-past-due is zero. The whole report drifts thirty days to the right. The fix is to anchor on due_at and filter out invoices whose due_at is still in the future. The report is about lateness, not elapsed time.
A second trap is forgetting to filter paid invoices. The defensive pattern is paid_at IS NULL AND status IN ('open', 'overdue'), plus a weekly check that the union of buckets reconciles to the AR total on the balance sheet.
A third trap is mixing disputed invoices into the report. A disputed invoice is in an active commercial process, not delinquent. Bucketing it as 90+ inflates the high-risk number and sends collections after accounts where the right action is a credit memo. Track disputes in a separate disputed_ar slice and roll them up alongside aging, not inside it.
A fourth trap is using a single write-off rate across all segments. A Fortune 500 customer delayed by internal AP cycles is not the same risk as an SMB account at the same age — the enterprise account almost always pays. Compute write-off rates per segment and apply them in the forecast.
A fifth trap is ignoring per-customer payment history. A customer who consistently pays around day ninety-five is not high risk at day ninety-two — that is their pattern. A customer who usually pays at day twenty-eight showing up at day ninety-two is a red flag. Compute each customer's median days-to-pay and flag invoices whose current age materially exceeds that baseline.
Optimization tips
For warehouses with invoice counts in the tens of millions, a partial index on invoices(due_at) filtered to WHERE paid_at IS NULL keeps the scan tiny — the index only contains genuinely open invoices. In Snowflake the equivalent is clustering on due_at; in BigQuery, partition by invoiced_at and prune with a date filter.
Most finance dashboards do not need real-time aging — end-of-day truth is plenty. Schedule the base query at midnight and write its output to an ar_aging_daily table keyed by snapshot date. Dashboards read from the materialized table and respond in milliseconds. Historical snapshots also let you chart aging mix over time, the chart that answers "is our AR getting healthier?"
Finally, compute the bucketing in dbt rather than ad hoc in BI tools. Embed the CASE expression in a single ar_aging model and have every dashboard, every Slack alert, and every monthly close pack read from it. The moment finance, operations, and the board are looking at three different aging tables, the report has lost its purpose.
Related reading
If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.
FAQ
Should aging be measured from the invoice date or the due date?
From the due date. The whole point of the report is to track lateness, and an invoice with thirty-day terms is not late until day thirty-one past issuance. Anchoring on invoice date systematically shifts your buckets to the right and overstates how much of AR is in trouble. The single exception is when a customer has no contractual terms, in which case the invoice date functions as a de facto due date.
What share of AR over 90 days is acceptable?
For a healthy B2B SaaS book, less than three percent of total AR should sit in the 90+ bucket on any snapshot. Five percent is the threshold at which most finance teams escalate to leadership. Above ten percent and you have a structural collections problem. These benchmarks shift down for consumer marketplaces, where payment cycles are shorter, and up for enterprise software where Net-60 terms are standard.
When does an invoice become a write-off?
Under US GAAP, the trigger is when collection is "probable" not to occur — there is no fixed calendar threshold. In practice, most companies write off invoices after 180 days past due, after a final notice, and after a collections agency has reviewed and declined the account. Whatever your policy is, write it down and apply it consistently. Auditors will ask.
What is the standard collections cadence by bucket?
Inside 0-30 past due, automated email reminders at day three, seven, and fifteen. Between 31 and 60, a phone call from the account owner. Between 61 and 90, a formal dunning letter referencing the contract and warning of service suspension. Beyond 90, escalate to a collections agency or to legal, and suspend service. Pressure escalates with age; automation should not extend past day thirty.
Do prepayments belong in the AR aging report?
No. Prepayments sit on the balance sheet as deferred revenue, not AR. They are a liability until you deliver the service. AR aging tracks cash invoiced but not yet collected, and the two metrics should reconcile to different balance sheet lines.
How does AR aging connect to days sales outstanding (DSO)?
DSO summarizes the aging report into a single number: average days between invoicing and collection, as a fraction of recent revenue. Aging is the distribution; DSO is the mean. Watching DSO alone hides the shape of the tail — you can have a deteriorating 90+ bucket while DSO looks stable, because large recent invoices in the 0-30 bucket keep the average down. Read both: DSO for the trend, aging for the diagnosis.