Доля возвратов по месяцам на маркетплейсах
SQL, по которому посчитано
WITH monthly AS (
SELECT
date_trunc('month', date) AS month,
channel,
sum(returned_units) AS returned_units,
sum(units) AS sold_units
FROM sales
WHERE channel IN ('Wildberries', 'Ozon', 'Яндекс Маркет')
AND date >= (
SELECT date_trunc('month', max(date)) - INTERVAL '11 months'
FROM sales
)
GROUP BY 1, 2
)
SELECT
month,
round(
sum(CASE WHEN channel = 'Wildberries' THEN returned_units ELSE 0 END)::DOUBLE
/ nullif(sum(CASE WHEN channel = 'Wildberries' THEN sold_units ELSE 0 END), 0),
4
) AS wb_return_share,
round(
sum(CASE WHEN channel = 'Ozon' THEN returned_units ELSE 0 END)::DOUBLE
/ nullif(sum(CASE WHEN channel = 'Ozon' THEN sold_units ELSE 0 END), 0),
4
) AS ozon_return_share,
round(
sum(CASE WHEN channel = 'Яндекс Маркет' THEN returned_units ELSE 0 END)::DOUBLE
/ nullif(sum(CASE WHEN channel = 'Яндекс Маркет' THEN sold_units ELSE 0 END), 0),
4
) AS ym_return_share
FROM monthly
GROUP BY month
ORDER BY month