YqB3B7nm

1# CREATING RAW DATA
2drop table if exists cohort_analysis;
3
4create table cohort_analysis as (
5with pos as (
6select
7	o.trandate as cdate,
8	o.receipt as order_id,
9	o.linecode as sku,
10	o.qty,
11	o.amount,
12	o.customer,
13	if(cast(o.linecode as char)= '30', 'online', 'offline') as channel
14from pos.m_tlogrcp as o
15where
16	TRUE
17	and trandate >= '2019-01-01'
18	and trandate <= '2019-07-07'
19	and rowtype = 'TENDER'
20	and o.qty > 0
21),
22
23online_sku as (
24select
25distinct sku as sku
26from magento.catalog_product_entity as p
27where type_id = 'simple'
28),
29
30raw as (
31select
32	pos.*,
33	online_sku.sku is not null as is_avail_online
34from pos
35left join online_sku
36on online_sku.sku = pos.sku
37)
38
39select * from raw
40);
41
42alter table cohort_analysis
43	add index idx_customer (customer),
44	add index idx_cdate (cdate),
45	add index idx_channel (channel)
46;
47
48
49# TABLE 1 + 2
50drop table if exists cohort_analysis_customer;
51
52create table cohort_analysis_customer as (
53select
54customer,
55date_format(cdate, '%Y-%m-01') as cmonth,
56group_concat(distinct channel) as ls_channel
57from cohort_analysis
58where
59	cdate >= '2019-02-01'
60
61group by 1,2
62
63);
64
65alter table cohort_analysis_customer
66add index idx_customer (customer),
67add index idx_month (cmonth);
68
69-- explain
70select
71date_format(cohort_analysis.cdate, '%Y-%m-01') as cmonth,
72if(c.ls_channel in ('online','offline'), c.ls_channel, 'omni') as channel,
73count(distinct cohort_analysis.customer) as noCustomer,
74count(distinct cohort_analysis.order_id) as noOrder,
75sum(cohort_analysis.amount) as NMV
76
77from cohort_analysis
78left join cohort_analysis_customer as c
79on c.customer = cohort_analysis.customer
80and date_format(cdate, '%Y-%m-01') = c.cmonth
81
82where
83	cdate >= '2019-02-01'
84group by 1,2
85;
86
87# TABLE 3
88select
89date_format(cohort_analysis.cdate, '%Y-%m-01') as cmonth,
90channel,
91count(distinct cohort_analysis.customer) as noCustomer,
92count(distinct cohort_analysis.order_id) as noOrder,
93sum(cohort_analysis.amount) as NMV
94
95from cohort_analysis
96left join cohort_analysis_customer as c
97on c.customer = cohort_analysis.customer
98and date_format(cdate, '%Y-%m-01') = c.cmonth
99
100where
101	cdate >= '2019-02-01'
102	and c.ls_channel <> 'online'
103	and c.ls_channel <> 'offline'
104group by 1,2
105;