Cybercell reported txns

-- PARDEEP QUERY
SELECT A.*
, COALESCE(B.deviceid, C.deviceid) as deviceid
, COALESCE(B.subscriberid, C.subscriberid) as subscriberid
, COALESCE(B.paymethod, C.paymethod) as paymethod
, COALESCE(B.usergeohash4, C.usergeohash4) as usergeohash4
, COALESCE(B.paytmmerchantid, COALESCE(EDC.e_mid, QR.merchant_id)) as merchant_type
FROM
    (SELECT *
    FROM team_team_risk.Last_4_Months_I4C_Cybercell_data)A
LEFT JOIN
    -- ONUS USERS
    (select distinct transactionid, deviceid, subscriberid, paymethod, usergeohash4, paytmmerchantid
    FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
    WHERE dl_last_updated >= date'2024-01-01')B
ON A.txn_id = B.transactionid
LEFT JOIN
    -- OFFUS USERS
    (select distinct transactionid, deviceid, subscriberid, paymethod, usergeohash4, paytmmerchantid
    FROM cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    WHERE dl_last_updated >= date'2024-01-01')C
ON A.txn_id = B.transactionid
LEFT JOIN
    (SELECT DISTINCT mid AS e_mid FROM paytmpgdb.entity_edc_info_snapshot_v3 
    WHERE terminal_status = 'ACTIVE' AND dl_last_updated >= DATE '2010-01-01')EDC
ON C.paytmmerchantid = EDC.e_mid
LEFT JOIN 
    (SELECT DISTINCT merchant_id from datalake.online_payment_merchants)QR
ON C.paytmmerchantid = QR.merchant_id
LIMIT 100
;