Артикулы с самой низкой долей дней с продажами за 12 месяцев
SQL, по которому посчитано
WITH period AS (
SELECT
max(date) AS max_date,
CAST(date_trunc('month', max(date)) AS DATE) - INTERVAL '11 months' AS start_date
FROM sales
),
period_days AS (
SELECT
max_date,
start_date,
date_diff('day', start_date, max_date) + 1 AS days_in_period
FROM period
),
sku_days AS (
SELECT
s.sku,
max(s.product) AS product,
max(s.category) AS category,
count(DISTINCT s.date) AS sales_days
FROM sales s
CROSS JOIN period_days p
WHERE s.date >= p.start_date
AND s.date <= p.max_date
GROUP BY s.sku
)
SELECT
sd.sku,
sd.product,
sd.category,
sd.sales_days,
p.days_in_period,
round(sd.sales_days * 1.0 / p.days_in_period, 4) AS sales_share
FROM sku_days sd
CROSS JOIN period_days p
ORDER BY sales_share ASC, sd.sku
LIMIT 30