Пропущенные месяцы остатков по артикулам
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('month', max(date)) AS max_month
FROM stock
),
months AS (
SELECT CAST(b.max_month - (n.i * INTERVAL '1 month') AS DATE) AS month
FROM bounds b
CROSS JOIN (SELECT unnest(range(0, 12)) AS i) AS n
),
skus AS (
SELECT sku, max(category) AS category
FROM stock
GROUP BY sku
)
SELECT
s.sku,
s.category,
count(m.month) AS months_expected,
count(st.date) AS months_present,
count(m.month) - count(st.date) AS months_missing,
round(count(st.date) * 1.0 / count(m.month), 2) AS completeness
FROM skus s
CROSS JOIN months m
LEFT JOIN stock st
ON st.sku = s.sku
AND date_trunc('month', st.date) = m.month
GROUP BY s.sku, s.category
ORDER BY months_missing DESC, s.sku
LIMIT 30