How to calculate Gross Margin in SQL

Why Gross Margin matters

The growth team rolls out a flash promo — thirty percent off the electronics catalog. Revenue jumps fifty percent week over week and the channel lead drops a celebratory note. The finance partner does not react. The electronics catalog runs at a twelve percent Gross Margin; a thirty point discount on a twelve point margin sells every unit at a loss. By Monday morning the CFO at Stripe, DoorDash, or Snowflake wants a single chart of what happened to Gross Margin during the promo, and the analyst who can hand that chart over before standup earns the next interesting project.

Gross Margin is the first economic filter for any product line — does the price cover the cost of goods sold before anything else loads in? You cannot reason about operating margin, EBITDA, unit economics, or LTV without it. This post walks the SQL from the base aggregation through category and product cuts, Gross versus Contribution Margin, a month-over-month trend with a window function, the pitfalls that wreck early dashboards, and the optimizations that keep the query fast on a billion-row orders table.

The SQL formula

Gross Margin is revenue minus cost of goods sold, divided by revenue. Two columns drive the math — captured revenue per order and a per-unit COGS that reflects what the product cost on the day it shipped. Assume orders(order_id, item_id, quantity, price, cogs_per_item, created_at, status) with one row per line item.

Gross Margin = (Revenue - COGS) / Revenue
Gross Profit = Revenue - COGS

SELECT
    SUM(quantity * price) AS revenue,
    SUM(quantity * cogs_per_item) AS cogs,
    SUM(quantity * price) - SUM(quantity * cogs_per_item) AS gross_profit,
    ROUND(
        (SUM(quantity * price) - SUM(quantity * cogs_per_item))::NUMERIC
            * 100.0 / NULLIF(SUM(quantity * price), 0),
        2
    ) AS gross_margin_pct
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-04-01'
  AND created_at <  '2026-05-01';

The two SUM aggregates produce revenue and cost; their difference is dollar gross profit; the ratio expressed as a percent is the headline Gross Margin. The NULLIF guard is non-negotiable — a quiet month for a niche category can produce zero revenue and crash the dashboard otherwise. The hard constraint inside this query is that cogs_per_item is the snapshot at sale time. Stamping the current purchase price retroactively makes historical margins drift every time procurement renegotiates.

Gross Margin by category

A company-wide number is useful for a board slide and useless for any decision. The category cut is the first lever an analyst pulls because most pricing and merchandising decisions sit there — promo budgets, vendor renegotiations, end-cap allocations. Assume a products(item_id, item_name, category) catalog.

SELECT
    p.category,
    SUM(o.quantity * o.price) AS revenue,
    SUM(o.quantity * o.cogs_per_item) AS cogs,
    ROUND(
        (SUM(o.quantity * o.price) - SUM(o.quantity * o.cogs_per_item))::NUMERIC
            * 100.0 / NULLIF(SUM(o.quantity * o.price), 0),
        2
    ) AS gross_margin_pct
FROM orders o
JOIN products p ON p.item_id = o.item_id
WHERE o.status = 'paid'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.category
ORDER BY gross_margin_pct ASC;

Ordering ascending puts the worst categories at the top of the result — exactly where the eye lands first. A category running ten points below the company average is a pricing review candidate; a category ten points above is an acquisition-spend candidate. Most analytics teams keep a daily refresh of this query in a category_economics table.

Gross Margin by product

The category cut tells you where the money lives; the product cut tells you which SKUs are bleeding. The trap at the product level is small-N noise — a SKU with two sales last month can show an eighty percent Gross Margin from a one-time rebate and look like the most profitable line in the catalog. The HAVING filter on units sold separates real signal from rounding artifacts.

SELECT
    p.item_name,
    SUM(o.quantity) AS units_sold,
    SUM(o.quantity * o.price) AS revenue,
    ROUND(
        (SUM(o.quantity * o.price) - SUM(o.quantity * o.cogs_per_item))::NUMERIC
            * 100.0 / NULLIF(SUM(o.quantity * o.price), 0),
        2
    ) AS gross_margin_pct
FROM orders o
JOIN products p ON p.item_id = o.item_id
WHERE o.status = 'paid'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.item_id, p.item_name
HAVING SUM(o.quantity) >= 50
ORDER BY revenue DESC
LIMIT 100;

