learning/abc_analysis.sql at main · Ta-nu-ki/learning · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
WITH TotalSales AS
(
	SELECT to_char(date_trunc('year', TO_DATE("MONTH"::text, 'YYYYMM')), 'YYYY') as "DATE",
		   sales."STORE_FORMAT",
		   products."CATEGORY",
		   sales."PRODUCT",
		   sum(sales."SALES_QNTY") as "SALES_QNTY",
		   sum((sales."SALES_QNTY" * products."PRICE")) as "SALES_RUB"
	FROM sales
	INNER JOIN products ON products."PRODUCT" = sales."PRODUCT"
	GROUP BY "DATE", "STORE_FORMAT", "CATEGORY", sales."PRODUCT"
	ORDER BY "SALES_RUB" DESC
)
SELECT ts."DATE",
	   ts."STORE_FORMAT",
	   ts."CATEGORY",
	   ts."PRODUCT",
CASE
	WHEN SUM(ts."SALES_QNTY") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY" ORDER BY ts."SALES_QNTY" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(ts."SALES_QNTY") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY") <= 0.85
		THEN 'A'
	WHEN SUM(ts."SALES_QNTY") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY" ORDER BY ts."SALES_QNTY" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(ts."SALES_QNTY") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY") <= 0.95
		THEN 'B'
	ELSE 'C'
END
AS "ABC_QNTY",

CASE
	WHEN SUM(ts."SALES_RUB") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY" ORDER BY ts."SALES_RUB" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(ts."SALES_RUB") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY") <= 0.85
		THEN 'A'
	WHEN SUM(ts."SALES_RUB") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY" ORDER BY ts."SALES_RUB" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / SUM(ts."SALES_RUB") OVER (PARTITION BY ts."DATE", ts."STORE_FORMAT", ts."CATEGORY") <= 0.95
		THEN 'B'
	ELSE 'C'
END
AS "ABC_RUB"
FROM TotalSales AS ts
ORDER BY "DATE", "STORE_FORMAT", "CATEGORY", "PRODUCT"