Прибыль с одной штуки на Wildberries
SQL, по которому посчитано
WITH wb AS (
SELECT sku, product, category,
sum(units - returned_units) AS units,
sum(revenue - returned_revenue) AS revenue,
sum(cost_of_goods * (units - returned_units) / units) AS goods,
sum(commission) AS commission,
sum(logistics) AS logistics
FROM sales
WHERE channel = 'Wildberries' AND date > (SELECT max(date) FROM sales) - INTERVAL 3 MONTH
GROUP BY ALL
),
promo AS (
SELECT category, sum(cost) AS cost
FROM ads
WHERE source = 'Продвижение WB' AND date > (SELECT max(date) FROM ads) - INTERVAL 3 MONTH
GROUP BY category
),
by_category AS (SELECT category, sum(units) AS units FROM wb GROUP BY category)
SELECT wb.product,
round((wb.revenue - wb.goods - wb.commission - wb.logistics - coalesce(promo.cost, 0) * wb.units / by_category.units) / wb.units) AS profit_per_unit
FROM wb
JOIN by_category USING (category)
LEFT JOIN promo USING (category)
ORDER BY profit_per_unit DESC