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

Зачем Operating Margin

Operating Margin = Operating Income / Revenue. Показывает efficiency core operations. Без учёта interest и taxes. Hub финансовых KPIs.

Формула

Operating Margin = (Revenue - COGS - Opex) / Revenue × 100%
                 = Operating Income / Revenue × 100%

Operating Income (EBIT) = Revenue - COGS - SG&A - R&D - depreciation

Базовый расчёт

WITH monthly_pnl AS (
    SELECT
        DATE_TRUNC('month', DATE) AS month,
        SUM(revenue) AS revenue,
        SUM(cogs) AS cogs,
        SUM(sga + r_n_d + depreciation) AS opex
    FROM financial_data
    WHERE DATE >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    month,
    revenue,
    cogs,
    opex,
    revenue - cogs AS gross_profit,
    revenue - cogs - opex AS operating_income,
    (revenue - cogs - opex)::NUMERIC * 100 / NULLIF(revenue, 0) AS operating_margin_pct
FROM monthly_pnl
ORDER BY month;

Gross vs Operating vs Net

WITH pnl AS (
    SELECT
        SUM(revenue) AS rev,
        SUM(cogs) AS cogs,
        SUM(sga + r_n_d + depreciation) AS opex,
        SUM(interest + taxes) AS below_line
    FROM financial_data
    WHERE DATE >= '2026-01-01' AND DATE < '2026-04-01'
)
SELECT
    rev,
    (rev - cogs) * 100.0 / rev AS gross_margin_pct,
    (rev - cogs - opex) * 100.0 / rev AS operating_margin_pct,
    (rev - cogs - opex - below_line) * 100.0 / rev AS net_margin_pct
FROM pnl;

Тренды

SELECT
    DATE_TRUNC('quarter', DATE) AS quarter,
    SUM(revenue - cogs - sga - r_n_d - depreciation) AS operating_income,
    SUM(revenue) AS revenue,
    SUM(revenue - cogs - sga - r_n_d - depreciation)::NUMERIC * 100 / NULLIF(SUM(revenue), 0) AS op_margin_pct,
    -- YoY change
    LAG(SUM(revenue - cogs - sga - r_n_d - depreciation)::NUMERIC * 100 / NULLIF(SUM(revenue), 0))
    OVER (ORDER BY DATE_TRUNC('quarter', DATE)) AS prev_quarter_margin
FROM financial_data
WHERE DATE >= '2024-01-01'
GROUP BY 1
ORDER BY 1;

По segment / product line

SELECT
    product_line,
    SUM(revenue) AS revenue,
    SUM(revenue - direct_costs - allocated_opex)::NUMERIC * 100
    / NULLIF(SUM(revenue), 0) AS op_margin_pct
FROM segment_pnl
WHERE DATE >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY product_line
ORDER BY op_margin_pct DESC;

Высокая margin — invest more. Низкая — fix или divest.

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

Ошибка 1. SG&A vs OpEx. SG&A = subset of OpEx. Include depreciation, R&D, etc.

Ошибка 2. Operating ≠ Net. Don't subtract interest / taxes для operating margin. Common confusion.

Ошибка 3. One-time items. Restructuring, lawsuit settlement — exclude от operating (non-recurring).

Ошибка 4. Different cost classification. Software companies: R&D как opex? Different views.

Ошибка 5. Currency. Multi-currency P&L — translate consistent rate.

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

• Как посчитать gross margin в SQL
• Как посчитать net margin в SQL
• Как посчитать EBITDA в SQL
• Как посчитать revenue в SQL

FAQ

Какой Operating Margin ok?

SaaS: 20-30% — average, 40%+ — excellent. Retail: 5-10%. Tech: 15-25%.

Operating vs EBITDA?

Operating Income = EBIT (before interest/tax). EBITDA = EBIT + Depreciation + Amortization.

Negative Operating Margin?

Company burning cash from operations. Often early-stage SaaS / startups.

Improve Operating Margin как?

1. Raise prices. 2) Reduce COGS. 3) Cut OpEx (efficiency). 4) Scale (operating leverage).

По sector ranking?

Software 30%+. Banking 25%+. Retail 5-10%. Airlines 0-5%.