yYRxMM7Y

1DROP table IF EXISTS gvrfm ;
2CREATE table IF NOT EXISTS gvrfm 
3
4select
5 email,
6reQuintile,
7freQuintile,
8moQuintile,
9 concat(cast(gv_rfm_recency as varchar),cast(gv_rfm_frequency as varchar),cast(gv_rfm_monetary as varchar)) as gv_rfm_combined
10cast(gv_last_order_date as integer) as gv_last_order_date1,
11cast(gv_total_orders as integer) as gv_total_orders1,
12cast(gv_total_revenue as integer) as gv_total_revenue1,
13from 
14(select
15  email,
16  ntile (5) over (order by coalesce(cast(gv_last_order_date as integer),0)) as reQuintile
17  ntile (5) over (order by coalesce(cast(gv_total_orders as integer),0))    as freQuintile
18  ntile (5) over (order by coalesce(cast(gv_total_revenue as integer),0))   as moQuintile
19 from richline_custom
20 where coalesce(cast(gv_last_order_date as integer),0) <> 0
21 and coalesce(cast(gv_total_orders as integer),0) <> 0
22 and coalesce(cast(gv_total_revenue as integer),0) <> 0
23) ;
24
25ALTER TABLE  gvrfm  ADD gv_rfm_frequency INT;
26
27UPDATE gvrfm  SET gv_rfm_frequency = 1 WHERE gv_total_orders1 = (SELECT max(gv_total_orders1) WHERE freQuintile = 1) ;
28
29
30ALTER TABLE gvrfm ADD freQuartile INT ;
31
32UPDATE gvrfm  SET freQuartile = ntile (4) OVER (PARTITION BY gv_rfm_frequency, ORDER BY coalesce (gv_total_orders1 ,0)) ;
33
34UPDATE gvrfm  SET gv_rfm_frequency = 2 WHERE gv_total_orders1 = (select max(gv_total_orders1) WHERE freQuartile = 1 and gv_rfm_frequency is NULL) ;
35
36
37ALTER TABLE gvrfm ADD freTercile INT ;
38
39UPDATE gvrfm  SET freTercile = ntile (3) OVER (PARTITION BY gv_rfm_frequency, ORDER BY coalesce (gv_total_orders1 ,0)) ;
40
41UPDATE gvrfm  SET gv_rfm_frequency = 3 WHERE gv_total_orders1 = (select max(gv_total_orders1) WHERE freTercile = 1 and gv_rfm_frequency is NULL) ;
42
43
44
45
46ALTER TABLE gvrfm ADD freDecile INT ;
47
48UPDATE gvrfm  SET freDecile = ntile (2) OVER (PARTITION BY gv_rfm_frequency, ORDER BY coalesce (gv_total_orders1 ,0)) ;
49
50UPDATE gvrfm  SET gv_rfm_frequency = 4 WHERE gv_total_orders1 = (select max(gv_total_orders1) WHERE freDecile = 1 and gv_rfm_frequency is NULL) ;
51
52UPDATE gvrfm  SET gv_rfm_frequency = 5 WHERE gv_rfm_frequency is NULL ;