WITH
order_tab_union AS (
        SELECT
            tb1.uid,
            tb1.order_id,
            tb1.topup_payable_amount/1000000 AS amount,
            tb1.payment_payable_amount/1000000 AS payment_payable_amount,
            tb1.payment_cash_amount/1000000 AS cashback,
            tb1.currency_amount/1000000 currency_amount,
            tb1.topup_coins_amount/1000000 topup_coins_amount,
            tb1.payment_coins_num/1000000 payment_coins_num,
            tb1.payment_channel_id,
            tb1.topup_channel_id,
            tb1.caller,
            tb1.extra_data,
            tb1.item_id,
            json_extract_scalar(tb1.extra_data,'$.payment.__private__.partner.partner_reference_mct') mct,
            tb1.STATUS,
            tb1.memo,
            tb1.TYPE,
            tb1.valid_time,
            ex.exchange_rate
        FROM shopeepay.airpay_payment_txn_vn_db__txn_order_tab_union__reg_daily_s0_live tb1
        LEFT JOIN mp_order.dim_exchange_rate__reg_s0_live ex ON DATE(from_unixtime(tb1.valid_time-3600)) = ex.grass_date AND grass_region = 'VN'
        WHERE DATE(from_unixtime(create_time-3600)) > DATE '2021-12-09'

    )
,orders AS (
SELECT
DATE(from_unixtime(valid_time-3600)) DATE
,CASE
WHEN tb1.amount >= 0 AND tb1.amount < 100000 THEN '1 0-100k'
ELSE '2 >100k'
END range
,CASE WHEN tb1.payment_channel_id = 21011 THEN 'shp'
      WHEN tb1.payment_channel_id = 21003 THEN 'spp'
END SOURCE
,COUNT(DISTINCT tb1.order_id) orders
,SUM(amount/exchange_rate) gmv

FROM order_tab_union tb1
-- left join airpay_vn.airpay_payment_txn_stats_vn_db__order_extra_tab tb2 on tb1.order_id= tb2.order_id
LEFT JOIN airpay_vn.airpay_payment_common_vn_db__channel_tab c ON tb1.payment_channel_id = c.channel_id
LEFT JOIN (SELECT channel_id, max_by(code, id) code FROM airpay_vn.airpay_payment_common_vn_db__product_code_tab GROUP BY 1) pc_i ON c.channel_id = pc_i.channel_id
LEFT JOIN (SELECT channel_type, max_by(code, id) code FROM airpay_vn.airpay_payment_common_vn_db__product_code_tab GROUP BY 1) pc_t ON c.TYPE = pc_t.channel_type

WHERE 1=1
AND tb1.STATUS NOT IN (16, 19)
AND DATE(from_unixtime(valid_time-3600)) >= DATE '2022-01-11'
AND tb1.STATUS = 8
AND tb1.memo <> 'test'
AND tb1.topup_channel_id NOT IN (11100,13002,13003,13004,13009,13010,13011,13103)
-- and tb1.payment_channel_id not in (21000, 21002,21003,21004,21005,21006,21007,21008,21009,100021011,100021016,
-- 100021017,100021040,999991,999992,999993,100021018,100021019,100021020,100021021,
-- 100021022,100021023,100021024,100021025,100021026,100021027,100021028,100021029,
-- 100021040,100021041,100021042,100021043,100001,110012,210001,210002,210003,210004,
-- 210005,210006,210007,210008,210009,210010,210011,210013,210014,210015,210016,210017,
-- 210018,21031,21032,21033,21034,21035,21039,10200077,10200086,10200092,10200097,11000001,11000002,
-- 310001,310002,11000003,100021013,100021014,100021015,21070,21011,31000,31001,
--           21017)
AND tb1.payment_channel_id IN (21003,21004,21005,21006,21008,21009,21011)
AND (regexp_like(json_extract_scalar(tb1.extra_data, '$.payment.transfer.purpose'), 'CashBack###Tien thuong het han') = FALSE --exclude clawback P2P
OR
json_extract_scalar(tb1.extra_data, '$.payment.transfer.purpose') IS NULL)
GROUP BY 1, 2, 3
)
,users AS (
SELECT
DATE(from_unixtime(valid_time-3600)) DATE
,COUNT(DISTINCT uid) users

FROM order_tab_union tb1
-- left join airpay_vn.airpay_payment_txn_stats_vn_db__order_extra_tab tb2 on tb1.order_id= tb2.order_id
LEFT JOIN airpay_vn.airpay_payment_common_vn_db__channel_tab c ON tb1.payment_channel_id = c.channel_id
LEFT JOIN (SELECT channel_id, max_by(code, id) code FROM airpay_vn.airpay_payment_common_vn_db__product_code_tab GROUP BY 1) pc_i ON c.channel_id = pc_i.channel_id
LEFT JOIN (SELECT channel_type, max_by(code, id) code FROM airpay_vn.airpay_payment_common_vn_db__product_code_tab GROUP BY 1) pc_t ON c.TYPE = pc_t.channel_type

WHERE 1=1
AND tb1.STATUS NOT IN (16, 19)
AND DATE(from_unixtime(valid_time-3600)) >= DATE '2022-01-11'
AND tb1.STATUS = 8
AND tb1.memo <> 'test'
AND tb1.topup_channel_id NOT IN (11100,13002,13003,13004,13009,13010,13011,13103)
-- and tb1.payment_channel_id not in (21000, 21002,21003,21004,21005,21006,21007,21008,21009,100021011,100021016,
-- 100021017,100021040,999991,999992,999993,100021018,100021019,100021020,100021021,
-- 100021022,100021023,100021024,100021025,100021026,100021027,100021028,100021029,
-- 100021040,100021041,100021042,100021043,100001,110012,210001,210002,210003,210004,
-- 210005,210006,210007,210008,210009,210010,210011,210013,210014,210015,210016,210017,
-- 210018,21031,21032,21033,21034,21035,21039,10200077,10200086,10200092,10200097,11000001,11000002,
-- 310001,310002,11000003,100021013,100021014,100021015,21070,21011,31000,31001,
--           21017)
AND tb1.payment_channel_id IN (21003,21004,21005,21006,21008,21009,21011)
AND (regexp_like(json_extract_scalar(tb1.extra_data, '$.payment.transfer.purpose'), 'CashBack###Tien thuong het han') = FALSE --exclude clawback P2P
OR
json_extract_scalar(tb1.extra_data, '$.payment.transfer.purpose') IS NULL)
GROUP BY 1
)
SELECT
*
FROM
orders
LEFT JOIN users USING(DATE)
ORDER BY 1, 2