Юнит-экономика Ozon и Wildberries по артикулам

SQL, по которому посчитано

WITH last_month AS (
  SELECT date_trunc('month', max(date)) AS month_start
  FROM sales
),
sales_agg AS (
  SELECT
    s.channel,
    s.sku,
    s.category,
    SUM(s.units) AS units,
    SUM(s.revenue) AS revenue,
    SUM(s.commission) AS commission,
    SUM(s.logistics) AS logistics,
    SUM(s.cost_of_goods) AS cost_of_goods,
    SUM(s.returned_units) AS returned_units
  FROM sales s
  CROSS JOIN last_month lm
  WHERE s.channel IN ('Ozon', 'Wildberries')
    AND s.date >= lm.month_start
    AND s.date < lm.month_start + INTERVAL 1 MONTH
  GROUP BY 1, 2, 3
),
ads_month AS (
  SELECT
    CASE a.source
      WHEN 'Продвижение Ozon' THEN 'Ozon'
      WHEN 'Продвижение WB' THEN 'Wildberries'
    END AS channel,
    a.category,
    SUM(a.cost) AS ad_cost
  FROM ads a
  CROSS JOIN last_month lm
  WHERE a.source IN ('Продвижение Ozon', 'Продвижение WB')
    AND a.date >= lm.month_start
    AND a.date < lm.month_start + INTERVAL 1 MONTH
  GROUP BY 1, 2
),
category_units AS (
  SELECT channel, category, SUM(units) AS category_units
  FROM sales_agg
  GROUP BY 1, 2
),
channel_units AS (
  SELECT channel, SUM(units) AS channel_units
  FROM sales_agg
  GROUP BY 1
),
channel_shared_ads AS (
  SELECT channel, SUM(ad_cost) AS shared_ad_cost
  FROM ads_month
  WHERE category = 'Все'
  GROUP BY 1
),
ad_alloc AS (
  SELECT
    cu.channel,
    cu.category,
    COALESCE(am.ad_cost, 0) AS direct_ad_cost,
    COALESCE(csa.shared_ad_cost, 0) * cu.category_units / NULLIF(chu.channel_units, 0) AS shared_ad_cost,
    cu.category_units
  FROM category_units cu
  LEFT JOIN ads_month am
    ON am.channel = cu.channel
   AND am.category = cu.category
   AND am.category <> 'Все'
  LEFT JOIN channel_shared_ads csa ON csa.channel = cu.channel
  LEFT JOIN channel_units chu ON chu.channel = cu.channel
)
SELECT
  sa.channel || ' / ' || sa.sku AS sku_channel,
  sa.units AS units,
  ROUND(sa.revenue / NULLIF(sa.units, 0), 2) AS price_per_unit,
  ROUND(sa.commission / NULLIF(sa.units, 0), 2) AS commission_per_unit,
  ROUND(sa.logistics / NULLIF(sa.units, 0), 2) AS logistics_per_unit,
  ROUND(sa.cost_of_goods / NULLIF(sa.units, 0), 2) AS cogs_per_unit,
  ROUND((aa.direct_ad_cost + aa.shared_ad_cost) / NULLIF(aa.category_units, 0), 2) AS ad_per_unit,
  ROUND(
    (sa.revenue - sa.commission - sa.logistics - sa.cost_of_goods) / NULLIF(sa.units, 0)
    - (aa.direct_ad_cost + aa.shared_ad_cost) / NULLIF(aa.category_units, 0),
    2
  ) AS profit_per_unit,
  ROUND(sa.returned_units::DOUBLE / NULLIF(sa.units, 0), 4) AS return_rate
FROM sales_agg sa
JOIN ad_alloc aa
  ON aa.channel = sa.channel
 AND aa.category = sa.category
WHERE sa.units > 0
ORDER BY profit_per_unit ASC
LIMIT 30