Доля выкупа на Wildberries по артикулам за последний квартал
SQL, по которому посчитано
WITH bounds AS (
SELECT
(date_trunc('month', max(date)) - INTERVAL '2 months')::DATE AS start_date,
max(date) AS end_date
FROM sales
),
base AS (
SELECT
s.sku,
s.product,
s.category,
sum(s.units) AS sold_units,
sum(s.returned_units) AS returned_units
FROM sales AS s, bounds AS b
WHERE s.channel = 'Wildberries'
AND s.date >= b.start_date
AND s.date <= b.end_date
GROUP BY s.sku, s.product, s.category
HAVING sum(s.units) >= 10
)
SELECT
sku,
product,
category,
sold_units,
returned_units,
sold_units - returned_units AS bought_units,
round((sold_units - returned_units) / nullif(sold_units, 0), 4) AS buyout_rate
FROM base
ORDER BY buyout_rate ASC, sku
LIMIT 30