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'
    )

,a AS (
SELECT
wpa.partner_account_id shp_user_id
,COUNT(DISTINCT CASE WHEN tb1.payment_channel_id IN (21003,21004,21005,21006,21008,21009,21011) AND amount >= 100000 THEN tb1.order_id END) topup_txn
,COUNT(DISTINCT CASE WHEN tb1.payment_channel_id IN (21002) /*OR re.order_id IS NOT NULL*/ THEN tb1.order_id END) withdraw_txn

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 shopeepay.airpay_payment_common_vn_db__channel_tab__vn_daily_s0_live c ON tb1.payment_channel_id = c.channel_id
LEFT JOIN (SELECT channel_id, max_by(code, id) code FROM shopeepay.airpay_payment_common_vn_db__product_code_tab__vn_daily_s0_live GROUP BY 1) pc_i ON c.channel_id = pc_i.channel_id
LEFT JOIN (SELECT channel_type, max_by(code, id) code FROM shopeepay.airpay_payment_common_vn_db__product_code_tab__vn_daily_s0_live GROUP BY 1) pc_t ON c.TYPE = pc_t.channel_type
LEFT JOIN
(
SELECT
uid, max_by(partner_account_id, update_time) partner_account_id --shopee user_id
FROM shopeepay.airpay_user_service_vn_db__wallet_partner_account_tab__vn_daily_s0_live
WHERE unlink_time = 0
GROUP BY 1
) wpa ON wpa.uid = tb1.uid
INNER JOIN
(SELECT DISTINCT uid FROM shopeepay.airpay_user_service_vn_db__user_kyc_info_log_tab__vn_daily_s0_live) kyc ON tb1.uid = kyc.uid
INNER JOIN
(SELECT DISTINCT uid FROM shopeepay.airpay_is_card_center_vn_db__bank_account_tab__vn_daily_s0_live WHERE flag IN (1, 257) AND channel_id != 10004) g ON g.uid = tb1.uid
-- LEFT JOIN dev_vnapbi_general.spp_withdraw_remittance re ON tb1.order_id = re.order_id

WHERE 1=1
AND DATE(from_unixtime(valid_time-3600)) BETWEEN DATE '2022-05-01' AND DATE '2022-05-04'
AND tb1.TYPE NOT IN (16, 19)
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 IN (21003,21004,21005,21006,21008,21009,21011,       21002)
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 a WHERE topup_txn >= 5 LIMIT 200