SELECT
date_trunc('month', DATE(FROM_UNIXTIME(valid_time-3600))) AS grass_date,
-- case when tb1.payment_channel_id in (21003,21004,21005,21006,21008,21009,21011,31000,31001) then 'topup' else 'payment' end as flag,
CASE
WHEN tb1.topup_channel_id IN (10004) THEN '1. Credit_card'
WHEN tb1.topup_channel_id BETWEEN 13400 AND 13460 THEN '2. Giro'
WHEN tb1.topup_channel_id IN (11000) THEN '3. Cash'
WHEN tb1.topup_channel_id BETWEEN 10101 AND 10137 THEN '5. iBanking'
WHEN tb1.topup_channel_id BETWEEN 10301 AND 10314 THEN '6. Bank_channel'
ELSE c.name
END topup_lv1
-- ,c.name topup_lv2
,COUNT(DISTINCT tb1.order_id) c_order
,SUM(topup_payable_amount/1000000) gmv
,SUM(greatest(CAST(bi.min_fee AS DOUBLE)/23250, CAST(CAST(var_fee AS DOUBLE)*(topup_payable_amount/1000000) AS DOUBLE)/23250)) AS bank_fee_total


FROM shopeepay.airpay_payment_txn_vn_db__txn_order_tab_union__reg_daily_s0_live tb1
        LEFT JOIN shopeepay.airpay_payment_common_vn_db__channel_tab__vn_daily_s0_live b ON tb1.topup_channel_id = b.channel_id
        LEFT JOIN shopeepay.airpay_payment_common_vn_db__channel_tab__vn_daily_s0_live c ON tb1.topup_channel_id = c.channel_id
        LEFT JOIN vnapbi_general.airpay_vn_bi_team__bank_ingest bi ON tb1.topup_channel_id = CAST(bi.id_no AS BIGINT)
            AND CASE WHEN tb1.payment_channel_id IN (21003,21004,21005,21006,21008,21009,21011,31000,31001) THEN 'topup' ELSE 'payment' END = bi.cash_type
            AND bi.platform IN ('APA') AND note IS NULL AND date_trunc('month', DATE(FROM_UNIXTIME(valid_time-3600))) = CAST(bi.MONTH AS DATE)
WHERE 1=1
AND tb1.TYPE NOT IN (16, 19)
AND tb1.STATUS = 8
AND tb1.memo <> 'test'
AND DATE(FROM_UNIXTIME(valid_time-3600)) >= DATE '2022-01-01'
AND tb1.payment_channel_id IN (21003,21004,21005,21006,21008,21009,21011,31000,31001)
AND tb1.topup_channel_id NOT IN (11100,13002,13003,13004,13009,13010,13011,13103)
GROUP BY 1, 2