Доля возвратов по артикулам на маркетплейсах за 12 месяцев
SQL, по которому посчитано
WITH last_date AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM sales
),
marketplace_sales AS (
SELECT
sku,
min(product) AS product,
sum(units) AS sold_units,
sum(returned_units) AS returned_units
FROM sales
WHERE channel IN ('Wildberries', 'Ozon', 'Яндекс Маркет')
AND date >= (SELECT month_start - INTERVAL '11 months' FROM last_date)
AND date < (SELECT month_start + INTERVAL '1 month' FROM last_date)
GROUP BY sku
HAVING sum(units) >= 10
)
SELECT
sku,
product,
sold_units,
returned_units,
round(returned_units::DOUBLE / nullif(sold_units, 0), 4) AS return_share
FROM marketplace_sales
ORDER BY return_share DESC, sold_units DESC
LIMIT 20