Cash Conversion Cycle in SQL

What Cash Conversion Cycle measures

Picture this. A finance director DMs you on Slack at 9pm: "Auditor wants Cash Conversion Cycle by quarter for the last two years, decomposed by component, by tomorrow standup." You open the ledger schema, see four tables that vaguely look like inventory, AR, AP, and revenue, and start to sweat. CCC is one of those metrics that sounds simple, behaves treacherously, and ships to the audit committee with a magnifying glass attached.

Cash Conversion Cycle captures how many days a dollar of working capital is tied up inside the operating cycle. The flow is intuitive once you draw it. The company buys raw materials or merchandise, holds it as inventory, sells it, waits for the customer to pay, and meanwhile owes its suppliers on a separate clock. CCC asks: from the moment cash leaves the door to pay a supplier, how long until the same dollar comes back from a customer? Lower is better. Negative is rare and excellent.

Why finance teams care. CCC drives the working capital line on the balance sheet. A company with a 90-day CCC finances three months of operations every period. A company with a negative CCC, like Amazon during its prime era, has customers funding the business for free. When equity analysts compare two retailers with similar revenue, the one with shorter CCC has structurally lower capital needs and higher return on invested capital. That single ratio swings valuation multiples and often flags a liquidity event before it shows up in net income.

The CCC formula

The textbook decomposition has three parts, all expressed in days:

CCC = DIO + DSO - DPO

DIO (Days Inventory Outstanding) = 365 * Avg Inventory / COGS
DSO (Days Sales Outstanding)     = 365 * Avg AR        / Revenue
DPO (Days Payable Outstanding)   = 365 * Avg AP        / COGS

DIO is the number of days inventory sits before it is sold. DSO is the number of days the customer takes to pay after the invoice. DPO is the number of days the company waits before paying suppliers. The sign convention is the key insight: cash leaves when the company pays suppliers (DPO is a credit), and cash returns when customers settle invoices (DSO is a debit).

The denominators matter as much as the numerators. Revenue belongs only in the DSO line. Cost of Goods Sold belongs in both DIO and DPO because those balances are stocked and paid at cost, not at sale price. Mixing them is the single most common mistake on a take-home. Interviewers at Stripe and Snowflake love to watch a candidate divide AR by COGS and then explain why the result is nonsense.

A worked SQL example

Below is a clean baseline computation that assumes a monthly snapshot table called monthly_financials with month, inventory_value, cogs, accounts_receivable, accounts_payable, and revenue. The CTE averages balances and sums flows across a trailing twelve-month window, then derives the three day-count components and their sum.

WITH financial_stats AS (
    SELECT
        AVG(inventory_value)       AS avg_inventory,
        SUM(cogs)                  AS annual_cogs,
        AVG(accounts_receivable)   AS avg_ar,
        SUM(revenue)               AS annual_revenue,
        AVG(accounts_payable)      AS avg_ap
    FROM monthly_financials
    WHERE month >= CURRENT_DATE - INTERVAL '12 months'
)
SELECT
    365 * avg_inventory / NULLIF(annual_cogs, 0)    AS dio,
    365 * avg_ar        / NULLIF(annual_revenue, 0) AS dso,
    365 * avg_ap        / NULLIF(annual_cogs, 0)    AS dpo,
    (365 * avg_inventory / NULLIF(annual_cogs, 0))
    + (365 * avg_ar      / NULLIF(annual_revenue, 0))
    - (365 * avg_ap      / NULLIF(annual_cogs, 0)) AS ccc
FROM financial_stats;

Read the query top to bottom. The AVG aggregates over snapshot balances are intentional. A balance is a stock, not a flow, so averaging twelve monthly snapshots gives a more representative figure than picking either the first or the last reading. Conversely, COGS and revenue are flow items that should be summed over the same window. The NULLIF guards exist for the early-stage case where annual revenue or COGS can be zero in seed quarters; without them, the division would throw and a dashboard would render as a red bar.

