WITH
a AS
(
SELECT
    grass_date,
    buyer_id,
    payment_method_id,
    gmv_usd,
    TYPE,
    order_id,
    pv_voucher_code
FROM
(
    SELECT
        from_unixtime(o.create_time - 3600) AS grass_date
        , o.user_id AS buyer_id
        ,CASE WHEN (channel_id IN (5001800,5002900) OR (regexp_like(v.code,'^AP') AND channel_id NOT IN (5002800,5000102) )) THEN 30 ELSE NULL END AS payment_method_id
        ,final_price/23500 AS gmv_usd
        ,'dp' AS TYPE
        ,o.order_id
        ,'' pv_voucher_code
    FROM digitalpurchase.shopee_digital_product_order_v2_vn_db__order_tab__reg_daily_s0_live o
    LEFT JOIN digitalpurchase.shopee_digital_product_promotion_vn_db__voucher_tab__reg_daily_s0_live v   ON v.order_id = o.order_id AND v.use_status = 'VU2' AND v.return_status = 'VR0'
    WHERE 1=1
        AND (o.transaction_id != '' OR(payment_status = 'P2' AND final_price = 0))
        AND platform = 1
        AND DATE(from_unixtime(o.create_time - 3600)) >= CURRENT_DATE - INTERVAL '2' MONTH

    UNION

    SELECT
        from_unixtime(create_timestamp - 3600) grass_date,
        buyer_id,
        payment_method_id,
        gmv_usd,
        'mkp' AS TYPE,
        CAST(order_id AS VARCHAR) order_id,
        pv_voucher_code
    FROM mp_order.dwd_order_all_ent_df__vn_s0_live
    WHERE 1=1
        AND DATE(from_unixtime(create_timestamp - 3600)) >= CURRENT_DATE - INTERVAL '2' MONTH
        AND NOT (gmv < 20 AND shop_id IN (134344983,145418026))
        -- and order_item_status not in (3,4)
)
)
,dim_user AS (
SELECT
user_id
-- ,case when gender in (1, 3) then 'male' else 'female' end gender
-- ,case
-- when 2021 - year(birthday)  between 0 and 17 then '0-17'
-- when 2021 - year(birthday)  between 18 and 24 then '18-24'
-- when 2021 - year(birthday)  between 25 and 34 then '25-34'
-- when 2021 - year(birthday)  between 35 and 44 then '35-44'
-- when 2021 - year(birthday)  >=45 then '45+'
-- else null
-- end age
,CASE
    WHEN default_delivery_address_state = 'Hà Nội' THEN 'hn'
    WHEN default_delivery_address_state = 'TP. Hồ Chí Minh' THEN 'hcm'
    WHEN default_delivery_address_state IN ('Hà Tĩnh','Đắk Lắk','Nghệ An','Bình Định','Đà Nẵng','Quảng Bình','Khánh Hòa','Gia Lai','Quảng Trị'
    ,'Quảng Nam','Thừa Thiên Huế','Quảng Ngãi','Bình Thuận','Lâm Đồng','Đắk Nông','Kon Tum','Phú Yên','Ninh Thuận','Thanh Hóa') THEN '2 central'
    WHEN default_delivery_address_state IN ('Yên Bái','Hà Nội','Hà Giang','Phú Thọ','Hưng Yên','Hải Phòng','Bắc Giang','Hòa Bình','Nam Định'
    ,'Bắc Ninh','Thái Nguyên','Quảng Ninh','Vĩnh Phúc','Lạng Sơn','Hà Nam','Thái Bình','Lào Cai','Hải Dương','Ninh Bình','Tuyên Quang','Sơn La','Điện Biên','Cao Bằng','Bắc Kạn','Lai Châu') THEN '1 north'
    WHEN default_delivery_address_state IN ('Bình Phước','Cần Thơ','Long An','TP. Hồ Chí Minh','Đồng Nai','Bình Dương','An Giang','Trà Vinh'
    ,'Bến Tre','Tây Ninh','Đồng Tháp','Vĩnh Long','Sóc Trăng','Cà Mau','Tiền Giang','Kiên Giang','Bà Rịa - Vũng Tàu','Hậu Giang','Bạc Liêu') THEN '3 south'
    ELSE ''
END reg2
-- ,case
--     when default_delivery_address_state = 'Hà Nội' then '1 hn'
--     when default_delivery_address_state = 'TP. Hồ Chí Minh' then '2 hcm'
--     when default_delivery_address_state in ('Bà Rịa - Vũng Tàu','Bắc Ninh','Bình Dương','Cần Thơ','Đà Nẵng','Đồng Nai','Hải Phòng','Khánh Hòa','Quảng Ninh','Thái Nguyên','Vĩnh Phúc') then '3 high'
--     when default_delivery_address_state in ('Bắc Giang','Bình Định','Bình Phước','Bình Thuận','Hà nam','Hà Tĩnh','Hải Dương','Hòa Bình',
--     'Hưng Yên','Kiên Giang','Lâm Đồng','Lào Cai','Long An','Ninh Bình','Quảng Nam','Quảng Ngãi','Tây Ninh','Tiền Giang','Vĩnh Long') then '4 mid'
--     when default_delivery_address_state in ('An Giang','Bắc Kạn','Bạc Liêu','Bến Tre','Cà Mau','Cao Bằng','Đắk Lắk','Đắk Nông','Điện Biên'
--     ,'Đồng Tháp','Gia Lai','Hà Giang','Hậu Giang','Thừa Thiên huế','Kon Tum','Lai Châu','Lạng Sơn','Nam Định','Nghệ An','Ninh Thuận','Phú Thọ'
--     ,'Phú Yên','Quảng Bình','Quảng Trị','Sóc Trăng','Sơn La','Thái Bình','Thanh Hóa','Trà Vinh','Tuyên Quang','Yên Bái') then '5 low'
-- end reg1
FROM
mp_user.dim_user__vn_s0_live
WHERE 1=1
AND grass_date = CURRENT_DATE - INTERVAL '2' DAY
)
SELECT
week(DATE(grass_date)) week
,'' note
--spp_mkp_order
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30)                 AND TYPE = 'mkp' THEN a.order_id END) spp_mkp_order_total
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = 'hn' AND TYPE = 'mkp' THEN a.order_id END) spp_mkp_order_hn
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = 'hcm' AND TYPE = 'mkp' THEN a.order_id END) spp_mkp_order_hcm
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '1 north' AND TYPE = 'mkp' THEN a.order_id END) spp_mkp_order_north
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '2 central' AND TYPE = 'mkp' THEN a.order_id END) spp_mkp_order_central
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '3 south' AND TYPE = 'mkp' THEN a.order_id END) spp_mkp_order_south
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '' AND TYPE = 'mkp' THEN a.order_id END) spp_mkp_order_blank

