[Reconcile] WCV

WITH DATA AS
(SELECT DISTINCT
    orderid
    , DATE(FROM_UNIXTIME(MIN(ctime - 3600))) AS escrow_time
FROM
    marketplace.shopee_order_audit_v3_db__order_audit_tab__vn_daily_s0_live
WHERE
    new_status = 14
AND DATE(FROM_UNIXTIME((ctime - 3600))) >= DATE'2021-01-01' --new
GROUP BY 1
)
, user_tab AS
(SELECT * FROM mp_user.dim_shop__vn_s0_live WHERE grass_date = (SELECT MAX(grass_date) FROM mp_user.dim_shop__vn_s0_live WHERE grass_date = (SELECT MAX(grass_date) FROM mp_user.dim_shop__vn_s0_live)))
, item AS
(SELECT * FROM mp_item.dim_model__vn_s0_live WHERE grass_date = (SELECT MAX(grass_date) FROM mp_item.dim_model__vn_s0_live WHERE grass_date = (SELECT MAX(grass_date) FROM mp_item.dim_model__vn_s0_live)))
, dim_user AS (
SELECT DISTINCT user_id, default_delivery_address_state FROM mp_user.dim_user__vn_s0_live WHERE grass_date = CURRENT_DATE - INTERVAL '2' DAY)
,prefix AS
(SELECT
    promotionid,
    prefix
FROM
    marketplace.shopee_voucher_db__promotion_tab__vn_daily_s0_live
WHERE
    UPPER(name) LIKE '%REISSUE%'),
tb_o AS (
SELECT
    o.create_datetime,
    d.escrow_time,
    o.base_checkout_id,
    o.shop_id,
    u.user_name,
    o.buyer_id,
    pv_promotion_id AS promotionid,
    pv_voucher_code,
    o.order_id,
    default_delivery_address_state,
    SUM(o.cogs) cogs,
    SUM(gmv) gmv,
    SUM(o.pv_rebate_by_shopee_amt) pv_rebate_by_shopee_amt
FROM
    mp_order.dwd_order_item_all_ent_df__vn_s0_live o
INNER JOIN
    DATA d
ON
    d.orderid = o.order_id
LEFT JOIN
    user_tab u
ON
    u.shop_id = o.shop_id
LEFT JOIN
    prefix p
ON
    p.promotionid = o.pv_promotion_id
LEFT JOIN
    dim_user du
ON
    o.buyer_id = du.user_id

WHERE
    DATE(split(o.create_datetime, ' ')[1]) > DATE'2020-02-29'
AND
    d.escrow_time BETWEEN DATE'2022-03-01' AND DATE'2022-03-31'
AND
    (regexp_like(o.pv_voucher_code, '^AP.*|AIRPAY') OR regexp_like(o.pv_voucher_code, '^SPP.*|SHOPEEPAY'))
AND
    pv_voucher_code IN ('APMKPNU', 'APMKPEU', 'APMKPEU21', 'APMKPNU21', 'APMKPEU2021', 'SPPMKPNU1021', 'SPPMKPEU1021')
AND
    o.payment_method_id IN (28,30,5)
AND
    o.pv_rebate_by_shopee_amt <> 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
    )
SELECT
SUM(pv_rebate_by_shopee_amt)
-- tba.*, tbb.partner_account_id AS sp_user, tbc.uid AS spp_uid2, tbc.register_mobile_no AS spp_phone,o2.order_id spp_order_id
FROM  tb_o tba
LEFT JOIN --to remove duplicate
(SELECT partner_account_id, max_by(uid, create_time) uid FROM shopeepay.airpay_user_service_vn_db__wallet_partner_account_tab__vn_daily_s0_live GROUP BY 1) tbb ON tba.buyer_id = tbb.partner_account_id
LEFT JOIN --to remove duplicate
(SELECT uid, max_by(register_mobile_no, register_time) register_mobile_no FROM shopeepay.airpay_user_service_vn_db__user_register_tab__vn_daily_s0_live GROUP BY 1) tbc ON tbc.uid = tbb.uid
LEFT JOIN --to remove duplicate
(SELECT DISTINCT CAST(json_extract_scalar(o2.partner_data,'$.checkout_id') AS BIGINT) checkout_id, max_by(o2.order_id, tb1.valid_time) order_id
FROM
shopeepay.airpay_merchant_shopee_txn_vn_db__wallet_order_log_tab__vn_daily_s0_live o2
LEFT JOIN shopeepay.airpay_payment_txn_vn_db__txn_order_tab__vn_daily_s0_live tb1 ON o2.order_id = tb1.order_id
WHERE STATUS = 8
GROUP BY 1) o2
ON CAST(tba.base_checkout_id AS BIGINT) = o2.checkout_id
WHERE 1=1