Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ## Table 1,2
- with channel as (
- select
- date_format(cdate, '%Y-%m-01') as cmonth,
- channel,
- count(distinct customer) as noCustomer,
- count(distinct order_id) as noOrder,
- sum(amount) as NMV
- from cohort_analysis
- where
- cdate >= '2019-02-01'
- group by 1,2
- ),
- total as (
- select
- date_format(cdate, '%Y-%m-01') as cmonth,
- 'all',
- count(distinct customer) as noCustomer,
- count(distinct order_id) as noOrder,
- sum(amount) as NMV
- from cohort_analysis
- where
- cdate >= '2019-02-01'
- group by 1,2
- ),
- c as (
- select
- customer,
- date_format(cdate, '%Y-%m-01') as cmonth,
- group_concat(distinct channel) as ls_channel
- from cohort_analysis
- where
- cdate >= '2019-02-01'
- group by 1,2
- ),
- omni_customer as (
- select
- *
- from c
- where ls_channel <> 'offline'
- and ls_channel <> 'online'
- ),
- omni as (
- select
- date_format(cohort_analysis.cdate, '%Y-%m-01') as cmonth,
- 'omni' as channel,
- count(distinct cohort_analysis.customer) as noCustomer,
- count(distinct cohort_analysis.order_id) as noOrder,
- sum(cohort_analysis.amount) as NMV
- from
- cohort_analysis
- left join
- omni_customer
- on omni_customer.customer = cohort_analysis.customer
- and date_format(cohort_analysis.cdate, '%Y-%m-01') = omni_customer.cmonth
- where
- cohort_analysis.cdate >= '2019-02-01'
- and omni_customer.ls_channel is not null
- group by 1,2
- )
- select * from channel union all select * from total union all select * from omni
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement