How to calculate Contribution Margin in SQL

Why Contribution Margin matters

It is Friday afternoon and a finance partner pings: "Revenue is up nine percent quarter over quarter, but operating margin is flat. The CFO wants to know which products are subsidizing which by Monday morning." That question is a Contribution Margin question, not a gross margin question, and it shows up in analyst loops at Stripe, Snowflake, DoorDash, Uber, and Airbnb. Contribution Margin is revenue minus variable costs — the share of each sale that survives the costs that scale with the sale itself and is therefore available to cover fixed costs and produce profit.

It is the only ratio that lets you reason about whether a product, a segment, or a channel is worth keeping. Gross margin lumps cost of goods sold into one block; Contribution Margin breaks revenue against every cost that varies per unit — payment processing, shipping, refunds, referral payouts, per-call API spend on third-party models. A product can have a healthy gross margin and a negative Contribution Margin once you load in a 2.9 percent processor fee, a 6 percent referral discount, and an 11 percent return rate. This post walks the SQL from the base aggregation to a customer-segment cut, the cost-classification rules the interviewer wants to hear, the pitfalls that bite first-time authors, and the optimizations that keep the query fast.

The SQL formula

Contribution Margin for a product is the sum of revenue minus the sum of variable costs over the same set of orders, and the ratio is the dollar margin divided by revenue. Two columns drive the math — an orders table with revenue per order and a variable_costs table or column with the costs that scale with that order.

Contribution Margin = Revenue - Variable Costs
Contribution Margin Ratio = Contribution Margin / Revenue

The trap most analysts hit is borrowing the gross-margin query and calling it Contribution Margin. Gross margin subtracts cost of goods sold and stops. Contribution Margin subtracts every cost that moves with the unit — processor fees, shipping, fulfillment, referral payouts, per-call API spend on third-party model providers. Get the classification right and the SQL is a five-line aggregation; get it wrong and the slide tells the wrong story by a factor of two.

Contribution Margin by product

Assume three tables. orders(order_id, product_id, revenue, order_date) has one row per completed order. products(product_id, product_name) is the catalog. variable_costs(order_id, cost) has one row per order with the per-order variable cost rolled up — processor fee plus shipping plus per-call API spend plus any referral payout tied to that order. If your variable costs live in three separate tables, union them into one view; downstream queries are cleaner with a canonical per-order cost row.

SELECT
    p.product_name,
    SUM(o.revenue) AS revenue,
    SUM(vc.cost) AS variable_costs,
    SUM(o.revenue - vc.cost) AS contribution_margin,
    ROUND(
        SUM(o.revenue - vc.cost)::NUMERIC
            / NULLIF(SUM(o.revenue), 0),
        3
    ) AS cm_ratio
FROM orders o
JOIN products p USING (product_id)
JOIN variable_costs vc USING (order_id)
WHERE o.order_date >= '2026-01-01'
GROUP BY p.product_name
ORDER BY contribution_margin DESC;

The two SUM aggregates produce revenue and variable cost; their difference is the dollar Contribution Margin. The NULLIF guard on the denominator is non-negotiable — if a product had zero revenue (test SKU, deprecated entry, currency anomaly), the divide-by-zero blows up the whole query on a deadline.

The output is one row per product with three numbers. A negative contribution_margin means the product loses money on every sale — a kill candidate unless it is strategic loss-leading. A low positive cm_ratio (below 20 percent for SaaS, below 10 percent for a marketplace) is a margin-improvement candidate; raise prices, renegotiate processor rates, cut shipping subsidies. A high cm_ratio (above 70 percent SaaS, above 30 percent marketplace) is a scale-up candidate; pour acquisition spend into that product.

The INNER JOIN on variable_costs is doing more work than it looks. If an order is missing a cost row it disappears from the result entirely, silently understating revenue for any product whose cost capture is unreliable. Switch to a LEFT JOIN with COALESCE(vc.cost, 0) when you need a complete revenue view, then add a data-quality dashboard to flag orders missing cost rows.

Contribution Margin by customer segment

Product cuts answer "which SKU is healthy". Segment cuts answer "which kind of customer is healthy", the more important question once a sales motion is in flight. Enterprise customers usually carry a higher ratio because their order sizes amortize fixed per-order costs; the smallest customers carry the lowest ratio because processor fees and support cost eat the same share of a much smaller revenue base.

WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(revenue) AS total_revenue,
        SUM(variable_cost) AS total_variable_cost,
        SUM(revenue - variable_cost) AS contribution_margin
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY customer_id
),
segments AS (
    SELECT
        customer_id,
        CASE
            WHEN total_revenue > 100000 THEN 'enterprise'
            WHEN total_revenue > 10000 THEN 'mid_market'
            ELSE 'smb'
        END AS segment
    FROM customer_revenue
)
SELECT
    s.segment,
    COUNT(DISTINCT s.customer_id) AS customers,
    SUM(cr.total_revenue) AS revenue,
    SUM(cr.contribution_margin) AS total_cm,
    ROUND(AVG(cr.contribution_margin), 0) AS avg_cm_per_customer,
    ROUND(
        SUM(cr.contribution_margin)::NUMERIC
            / NULLIF(SUM(cr.total_revenue), 0),
        3
    ) AS cm_ratio
FROM segments s
JOIN customer_revenue cr USING (customer_id)
GROUP BY s.segment
ORDER BY total_cm DESC;

The two-CTE shape is canonical. The first CTE collapses orders to one row per customer; the second classifies each customer by revenue into a tier; the final aggregate rolls the tiers up. The thresholds are placeholders; replace them with whatever the sales team already uses. The point of this cut is that the row labels line up with the language on a sales leader's whiteboard.

The interview follow-up is the interpretation: "If enterprise carries a 65 percent ratio and SMB carries 30 percent, what do you do?" On the demand side, bias acquisition spend toward enterprise — every dollar of enterprise revenue keeps sixty-five cents while every dollar of SMB revenue keeps thirty. On the supply side, attack the per-order cost stack inside SMB — cheaper processor, higher minimum order thresholds, automated support for the tickets that inflate variable cost. Interviewers want to hear both, with a clear next-quarter priority.

The avg_cm_per_customer is the second line of the story. A segment with a high ratio but tiny dollar contribution per customer is low-leverage; a segment with a moderate ratio but huge dollar contribution per customer is where the money lives. Always pair the ratio with the absolute.

What counts as a variable cost

This is the question interviewers care about most. The SQL is mechanical; the cost classification separates a senior analyst from a junior one.

The definitively variable bucket: payment processing (a percentage per transaction), shipping for physical goods, cost of goods sold for physical goods, per-call API spend on third-party model providers, referral payouts that scale with new accounts, and discount or coupon redemptions. Each moves dollar for dollar with the unit.

The semi-variable bucket: customer support headcount and serverless infrastructure. Support correlates with order volume but is not strictly proportional — a single agent absorbs a wide range of ticket counts before the next hire, which arrives as a step function. Serverless (Lambda, Cloud Functions, Vercel Functions) scales per invocation but is usually billed at a level where marginal cost per order rounds to zero. Pragmatic rule: keep semi-variable costs out of the denominator unless you can attribute them per-order with a defensible model.

The fixed bucket: engineering and product salaries, office rent, brand marketing budget, research spend. None of these move when you sell one more unit. The junior mistake is loading support payroll into the variable cost table because the team interacts with customers. Salaries are a fixed commitment; including them confuses Contribution Margin with operating margin.

The one-sentence rule interviewers want: a variable cost is any cost line that goes up by a definite amount when one more unit ships. If the cost stays the same at one extra unit, it is fixed.

Common pitfalls

The most damaging pitfall is loading fixed costs into the variable cost table. Salaries are the prime offender — a careless analyst tags support payroll as a per-order cost and watches the Contribution Margin ratio collapse by fifteen points overnight. The fix is the one-more-unit test column by column, and routing fixed costs into a separate operating-margin query.

The second pitfall is ignoring payment processing. Stripe charges roughly 2.9 percent plus thirty cents per successful transaction; PayPal and most regional processors are in the same band. On a million-dollar product line that is twenty-nine thousand dollars a year of margin you are pretending does not exist. No Contribution Margin query that omits processor fees produces a number a CFO will sign.