A grounded example: imagine a mid-market apparel brand with average inventory of 12 million dollars, average AR of 8 million, average AP of 6 million, annual COGS of 48 million, and annual revenue of 90 million. DIO works out to about 91 days, DSO to about 32 days, DPO to about 46 days, and CCC lands at roughly 77 days. That means every dollar of working capital stays trapped in the operating cycle for two and a half months before recycling. To run that business, the brand needs a credit line large enough to cover roughly two and a half months of operating expenses on top of equity.

Quarterly trends and decomposition

A single CCC number is a snapshot. The real signal lives in the trend. The query below recomputes CCC quarterly so a stakeholder can see whether the company is improving its working capital efficiency or quietly bleeding cash. Note the substitution of 91.25 days per quarter in place of 365 to keep the units honest.

WITH quarterly AS (
    SELECT
        DATE_TRUNC('quarter', month) AS quarter,
        AVG(inventory_value)         AS avg_inv,
        SUM(cogs)                    AS quarterly_cogs,
        AVG(accounts_receivable)     AS avg_ar,
        SUM(revenue)                 AS quarterly_rev,
        AVG(accounts_payable)        AS avg_ap
    FROM monthly_financials
    GROUP BY 1
)
SELECT
    quarter,
    91.25 * avg_inv / NULLIF(quarterly_cogs, 0) AS dio,
    91.25 * avg_ar  / NULLIF(quarterly_rev,  0) AS dso,
    91.25 * avg_ap  / NULLIF(quarterly_cogs, 0) AS dpo,
    (91.25 * avg_inv / NULLIF(quarterly_cogs, 0))
    + (91.25 * avg_ar  / NULLIF(quarterly_rev,  0))
    - (91.25 * avg_ap  / NULLIF(quarterly_cogs, 0)) AS ccc
FROM quarterly
ORDER BY quarter;

When you visualize the output, plot all three components on one chart alongside the composite CCC line. The decomposition is what makes CCC actionable. If overall CCC rose ten days year over year, the chart immediately tells you whether the culprit is bloated inventory, slower collections, or a supplier pulling back on credit terms. Each root cause leads to a different remediation playbook, and a single CCC number cannot distinguish between them.

A second pattern worth adding is a rolling four-quarter trailing version. Quarterly numbers in retail are seasonal, and Q4 inventory builds before holiday will dominate any annual comparison. A trailing-twelve-months view smooths the seasonality. The OVER (ORDER BY quarter ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) construction is the standard tool, pairing naturally with the quarterly CTE above.

Common pitfalls

When a finance team computes CCC for the first time, the most common mistake is to use end-of-period balances instead of average balances. A snapshot taken on December 31 reflects whatever inventory the warehouse happens to hold that night, which may include a deliberate year-end drawdown or a stock-up for January promotions. The fix is to compute average balances across the period in question, either as a simple average of monthly snapshots or, for more granularity, a daily moving average from the general ledger. The difference between EOP and average can easily be ten to twenty days for a seasonal business.

A second trap is to include non-trade items in AR or AP. Accounts receivable on the balance sheet usually mixes customer trade receivables with tax refunds receivable, intercompany balances, and employee advances. Accounts payable likewise blends trade payables to suppliers with accrued income tax and lease obligations. CCC only makes sense on trade balances tied to the operating cycle. The fix is to filter for an account_type IN ('trade_ar', 'trade_ap') flag in the source ledger; if that flag does not exist, join to a chart of accounts and pull only the trade lines.

A third pitfall is to interpret a negative CCC as an error. It is not. Amazon, Apple, and Costco have all reported negative CCC at various points in their history. The mechanism is straightforward: a business with strong negotiating leverage can stretch DPO past the point where DIO and DSO catch up, effectively having customers and suppliers fund operations together. When you see a negative CCC, the right question is whether the negotiating leverage is sustainable.

A fourth pitfall is to annualize a single quarter by multiplying by four. If a stakeholder asks for annual CCC and you only have Q1 data, the trailing twelve months from prior quarters is the better source. Multiplying one quarter by four assumes flat seasonality, which is rarely the case in retail, apparel, agriculture, or anything tied to weather. If TTM data is unavailable, at minimum disclose the period you used and let the reader weight accordingly.

