y4A5uHGR

1--------------------------------------------------------------------------------
2------------------------------ TRAINING SET
3--------------------------------------------------------------------------------
4
5-- PATH TO CHURN
6DROP TABLE IF EXISTS customer_journey.ich_banking_churnpath_train;
7CREATE ANALYTIC TABLE customer_journey.ich_banking_churnpath_train
8DISTRIBUTE BY HASH(interaction_list)
9AS
10SELECT 
11	*,
12	-- WE NEED TO DEFINE DATA TYPE
13	'churn'::VARCHAR AS tag 
14FROM nPath(
15	ON(
16		SELECT a.* 
17		FROM customer_journey.ich_banking a
18		-- TRAINING CUSTOMER SET ONLY
19		JOIN customer_journey.customers_train b 
20			ON a.customer_identifier = b.customer_identifier
21			AND b.tag = 'churn'
22		WHERE product_category <> '-1'
23	)
24	PARTITION BY customer_identifier
25	ORDER BY interaction_timestamp
26	MODE(NONOVERLAPPING)
27	PATTERN('(OTHER_EVENT){4}.CLOSED_EVENT')
28	SYMBOLS(
29		interaction_type <> 'ACCOUNT_CLOSED' AS OTHER_EVENT,
30	    interaction_type =  'ACCOUNT_CLOSED' AS CLOSED_EVENT
31	)
32	RESULT(
33		ACCUMULATE(interaction_type OF ANY(OTHER_EVENT, CLOSED_EVENT)) AS interaction_list,
34		COUNT(interaction_type OF ANY(OTHER_EVENT, CLOSED_EVENT)) AS click_depth,
35		FIRST(customer_identifier OF OTHER_EVENT)  AS customer_identifier
36	)
37) path;
38ANALYZE customer_journey.ich_banking_churnpath_train;
39
40-- PATH TO NO CHURN
41DROP TABLE IF EXISTS customer_journey.ich_banking_nochurnpath_train;
42CREATE ANALYTIC TABLE customer_journey.ich_banking_nochurnpath_train
43DISTRIBUTE BY HASH(interaction_list)
44AS
45SELECT 
46	*,
47	-- WE NEED TO DEFINE DATA TYPE
48	'no_churn'::VARCHAR AS tag 
49FROM nPath(
50	ON(
51		SELECT a.* 
52		FROM customer_journey.ich_banking a
53		-- TRAINING CUSTOMER SET ONLY
54		JOIN customer_journey.customers_train b 
55			ON a.customer_identifier = b.customer_identifier
56			AND b.tag = 'no_churn'
57		WHERE product_category <> '-1'
58	)
59	PARTITION BY customer_identifier
60	ORDER BY interaction_timestamp
61	MODE (NONOVERLAPPING)
62	-- Limit to a depth of 5
63	PATTERN ('(OTHER_EVENT){5}')
64	SYMBOLS (
65		interaction_type NOT LIKE 'ACCOUNT_CLOSED' AS OTHER_EVENT
66	)
67	RESULT (
68		ACCUMULATE(interaction_type OF ANY(OTHER_EVENT)) AS interaction_list,
69		COUNT(interaction_type OF ANY(OTHER_EVENT)) AS click_depth,
70		FIRST(customer_identifier of OTHER_EVENT) AS customer_identifier
71	)
72) path;
73ANALYZE customer_journey.ich_banking_nochurnpath_train;