The HAVING SUM(quantity) >= 50 floor keeps the result honest. Choose the threshold by the catalog: a high-volume consumer-goods catalog wants two hundred units; a luxury catalog wants ten. The LIMIT 100 is for the dashboard; remove it for the full export. The output drives most merchandising conversations — which products earn shelf space, which lose it, which need a price increase.

Gross vs Contribution Margin

Interviewers at Stripe, Snowflake, and DoorDash almost always ask the follow-up: how is Gross Margin different from Contribution Margin? The honest answer is that Gross Margin stops at cost of goods sold while Contribution Margin keeps going. Contribution Margin loads payment processing fees, fulfillment, shipping, per-call API spend, and any variable operations cost into the same denominator.

SELECT
    SUM(quantity * price) AS revenue,
    SUM(quantity * cogs_per_item) AS cogs,
    SUM(quantity * variable_cost_per_item) AS variable_costs,
    ROUND(
        (SUM(quantity * price) - SUM(quantity * cogs_per_item))::NUMERIC
            * 100.0 / NULLIF(SUM(quantity * price), 0),
        2
    ) AS gross_margin_pct,
    ROUND(
        (SUM(quantity * price)
         - SUM(quantity * cogs_per_item)
         - SUM(quantity * variable_cost_per_item))::NUMERIC
            * 100.0 / NULLIF(SUM(quantity * price), 0),
        2
    ) AS contribution_margin_pct
FROM orders
WHERE status = 'paid';

A typical e-commerce business runs Gross Margin around forty percent and Contribution Margin around eight percent. That delta is the cost stack outside cost of goods sold — fulfillment, last-mile shipping, processor fees, return handling. The operating bottleneck is logistics, not pricing. Always pair the two metrics on the same slide, because Gross Margin alone hides the second-biggest cost line in the business.

Gross Margin trend

A point-in-time Gross Margin number tells you nothing about whether the business is improving. The trend cut answers the real question — is margin expanding, contracting, or stable? A window function over a monthly aggregate makes the month-over-month delta a single column instead of a separate query.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(quantity * price) AS revenue,
        SUM(quantity * cogs_per_item) AS cogs
    FROM orders
    WHERE status = 'paid'
      AND created_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    cogs,
    ROUND(
        (revenue - cogs)::NUMERIC * 100.0 / NULLIF(revenue, 0),
        2
    ) AS gm_pct,
    ROUND(
        (revenue - cogs)::NUMERIC * 100.0 / NULLIF(revenue, 0)
        - LAG((revenue - cogs)::NUMERIC * 100.0 / NULLIF(revenue, 0))
            OVER (ORDER BY month),
        2
    ) AS mom_change_pp
FROM monthly
ORDER BY month;

The mom_change_pp column is the change in margin in percentage points, not percent of percent — confusing the two is a common interview slip. A two-point monthly drop is a serious signal even when the absolute margin is still positive: four months of two-point drops takes a forty percent business to thirty-two and reshapes the operating plan for the year.

Common pitfalls

The most damaging pitfall is using the current purchase price as COGS for historical sales. Procurement renegotiates monthly, the catalog price feed updates, and yesterday's margin number changes overnight without any underlying business event. The fix is a cogs_per_item column written at order creation, locked from later mutation. If the warehouse only carries current cost, build a slowly-changing dimension on the products table and join by sale date.

The second pitfall is treating discounts as additions to COGS. A coupon redemption is a reduction in revenue, not an inflation of cost — revenue = quantity * (price - discount_per_item) and cogs is unchanged. Confusing the two double-counts the discount and overstates the margin compression. The dashboard ends up looking better than the business is performing, which is the worst kind of error in this domain.

The third pitfall is ignoring refunds. A refund returns revenue but the cost has already been incurred — the goods cannot be resold, the shipping is gone, the handling time is sunk. Net Gross Margin therefore subtracts both the returned revenue and any unrecoverable cost from the gross numbers. The clean pattern is to keep refunds as negative-quantity rows in the same orders table and let the same SUM aggregates handle both sides, with a separate refund_cost column that captures the unrecoverable portion.

The fourth pitfall is mixing services and physical goods inside a single query. A software subscription line runs at an eighty percent Gross Margin because the marginal cost is hosting and processor fees; a physical retail line runs at twenty-five percent because procurement and freight dominate. Reporting both as a single blended number produces a meaningless average that no operator can act on. Split the query by business line before any cut by category or product.

The fifth pitfall is ignoring the unit-mix effect. When headline Gross Margin drops three points the executive ask is always "why?" — and the answer usually decomposes into three drivers: price moved, cost moved, or mix shifted toward lower-margin categories. A clean decomposition holds two of the three constant while the third varies; without it the dashboard explains nothing.

Optimization tips

The first lever on a billion-row orders table is partitioning by created_at. Snowflake and BigQuery handle this natively through clustering; on Postgres declare monthly partitions explicitly. A "Q1 Gross Margin" query then scans three partitions instead of every order since the company was founded — three seconds against three minutes on the same hardware.

The second lever is a pre-aggregated daily_product_economics table maintained by a dbt model overnight, shaped (date, item_id, revenue, cogs, units). The category and product cuts become a small GROUP BY over a table orders of magnitude smaller than raw orders, and the same pre-aggregate powers Contribution Margin, Average Order Value, and refund-rate queries without re-scanning the source.

The third lever is materializing the products-to-category lookup. The catalog rarely changes; pushing the category onto each order row through a denormalized orders_enriched view eliminates the join entirely and the optimizer can use a single sequential scan with partition pruning. The trade is freshness — re-build the denormalized view on a schedule that matches catalog change cadence, usually nightly.

Related reading

• How to calculate Contribution Margin in SQL
• How to calculate COGS in SQL
• How to calculate AOV in SQL
• How to calculate EBITDA 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 a healthy Gross Margin?

It depends on the industry, and interviewers expect a range rather than a single number. Pure software and SaaS sit between seventy and eighty-five percent because the marginal cost of one more subscription is mostly hosting and processor fees. Physical e-commerce sits between twenty-five and forty-five percent because procurement and freight dominate. Consumer-packaged goods run fifteen to thirty. Restaurants target a sixty to seventy percent food cost, which translates to a thirty to forty percent Gross Margin on a menu item. Always compare against published peers in the same category before declaring a number good or bad.

Should the dashboard show Gross Margin or Contribution Margin?

Both, on the same page. Gross Margin is the right number for external financial reporting and for the board slide because it maps cleanly to public filings. Contribution Margin is the right number for operating decisions — whether to keep running a promo, which SKUs to retire, which channels to scale. A team that reports only Gross Margin will routinely sign off on revenue-positive, contribution-negative campaigns; a team that reports only Contribution Margin will struggle to reconcile with the finance close. Carry both.

What counts as COGS for a marketplace?

It depends on which side of the marketplace owns the unit. For the platform itself, COGS is payment processing, the per-order infrastructure cost, and any support effort directly attributable to the order. For the seller using the platform, COGS is the wholesale cost of the good plus inbound freight. Most marketplace analytics teams sidestep the classical Gross Margin entirely and report take rate over gross merchandise value, because the platform never owns the inventory. If the interviewer asks the question, lead with the take-rate framing and use Gross Margin only when discussing a specific seller cohort.

Gross Margin dropped five points — what is the response?

Decompose the drop into four candidate drivers before suggesting any action. First, cost — did procurement prices move, or did the currency a supplier invoices in shift against the base currency? Second, price — did a promo cut into list prices, or did competitive pressure force a structural reduction? Third, mix — did the share of sales rotate toward lower-margin categories without anything else changing? Fourth, waste — did the refund or shrinkage rate spike from a quality issue or a fulfillment problem? Each driver implies a different operating response, and naming the right one is what separates a senior analyst from a junior one in the interview loop.

How do I handle bundles or kits?

Allocate the bundle price across components proportionally to their standalone prices, and sum the component COGS for the bundle COGS. The allocation step matters because the bundle exists precisely to obscure per-unit pricing; without the allocation every component looks more profitable than it is. The clean schema is a bundle_components table mapping each bundle to its members with a weight column carrying the allocation ratio, and a view that explodes bundle orders into component-level revenue rows before the standard query runs.