OFFUS Dec onwards txn query for KPI base

create table team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec_Feb2 as
select g.*, h.final_status as final_cb_status, h.MID as cb_mid, h.txn_id as cb_txnid,
h.amount as cb_amount, h.RecoveredAmount as cb_RecoveredAmount, k.mid as fraud_mid,
k.txn_id as fraud_txnid, k.txn_amount as fraud_amount, k.refund_amount as refunded_fraud_gmv
from
--- creating olap from pgolap and M* flattened ---
(select distinct c., d. from
    (select a.*, f.channel, case when edc_mid is not null then 'EDC' else 'QR' end as mid_type from
        (SELECT DISTINCT pg_mid, channel from cdo.total_offline_merchant_base_snapshot_v3) f
    join
        (select distinct actionrecommended, cardtype,   corporatecard, dateinserted,
        isindian, isupicc, merchantcategory, merchantid, merchantsubcategory, paymethod,
        paytmmerchantid, prepaidcard, transactionid, userid, vpa,
        month(dateinserted) as mt, case when paymethod = 'UPI' then vpa
        when paymethod in ('CREDIT_CARD', 'DEBIT_CARD','EMI','EMI_DC') then globalcardindex end as users,
        cast(eventamount as double)/100 as txn_amount
        from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
        where dl_last_updated >= date '2024-12-01'
        and paymethod in ('UPI','CREDIT_CARD','DEBIT_CARD','EMI','EMI_DC')) a
    on a.paytmmerchantid = f.pg_mid
    LEFT JOIN
        -- LIST OF EDC MX
        (SELECT DISTINCT mid AS edc_mid FROM paytmpgdb.entity_edc_info_snapshot_v3
        WHERE terminal_status = 'ACTIVE' AND dl_last_updated >= DATE '2010-01-01') b
    ON a.paytmmerchantid = b.edc_mid)c
left join
    -- TABLE TO GET STATUS OF TXN
    (select distinct refund_amount, txn_id, txn_started_at, txn_status
    from dwh.pg_olap
    where ingest_date >= date '2024-12-01'
    and txn_started_at >= date '2024-12-01'
    and txn_status in ('SUCCESS','CLOSED'))d
on c.transactionid = d.txn_id) g
left join
    --- mapping data with CB Data ---
    (select * from
    (select distinct a.*, ROW_NUMBER() OVER (PARTITION BY txn_id ORDER BY
    CASE WHEN final_status = 'ACCEPTED' THEN 1
    WHEN final_status IN ('OPEN', 'DEFENDED') THEN 2 ELSE 3 END,
    Intimation_Date DESC) AS row_num
    from
(SELECT distinct a.dispute_id, a.transaction_date AS Txn_Date, a.transaction_id as txn_id,
CASE WHEN a.from_status='ACCEPT' AND a.to_status='CLOSED' THEN 'ACCEPTED'
WHEN a.from_status='DEFEND' AND a.to_status='CLOSED' THEN 'DEFENDED'
WHEN a.from_status='PROCESSING' AND a.to_status='ACCEPT' THEN 'ACCEPTED'
WHEN a.from_status='POD_UPLOAD' AND a.to_status='DEFEND' THEN 'DEFENDED'
WHEN a.from_status='POD_UPLOAD' AND a.to_status='ACCEPT' THEN 'ACCEPTED'
WHEN a.from_status='POD_REJECT' AND a.to_status='ACCEPT' THEN 'ACCEPTED'
WHEN a.from_status='INIT' AND a.to_status='PROCESSING' THEN 'OPEN'
ELSE 'OPEN' END AS final_status, a.created_on as Intimation_Date,
CAST(b.original_amount AS DECIMAL (18,2)) / 100 as amount,
b.payer_id as MID, CAST(b.recovered_amount AS DECIMAL (18,2)) / 100 as RecoveredAmount,
b.merchant_trans_id as Order_ID
FROM pgplusbo.chargeback_details_snapshot_v3 a
JOIN
pgaws_datalake_prod2.ded_order_snapshot_v3 b
ON a.dispute_id = b.bill_id
WHERE a.dl_last_updated >= date '2024-12-01'
AND b.dl_last_updated >= date '2024-12-01'
and date(transaction_date) >= date '2024-12-01') a
)x where row_num = 1) h
on g.transactionid = h.txn_id
left join
--- mapping data with Fraud Data ---
(select * from
(select distinct old_pg_txn_id as txn_id, cast(old_pg_txn_amount as double) as txn_amount,
date(old_pg_txn_started_at) as txn_date, old_pg_mid as mid,
min(reporting_date) as reporting_date
from frauds.fraud_combined_snapshot_v3
where old_pg_ingest_date >= date'2024-12-01'
and dl_last_updated >= date'2024-12-01'
and date(old_pg_txn_started_at) >= date'2024-12-01'
and ((table_name in ('ppsl_cybercell','ro_panel_cybmerchant_details_with_pg_olap',
'lending_fraud','efrm','ppsl_bank_escalations','ro_panel_minifmr_l2_PPI',
'ro_panel_minifmr_l2_BNK')) or
(sources_concatenated like '%ppsl_cybercell%'
or sources_concatenated like '%ro_panel_cybmerchant_details_with_pg_olap%'
or sources_concatenated like '%lending_fraud%'
or sources_concatenated like '%efrm%'
or sources_concatenated like '%ppsl_bank_escalations%'
or sources_concatenated like '%ro_panel_minifmr_l2_PPI%'
or sources_concatenated like '%ro_panel_minifmr_l2_BNK%'))
and old_pg_txn_status = 'SUCCESS' AND cast(old_pg_txn_amount as double) > 0
AND old_pg_txn_id IS NOT NULL
group by 1,2,3,4) i
left join
(SELECT distinct acq_id, CAST(refund_amount AS DOUBLE) / 100 AS refund_amount
FROM pgaws_datalake_prod.acq_refund_snapshot_v3
WHERE dl_last_updated >= date'2024-12-01' AND refund_status = 'SUCCESS') j
ON i.txn_id = j.acq_id) k
on g.transactionid = k.txn_id;



