# Raw data for analysis

drop table if exists cohort_analysis;

create table cohort_analysis as (
with pos as (
select
    o.trandate as cdate,
    o.receipt as order_id,
    o.linecode as sku,
    o.qty,
    o.amount,
    o.customer,
    'offline' as channel
from pos.m_tlogrcp as o
where
    TRUE
    and trandate >= '2019-01-01'
    and trandate < '2019-06-01'
    and rowtype = 'ITEM'
    and o.qty > 0
),

online_sku as (
select
distinct sku as sku
from magento.catalog_product_entity as p
where type_id = 'simple'
),

offline as (
select
    pos.*,
    online_sku.sku is not null as is_avail_online
from pos
left join online_sku
on online_sku.sku = pos.sku
),

`online` as (
    select
    date(o.created_at) as cdate,
    o.entity_id as order_id,
    i.sku,
    i.qty_ordered as qty,
    i.row_total as amount,
    coalesce(c.`code`, o.customer_id) as customer,
    'online' as channel,
    1 as is_avail_online

    from magento.sales_order as o

    left join magento.lof_marketplace_membership as c
    on c.customer_id = o.customer_id

    left join magento.sales_order_item as i
    on i.order_id = o.entity_id

    where
    TRUE
    and o.customer_id is not null
    and product_type = 'simple'
    and o.status = 'complete'
    and i.created_at >= '2019-01-01'
    and i.created_at < '2019-06-01'
),

raw as (
    select * from offline
    union all
    select * from `online`
)
select * from raw
);

alter table cohort_analysis
    add index idx_customer (customer),
    add index idx_cdate (cdate),
    add index idx_channel (channel)
;