Средний интервал между продажами артикулов на маркетплейсах, дней
SQL, по которому посчитано
WITH base AS (
SELECT
sku,
date,
sum(units - returned_units) AS net_units
FROM sales
WHERE channel <> 'Сайт'
AND date >= (SELECT date_trunc('month', max(date)) - INTERVAL '11 months' FROM sales)
GROUP BY sku, date
HAVING sum(units - returned_units) > 0
),
gaps AS (
SELECT
sku,
date,
date_diff('day', lag(date) OVER (PARTITION BY sku ORDER BY date), date) AS gap_days
FROM base
),
agg AS (
SELECT
sku,
round(avg(gap_days), 1) AS avg_interval_days,
count(*) AS intervals
FROM gaps
WHERE gap_days IS NOT NULL
GROUP BY sku
HAVING count(*) >= 3
)
SELECT
sku,
avg_interval_days,
intervals
FROM agg
ORDER BY intervals DESC, sku
LIMIT 20