---------------------------------


create table team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2 as
select DISTINCT g.*, h.final_status as final_cb_status, h.MID as cb_mid, h.txn_id as cb_txnid,
h.amount as cb_amount, h.RecoveredAmount as cb_RecoveredAmount, k.mid as fraud_mid,
k.txn_id as fraud_txnid, k.txn_amount as fraud_amount, k.refund_amount as refunded_fraud_gmv
from
--- creating olap from pgolap and M* flattened ---
(select distinct c.*, d.* from
(select a.*, f.channel, case when edc_mid is not null then 'EDC' else 'QR' end as mid_type
from
(SELECT DISTINCT pg_mid, channel from cdo.total_offline_merchant_base_snapshot_v3) f
join
(select distinct actionrecommended, dateinserted, merchantcategory, isindian,
merchantsubcategory, paymethod, paytmmerchantid, transactionid,
month(dateinserted) as mt, case when paymethod = 'UPI' then vpa
when paymethod in ('CREDIT_CARD', 'DEBIT_CARD','EMI','EMI_DC') then globalcardindex end as users,
cast(eventamount as double)/100 as txn_amount
from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
where dl_last_updated BETWEEN date '2024-12-01' AND date '2024-12-31'
and paymethod in ('UPI','CREDIT_CARD','DEBIT_CARD','EMI','EMI_DC')
group by 1,2,3,4,5,6,7,8,9,10, 11) a
on a.paytmmerchantid = f.pg_mid
LEFT JOIN
(SELECT DISTINCT mid AS edc_mid FROM paytmpgdb.entity_edc_info_snapshot_v3
WHERE terminal_status = 'ACTIVE' AND dl_last_updated >= DATE '2010-01-01') b
ON a.paytmmerchantid = b.edc_mid
)c
left join
(select distinct refund_amount, txn_id, txn_status, mcc, entity_id
from dwh.pg_olap
where ingest_date BETWEEN date '2024-12-01' AND date '2024-12-31'
and txn_started_at BETWEEN date '2024-12-01' AND date '2024-12-31'
and txn_status in ('SUCCESS','CLOSED'))d
on c.transactionid = d.txn_id) g


