6TXVuqXL

1# Raw data for analysis
2
3drop table if exists cohort_analysis;
4
5create table cohort_analysis as (
6with pos as (
7select
8	o.trandate as cdate,
9	o.receipt as order_id,
10	o.linecode as sku,
11	o.qty,
12	o.amount,
13	o.customer,
14	'offline' as channel
15from pos.m_tlogrcp as o
16where
17	TRUE
18	and trandate >= '2019-01-01'
19	and trandate < '2019-06-01'
20	and rowtype = 'ITEM'
21	and o.qty > 0
22),
23
24online_sku as (
25select
26distinct sku as sku
27from magento.catalog_product_entity as p
28where type_id = 'simple'
29),
30
31offline as (
32select
33	pos.*,
34	online_sku.sku is not null as is_avail_online
35from pos
36left join online_sku
37on online_sku.sku = pos.sku
38),
39
40`online` as (
41	select
42	date(o.created_at) as cdate,
43	o.entity_id as order_id,
44	i.sku,
45	i.qty_ordered as qty,
46	i.row_total as amount,
47	coalesce(c.`code`, o.customer_id) as customer,
48	'online' as channel,
49	1 as is_avail_online
50
51	from magento.sales_order as o
52
53	left join magento.lof_marketplace_membership as c
54	on c.customer_id = o.customer_id
55
56	left join magento.sales_order_item as i
57	on i.order_id = o.entity_id
58
59	where
60	TRUE
61	and o.customer_id is not null
62	and product_type = 'simple'
63	and o.status = 'complete'
64	and i.created_at >= '2019-01-01'
65	and i.created_at < '2019-06-01'
66),
67
68raw as (
69	select * from offline
70	union all
71	select * from `online`
72)
73select * from raw
74);
75
76alter table cohort_analysis
77	add index idx_customer (customer),
78	add index idx_cdate (cdate),
79	add index idx_channel (channel)
80;