Магазин вымышленный, пары категорий с расхождениями
SQL, по которому посчитано
WITH sales_12m AS (
SELECT
sku,
string_agg(DISTINCT category, ' / ') AS category_sales,
sum(units) AS units_sold
FROM sales
WHERE date >= (SELECT date_trunc('month', max(date)) - INTERVAL '11 months' FROM sales)
GROUP BY sku
),
stock_last AS (
SELECT
sku,
string_agg(DISTINCT category, ' / ') AS category_stock,
sum(units) AS stock_units
FROM stock
WHERE date = (SELECT max(date) FROM stock)
GROUP BY sku
),
mismatch AS (
SELECT
coalesce(s.sku, st.sku) AS sku,
s.category_sales,
st.category_stock,
s.units_sold,
st.stock_units,
coalesce(s.category_sales, 'нет в продажах') || ' → ' || coalesce(st.category_stock, 'нет в остатках') AS category_pair
FROM sales_12m s
FULL OUTER JOIN stock_last st ON s.sku = st.sku
WHERE s.category_sales IS DISTINCT FROM st.category_stock
)
SELECT
category_pair,
count(DISTINCT sku) AS sku_count,
sum(coalesce(units_sold, 0)) AS units_sold,
sum(coalesce(stock_units, 0)) AS stock_units
FROM mismatch
GROUP BY category_pair
ORDER BY sku_count DESC, category_pair
LIMIT 30