Маржа по артикулам на Яндекс Маркете за 12 месяцев
SQL, по которому посчитано
WITH base AS (
SELECT
sku,
any_value(product) AS product,
sum(units) AS units_sold,
sum(returned_units) AS units_returned,
sum(revenue) AS revenue_gross,
sum(returned_revenue) AS revenue_returned,
sum(cost_of_goods) AS cogs,
sum(commission) AS commission,
sum(logistics) AS logistics
FROM sales
WHERE channel = 'Яндекс Маркет'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY sku
)
SELECT
sku,
product,
units_sold - units_returned AS units_net,
round(revenue_gross - revenue_returned, 0) AS revenue_net,
round(cogs, 0) AS cogs,
round(commission, 0) AS commission,
round(logistics, 0) AS logistics,
round(revenue_gross - revenue_returned - cogs - commission - logistics, 0) AS margin,
round((revenue_gross - revenue_returned - cogs - commission - logistics)
/ nullif(revenue_gross - revenue_returned, 0), 3) AS margin_pct
FROM base
ORDER BY margin DESC
LIMIT 15