Средний и медианный срок жизни артикула по категориям, вымышленный мебельный магазин
SQL, по которому посчитано
WITH bounds AS (
SELECT max(date) AS max_date
FROM sales
),
marketplace_sales AS (
SELECT
s.channel,
s.category,
s.sku,
s.date AS sale_date,
s.units,
s.revenue
FROM sales s
CROSS JOIN bounds b
WHERE s.channel IN ('Wildberries', 'Ozon', 'Яндекс Маркет')
AND s.date >= (date_trunc('month', b.max_date) - INTERVAL '11 months')::DATE
AND s.date <= b.max_date
),
sku_life AS (
SELECT
category,
sku,
min(sale_date) AS first_sale,
max(sale_date) AS last_sale,
count(DISTINCT sale_date) AS active_days,
date_diff('day', min(sale_date), max(sale_date)) + 1 AS lifespan_days,
sum(units) AS units,
sum(revenue) AS revenue
FROM marketplace_sales
GROUP BY category, sku
),
category_life AS (
SELECT
category,
count(*) AS skus,
round(avg(lifespan_days), 1) AS avg_lifespan_days,
round(median(lifespan_days), 1) AS median_lifespan_days,
round(avg(active_days), 1) AS avg_active_days,
sum(units) AS units,
round(sum(revenue), 0) AS revenue
FROM sku_life
GROUP BY category
)
SELECT
category,
skus,
avg_lifespan_days,
median_lifespan_days,
avg_active_days,
units,
revenue
FROM category_life
ORDER BY avg_lifespan_days DESC
LIMIT 30