The third pitfall is forgetting discounts. If orders.revenue is the list price rather than the captured amount net of coupons, every Contribution Margin number is overstated by exactly the discount rate. Use the net amount captured by the processor, not the catalog price, and document which warehouse column represents which.

The fourth pitfall is the silent divide-by-zero. A segment with zero revenue produces NaN or a query error depending on dialect, and the dashboard renders garbage either way. Wrap the denominator in NULLIF(SUM(revenue), 0) so the row returns NULL and the slide renders an em-dash instead of a panic.

The fifth pitfall is reporting only the company-wide aggregate. "Our Contribution Margin is thirty percent" hides every interesting fact in the business. One product can run at negative ten percent while another sits at sixty; the blend looks like a stable thirty. The cut is what tells you which lever to pull. Always present at least one disaggregation alongside the headline number.

Optimization tips

On a billion-row orders table the join to variable_costs is the first bottleneck. Partition orders on order_date — Snowflake and BigQuery do this natively; on Postgres declare monthly partitions explicitly. A "Q1 Contribution Margin" query then reads three partitions instead of every order since the company was founded — the difference between four seconds and four minutes.

The second lever is a pre-aggregated daily_product_economics table built by a dbt model overnight, shaped (date, product_id, revenue, variable_cost, orders_count). The Contribution Margin query becomes a small GROUP BY over a table orders of magnitude smaller than raw orders, and the same pre-aggregate powers Average Order Value and refund-rate queries.

The third lever is materializing the segment classification. The CASE WHEN total_revenue > 100000 clause is cheap on a million customers and expensive on fifty million. If tiers change rarely, push the classification into a customer_segments table refreshed nightly and join directly. The plan flattens from a nested aggregate to a simple join.

Related reading

• How to calculate COGS in SQL
• How to calculate AOV in SQL
• How to calculate ARPU in SQL
• How to calculate Cohort Revenue in SQL
• SQL window functions interview questions

If you want to drill product economics SQL daily, NAILDD is launching with 500+ SQL problems on this pattern.

FAQ

What is the difference between Contribution Margin and gross margin?

Gross margin subtracts cost of goods sold from revenue and stops. Contribution Margin subtracts every cost that varies with the unit — cost of goods sold plus payment processing, shipping, per-call API spend, referral payouts, and discount redemptions. Contribution Margin is therefore almost always smaller than gross margin for the same product. Both belong on the same dashboard: gross margin tells you whether the product is priced above its physical cost; Contribution Margin tells you whether unit economics survive the full per-order stack.

What is a healthy Contribution Margin ratio?

It depends on the industry, and interviewers expect a range rather than a single number. Pure SaaS sits between 70 and 90 percent because the marginal cost of one more subscription is mostly processor fees and a sliver of infrastructure. Marketplaces sit between 10 and 30 percent because the take rate caps the ceiling and most revenue flows through to the supply side. Physical retail sits between 30 and 50 percent. Below the floor, look for fixable cost lines; above the ceiling, expect skepticism about whether the cost capture is complete.

How does Contribution Margin connect to LTV and CAC payback?

Contribution Margin is the numerator of LTV. Lifetime Value is the discounted sum of future Contribution Margin per customer, not the discounted sum of future revenue. Most LTV mistakes trace back to using revenue instead of margin, which overstates long-term economics by exactly the variable-cost ratio. CAC payback is the number of months until cumulative per-customer Contribution Margin equals the customer acquisition cost. Both metrics fall apart if the Contribution Margin number is wrong.

Do I include refunds and chargebacks?

Yes, with a sign flip. A refund issued in May for a March charge is a May Contribution Margin event with a negative amount, charged against the March product or segment. Exclude refunds entirely and you overstate Contribution Margin by the refund rate, which on consumer commerce is often 8 to 15 percent. The canonical pattern is SUM(CASE WHEN status = 'success' THEN revenue WHEN status IN ('refunded', 'chargeback') THEN -revenue ELSE 0 END) with a parallel expression for cost.

Is this an official accounting metric?

No. Contribution Margin is a management accounting concept rather than a line on a 10-K, and the exact definition shifts between teams. The version here — revenue minus per-unit variable costs — is what analyst interviews ask for and what drives product, pricing, and channel decisions. A finance team preparing GAAP statements will use a stricter definition. Confirm which version your reader expects.