left join
--- mapping data with CB Data ---
(select * from
(select distinct a.*, ROW_NUMBER() OVER (PARTITION BY txn_id ORDER BY
CASE WHEN final_status = 'ACCEPTED' THEN 1
WHEN final_status IN ('OPEN', 'DEFENDED') THEN 2 ELSE 3 END,
Intimation_Date DESC) AS row_num
from
(SELECT distinct a.dispute_id, a.transaction_date AS Txn_Date, a.transaction_id as txn_id,
CASE WHEN a.from_status='ACCEPT' AND a.to_status='CLOSED' THEN 'ACCEPTED'
WHEN a.from_status='DEFEND' AND a.to_status='CLOSED' THEN 'DEFENDED'
WHEN a.from_status='PROCESSING' AND a.to_status='ACCEPT' THEN 'ACCEPTED'
WHEN a.from_status='POD_UPLOAD' AND a.to_status='DEFEND' THEN 'DEFENDED'
WHEN a.from_status='POD_UPLOAD' AND a.to_status='ACCEPT' THEN 'ACCEPTED'
WHEN a.from_status='POD_REJECT' AND a.to_status='ACCEPT' THEN 'ACCEPTED'
WHEN a.from_status='INIT' AND a.to_status='PROCESSING' THEN 'OPEN'
ELSE 'OPEN' END AS final_status, a.created_on as Intimation_Date,
CAST(b.original_amount AS DECIMAL (18,2)) / 100 as amount,
b.payer_id as MID, CAST(b.recovered_amount AS DECIMAL (18,2)) / 100 as RecoveredAmount,
b.merchant_trans_id as Order_ID
FROM pgplusbo.chargeback_details_snapshot_v3 a
JOIN
pgaws_datalake_prod2.ded_order_snapshot_v3 b
ON a.dispute_id = b.bill_id
WHERE a.dl_last_updated BETWEEN date '2024-12-01' AND date '2024-12-31'
and date(transaction_date) BETWEEN date '2024-12-01' AND date '2024-12-31'
AND b.dl_last_updated >= date '2024-12-01') a
)x where row_num = 1) h
on g.transactionid = h.txn_id


left join
--- mapping data with Fraud Data ---
(select * from
(select distinct old_pg_txn_id as txn_id, cast(old_pg_txn_amount as double) as txn_amount,
date(old_pg_txn_started_at) as txn_date, old_pg_mid as mid,
min(reporting_date) as reporting_date
from frauds.fraud_combined_snapshot_v3
where old_pg_ingest_date >= date'2024-12-01'
and dl_last_updated >= date'2024-12-01'
and date(old_pg_txn_started_at) >= date'2024-12-01'
and ((table_name in ('ppsl_cybercell','ro_panel_cybmerchant_details_with_pg_olap',
'lending_fraud','efrm','ppsl_bank_escalations','ro_panel_minifmr_l2_PPI',
'ro_panel_minifmr_l2_BNK')) or
(sources_concatenated like '%ppsl_cybercell%'
or sources_concatenated like '%ro_panel_cybmerchant_details_with_pg_olap%'
or sources_concatenated like '%lending_fraud%'
or sources_concatenated like '%efrm%'
or sources_concatenated like '%ppsl_bank_escalations%'
or sources_concatenated like '%ro_panel_minifmr_l2_PPI%'
or sources_concatenated like '%ro_panel_minifmr_l2_BNK%'))
and old_pg_txn_status = 'SUCCESS' AND cast(old_pg_txn_amount as double) > 0
AND old_pg_txn_id IS NOT NULL
group by 1,2,3,4) i
left join
(SELECT distinct acq_id, CAST(refund_amount AS DOUBLE) / 100 AS refund_amount
FROM pgaws_datalake_prod.acq_refund_snapshot_v3
WHERE dl_last_updated >= date'2024-12-01' AND refund_status = 'SUCCESS') j
ON i.txn_id = j.acq_id) k
on g.transactionid = k.txn_id ;