Как посчитать Sell-Through Rate в SQL

Зачем Sell-Through Rate

В fashion retail Sell-Through Rate — главная метрика сезонной коллекции. Если за сезон продали 60% — приличный результат. 30% — disaster (markdowns + losses). 80%+ — sold out (lost revenue from unmet demand).

Формула

Sell-Through Rate = units_sold / units_received × 100%

За определённый период (часто season).

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

Данные: inventory_received(sku, quantity, received_at), orders для sold.

WITH received AS (
    SELECT
        sku,
        SUM(quantity) AS units_received
    FROM inventory_received
    WHERE received_at BETWEEN '2026-01-01' AND '2026-03-31'  -- Q1 season
    GROUP BY sku
),
sold AS (
    SELECT
        sku,
        SUM(quantity) AS units_sold
    FROM orders
    WHERE status = 'paid'
      AND created_at BETWEEN '2026-01-01' AND '2026-03-31'
    GROUP BY sku
)
SELECT
    r.sku,
    r.units_received,
    COALESCE(s.units_sold, 0) AS units_sold,
    COALESCE(s.units_sold, 0)::NUMERIC * 100 / NULLIF(r.units_received, 0) AS sell_through_pct
FROM received r
LEFT JOIN sold s ON s.sku = r.sku
ORDER BY sell_through_pct ASC;

ORDER BY sell_through_pct ASC — show worst sellers first (candidates for markdown).

По продуктам

SELECT
    p.category,
    p.brand,
    SUM(r.units_received) AS received,
    SUM(s.units_sold) AS sold,
    SUM(s.units_sold)::NUMERIC * 100 / NULLIF(SUM(r.units_received), 0) AS sell_through_pct
FROM inventory_received r
JOIN products p USING (sku)
LEFT JOIN (
    SELECT sku, SUM(quantity) AS units_sold
    FROM orders
    WHERE status = 'paid'
      AND created_at BETWEEN '2026-01-01' AND '2026-03-31'
    GROUP BY sku
) s ON s.sku = r.sku
WHERE r.received_at BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY p.category, p.brand
ORDER BY sell_through_pct DESC;

Brand A category «Куртки» sold 80% → плановать ещё. Brand B same category 30% → stop buying.

Sell-Through vs Inventory Turnover

Sell-Through — про конкретную поставку. Inventory Turnover — про business operations.

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

Ошибка 1. Window mismatch. Received в Q1, sold в Q2 — не считается. Use longer window.

Ошибка 2. Returns. Returned items technically «not sold». Adjust.

Ошибка 3. Transfers between stores. Internal transfers — не sell. Filter.

Ошибка 4. SKU lifecycle. SKU received в multiple sezons. Group by season.

Ошибка 5. Markdowns hidden. Sell-through 80% with 50% off — sold, но низкая margin.

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

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

FAQ

Какой Sell-Through ok?

Fashion: 60-80% по season — норма. <40% — disaster. 90%+ — under-ordered.

Sell-Through падает — что делать?

Markdown / promo. Better merchandise planning next season.

Sell-Through vs Sell-Out?

Sell-Through — % received. Sell-Out — sold out (out-of-stock).

Period choice?

Season (Q или 6mo) — стандарт для fashion. Month — FMCG.

Sell-Through для marketplace?

Считают по продавцам. SaaS — не применима (no inventory).