Стоимость неликвида по артикулам, руб.
SQL, по которому посчитано
WITH last_stock AS (
SELECT sku, category, units, value AS stock_value
FROM stock
WHERE date = (SELECT max(date) FROM stock)
),
sales_12m AS (
SELECT sku,
sum(units) - sum(returned_units) AS sold_units,
sum(revenue) - sum(returned_revenue) AS revenue,
max(date) AS last_sale_date
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY sku
),
names AS (
SELECT sku, max(product) AS product
FROM sales
GROUP BY sku
)
SELECT s.sku,
n.product,
s.category,
s.units AS stock_units,
round(s.stock_value) AS stock_value,
coalesce(sa.sold_units, 0) AS sold_units_12m,
round(coalesce(sa.revenue, 0)) AS revenue_12m,
sa.last_sale_date
FROM last_stock s
LEFT JOIN sales_12m sa ON sa.sku = s.sku
LEFT JOIN names n ON n.sku = s.sku
WHERE s.units > 0
AND coalesce(sa.sold_units, 0) <= 2
ORDER BY stock_value DESC
LIMIT 30