--shp_mkp_order
,COUNT(DISTINCT CASE WHEN                 TYPE = 'mkp' THEN a.order_id END) shp_mkp_order_total
,COUNT(DISTINCT CASE WHEN reg2 = 'hn' AND TYPE = 'mkp' THEN a.order_id END) shp_mkp_order_hn
,COUNT(DISTINCT CASE WHEN reg2 = 'hcm' AND TYPE = 'mkp' THEN a.order_id END) shp_mkp_order_hcm
,COUNT(DISTINCT CASE WHEN reg2 = '1 north' AND TYPE = 'mkp' THEN a.order_id END) shp_mkp_order_north
,COUNT(DISTINCT CASE WHEN reg2 = '2 central' AND TYPE = 'mkp' THEN a.order_id END) shp_mkp_order_central
,COUNT(DISTINCT CASE WHEN reg2 = '3 south' AND TYPE = 'mkp' THEN a.order_id END) shp_mkp_order_south
,COUNT(DISTINCT CASE WHEN reg2 = '' AND TYPE = 'mkp' THEN a.order_id END) shp_mkp_order_blank

--spp_mkp_buyer
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30)                 AND TYPE = 'mkp' THEN a.buyer_id END) spp_mkp_buyer_total
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = 'hn' AND TYPE = 'mkp' THEN a.buyer_id END) spp_mkp_buyer_hn
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = 'hcm' AND TYPE = 'mkp' THEN a.buyer_id END) spp_mkp_buyer_hcm
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '1 north' AND TYPE = 'mkp' THEN a.buyer_id END) spp_mkp_buyer_north
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '2 central' AND TYPE = 'mkp' THEN a.buyer_id END) spp_mkp_buyer_central
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '3 south' AND TYPE = 'mkp' THEN a.buyer_id END) spp_mkp_buyer_south
,COUNT(DISTINCT CASE WHEN payment_method_id IN (28, 30) AND reg2 = '' AND TYPE = 'mkp' THEN a.buyer_id END) spp_mkp_buyer_blank

--shp_mkp_buyer
,COUNT(DISTINCT CASE WHEN                 TYPE = 'mkp' THEN a.buyer_id END) shp_mkp_buyer_total
,COUNT(DISTINCT CASE WHEN reg2 = 'hn' AND TYPE = 'mkp' THEN a.buyer_id END) shp_mkp_buyer_hn
,COUNT(DISTINCT CASE WHEN reg2 = 'hcm' AND TYPE = 'mkp' THEN a.buyer_id END) shp_mkp_buyer_hcm
,COUNT(DISTINCT CASE WHEN reg2 = '1 north' AND TYPE = 'mkp' THEN a.buyer_id END) shp_mkp_buyer_north
,COUNT(DISTINCT CASE WHEN reg2 = '2 central' AND TYPE = 'mkp' THEN a.buyer_id END) shp_mkp_buyer_central
,COUNT(DISTINCT CASE WHEN reg2 = '3 south' AND TYPE = 'mkp' THEN a.buyer_id END) shp_mkp_buyer_south
,COUNT(DISTINCT CASE WHEN reg2 = '' AND TYPE = 'mkp' THEN a.buyer_id END) shp_mkp_buyer_blank

FROM
a
LEFT JOIN dim_user u ON a.buyer_id = u.user_id
WHERE 1=1
AND DATE(a.grass_date) >= CURRENT_DATE - INTERVAL '2' MONTH
GROUP BY 1