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

Зачем COGS

COGS — фундамент unit-экономики. Revenue $100, COGS $40 → Gross Profit $60. Without COGS metrics нет gross margin, нет понимания pricing power.

Формула

COGS = beginning_inventory + purchases - ending_inventory

Или per-unit:

COGS_per_unit = cost_purchased / quantity_purchased

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

Простой (single SKU):

WITH purchases AS (
    SELECT
        SUM(quantity * unit_cost) AS total_purchased_cost,
        SUM(quantity) AS total_units_purchased
    FROM purchase_orders
    WHERE received_at BETWEEN '2026-01-01' AND '2026-04-01'
),
sold AS (
    SELECT SUM(quantity) AS units_sold
    FROM orders
    WHERE status = 'paid'
      AND created_at BETWEEN '2026-01-01' AND '2026-04-01'
)
SELECT
    p.total_purchased_cost,
    p.total_units_purchased,
    s.units_sold,
    (p.total_purchased_cost::NUMERIC / NULLIF(p.total_units_purchased, 0)) * s.units_sold AS cogs
FROM purchases p
CROSS JOIN sold s;

Weighted Average

Среднее по всем покупкам:

WITH wac AS (
    SELECT
        sku,
        SUM(quantity * unit_cost) / NULLIF(SUM(quantity), 0) AS weighted_avg_cost
    FROM purchase_orders
    WHERE received_at >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY sku
)
SELECT
    o.sku,
    SUM(o.quantity) AS units_sold,
    SUM(o.quantity) * w.weighted_avg_cost AS cogs
FROM orders o
JOIN wac w ON w.sku = o.sku
WHERE o.status = 'paid'
GROUP BY o.sku, w.weighted_avg_cost;

FIFO

«First-in, first-out» — используем cost самой старой партии первой:

-- В SQL FIFO сложно. Упрощённо для одной партии:
WITH ranked_purchases AS (
    SELECT
        sku,
        quantity,
        unit_cost,
        received_at,
        SUM(quantity) OVER (PARTITION BY sku ORDER BY received_at) AS cumulative_units
    FROM purchase_orders
)
SELECT
    sku,
    -- For sold qty=Q, find which purchases cover it
    -- Then COGS = sum of cost × min(remaining_in_lot, Q)
    ...
FROM ranked_purchases
WHERE sku = 'product-x';

Practical FIFO usually done в Python / dedicated accounting systems.

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

Ошибка 1. Cost variance. В разные дни покупали по разной цене. Weighted average — стандарт.

Ошибка 2. Currency. Закупка в USD, sales в RUB. Convert at right date.

Ошибка 3. Include vs exclude inbound logistics. COGS должен включать landed cost (стоимость + доставка до склада).

Ошибка 4. Returns. Returned items увеличивают inventory back. Уменьшают COGS.

Ошибка 5. Period mismatch. Sold в Q1, purchase made в Q4 previous year. Match through inventory accounting.

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

• Как посчитать gross margin в SQL
• Как посчитать inventory turnover в SQL
• Как посчитать GMV в SQL
• Как посчитать net margin в SQL

FAQ

Weighted average vs FIFO?

Weighted Average — simpler. FIFO — more accurate в inflation/deflation. LIFO — реже used (US accounting).

Landed cost?

Стоимость + транспорт + duties + handling. Real COGS includes landed.

COGS in services?

«Cost of Revenue» — instead of COGS. Includes hosting, support staff.

COGS падает — хорошо?

Только если revenue не падает proportionally. Watch gross margin.

Inventory write-offs?

Outdated / damaged inventory → write off → adds to COGS at moment of write-off.