3.0

/* CREATE VIEW OF FILTERED TRANSACTIONS AS [VW_TMP_FTXN] */
CREATE VIEW [VW_TMP_FTXN] AS
SELECT
	ftxn.[CustomerID],
	ftxn.[TransactionDate],
	CASE WHEN ftxn.[TransactionDate] >= '2019/01/01' AND ftxn.[TransactionDate] <= '2019/12/31' THEN 1 ELSE 0 END AS [IN_2019],
	CASE WHEN ftxn.[TransactionDate] >= '2020/01/01' AND ftxn.[TransactionDate] <= '2020/12/31' THEN 1 ELSE 0 END AS [IN_2020],
	ftxn.[TicketNumber],
	SUM(ftxn.[AMT]) AS [AMT]
FROM
	(
	SELECT
		txn.[CustomerID],
		txn.[TransactionDate],
		txn.[TicketNumber],
		txn.[QTY],
		txn.[SKUCode],
		pm.[DeptID],
		pm.[SKU_Price],
		txn.[QTY] * pm.[SKU_Price] AS [AMT]
	-- TXN TABLE  
	FROM [Sales_SKU_CDS] txn
	-- USE txn.[SKUCode] GET [DeptID],[SKU_Price]
	LEFT JOIN [Product_Maste] pm
	ON txn.[SKUCode] = pm.[SKUCode]
	-- FILTER
	WHERE pm.[DeptID] = '001'
	) ftxn
GROUP BY
	ftxn.[CustomerID],
	ftxn.[TransactionDate],
	CASE WHEN ftxn.[TransactionDate] >= '2019/01/01' AND ftxn.[TransactionDate] <= '2019/12/31' THEN 1 ELSE 0 END,
	CASE WHEN ftxn.[TransactionDate] >= '2020/01/01' AND ftxn.[TransactionDate] <= '2020/12/31' THEN 1 ELSE 0 END,
	ftxn.[TicketNumber]

/* CREATE VIEW OF CUSTOMER GROUP AS [VW_TMP_CUST_GRP] */
CREATE VIEW [VW_TMP_CUST_GRP] AS
SELECT
	t1.[CustomerID],
	CASE 
	WHEN t1.[IN_2019] = 1 AND t1.[IN_2020] = 1 THEN 'EXIST'
	WHEN t1.[IN_2019] = 1 AND t1.[IN_2020] <> 1 THEN 'LAPSED'
	WHEN t1.[IN_2019] <> 1 AND t1.[IN_2020] = 1 THEN 'NEW'
	ELSE 'OTH'
	END AS [CUST_BIN]
FROM
	(SELECT
		[CustomerID],
		MAX([IN_2019]) AS [IN_2019],
		MAX([IN_2020]) AS [IN_2020],
	FROM [VW_TMP_CUST_GRP]
	GROUP BY [CustomerID]) t1

/* BUILD MAIN TABLE */
SELECT
	t1.[CUST_BIN],
	t1.[SALES_TIER],
	SUM(t1.[AMT]) AS [SUM_AMT],
	COUNT(DISTINCT t1.[CustomerID]) AS [DTC_CUST_ID],
	SUM(t1.[AMT]) / COUNT(t1.[TicketNumber]) AS [ATV]
FROM
	(
	SELECT
		ftxn.[CustomerID],
		cgrp.[CUST_BIN],
		ftxn.[TicketNumber],
		ftxn.[AMT],
		CASE 
		WHEN ftxn.[AMT] < 0 THEN NULL
		WHEN ftxn.[AMT] < 3000 THEN 1
		WHEN ftxn.[AMT] < 5000 THEN 2
		WHEN ftxn.[AMT] < 10000 THEN 3
		WHEN ftxn.[AMT] < 15000 THEN 4
		WHEN ftxn.[AMT] < 30000 THEN 5
		WHEN ftxn.[AMT] < 50000 THEN 6
		WHEN ftxn.[AMT] < 100000 THEN 7
		WHEN ftxn.[AMT] >= 100000 THEN 8
		ELSE NULL END AS [SALES_TIER]
	FROM [VW_TMP_FTXN] ftxn
	LEFT JOIN [VW_TMP_CUST_GRP] cgrp
	ON ftxn.[CustomerID] = cgrp.[CustomerID]
	) t1
GROUP BY
	t1.[CUST_BIN],
	t1.[SALES_TIER]
ORDER BY
	t1.[CUST_BIN],
	t1.[SALES_TIER]