Доля стоимости на артикулах с нулевым количеством по категориям
SQL, по которому посчитано
WITH last_date AS (
SELECT max(date) AS d FROM stock
),
latest AS (
SELECT sku, category, units, value
FROM stock
WHERE date = (SELECT d FROM last_date)
)
SELECT category,
count(*) FILTER (WHERE units = 0) AS sku_zero,
round(sum(value), 2) AS stock_value,
round(sum(CASE WHEN units = 0 THEN value ELSE 0 END), 2) AS frozen_value,
round(sum(CASE WHEN units = 0 THEN value ELSE 0 END) / nullif(sum(value), 0), 4) AS frozen_share
FROM latest
GROUP BY category
ORDER BY frozen_share DESC NULLS LAST
LIMIT 30