A fifth pitfall is to skip the decomposition entirely and report only the composite CCC. A finance committee may approve a working capital initiative that targets DSO when the real problem is DIO, simply because the deck never decomposed the number. Always present DIO, DSO, and DPO alongside CCC, and show the trend over at least eight quarters so seasonality is visible.

Optimization tips

For a finance warehouse that runs CCC nightly, the bottleneck is rarely arithmetic. It is the join between the monthly financial table and the chart of accounts. Materialize the filtered trade-only AR and AP balances in a daily snapshot table, and let the CCC query read from that pre-filtered view rather than re-filtering the raw ledger each run. On Snowflake or Databricks, a clustering key on (month, account_type) will prune unread micropartitions.

If the ledger is partitioned by fiscal period, push the date filter into the partition predicate rather than wrapping it in a function. WHERE month >= '2025-01-01' lets the planner skip partitions; WHERE EXTRACT(YEAR FROM month) >= 2025 blocks pruning. For visualization, precompute the rolling-four-quarter CCC in a materialized view refreshed weekly. Dashboards that recompute window functions on every page load are a common source of slow finance tooling.

Related reading

• SQL window functions interview questions
• How to calculate AR aging in SQL
• How to calculate burn rate in SQL
• How to calculate burn multiple in SQL

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What CCC is considered good?

A negative CCC, in the range of -10 to -30 days, is exceptional and usually appears at companies with extreme supplier leverage like Amazon or Costco. A CCC between 30 and 60 days is healthy for most consumer-facing businesses and aligns with the median for retail and consumer packaged goods. Once CCC drifts past 90 days, the business is capital-intensive and will require a sizable revolving credit facility or strong cash reserves to operate comfortably. Industry context matters: a heavy-equipment manufacturer with 180-day CCC is normal; a software-as-a-service business with the same number would be alarming.

What does a negative CCC actually mean?

It means the company collects cash from its customers before it has to pay its suppliers. Walk through the cycle: a customer pays on the spot at checkout, the supplier waits 45 or 60 days for an invoice to settle, and inventory turns fast enough that the net effect is the company holds cash that does not belong to it for a stretch. That stretch is free working capital. It funds growth, share buybacks, or new product development without needing equity or debt. Sustaining a negative CCC requires both pricing power with customers and negotiating power with suppliers, which is why so few companies achieve it.

CCC is growing quarter over quarter. Is that bad?

Generally yes. A growing CCC means cash is staying trapped in operations longer, which forces the company to either raise capital, draw on credit lines, or slow growth. The exception is during a deliberate inventory build ahead of a product launch or a seasonal peak. In those cases, DIO will rise temporarily, and the decomposition will tell the story. If DSO is the line creeping up, the issue is collections discipline or customer credit quality. If DPO is shrinking, suppliers may be tightening terms because they sense risk. Each diagnosis points to a different fix.

How should I drill into CCC by component?

Start with a trend chart of DIO, DSO, and DPO across eight to twelve quarters; the component with the steepest slope is the one to investigate first. For DIO, drill into inventory by SKU class to find slow-movers. For DSO, segment AR by customer cohort and aging bucket to surface late payers. For DPO, segment AP by supplier tier and contract terms to see whether the change is driven by mix or by renegotiation. The CCC formula gives the entry point; segment analysis gives the action item.

How do I compute CCC for a services business with no inventory?

Drop the DIO term and report the cycle as DSO minus DPO. Pure services firms like consulting agencies or law firms typically run a DSO of 45 to 75 days, with DPO largely reflecting payroll and overhead timing. The metric to watch is net working capital intensity per dollar of revenue, which the DSO-minus-DPO version captures cleanly. Some analysts add a quasi-DIO line for work-in-progress on long-running engagements, treating unbilled time as inventory; that is defensible when WIP is material.

What window length should I use for the balance averages?

Twelve months is the standard for annual reporting and matches how external auditors expect to see the number. For internal management cadence, a trailing three-month average smooths monthly snapshot noise without lagging reality. The shortest useful window equals the period over which the underlying flows are aggregated. Averaging balances across a quarter while summing COGS across the year produces a CCC that is mathematically incoherent even though the query returns a number.