Chatbot Data

 
select pg.mid,
pg.Month,
CASE
  WHEN m_3.o_mid IS NOT NULL THEN 'Online'
  WHEN m_4.e_mid IS NOT NULL THEN 'EDC'
  ELSE 'QR'
 END AS EDC_QR,
  m_7.channel as subBusiness,
 mcc.mcc as mccCode,
 pg.category,
 pg.subCategory,
 sum(pg.attemptedGMV)as attemptedGMV,
 sum(pg.seccessfulGMV) asseccessfulGMV ,
 sum(pg.refundGMV) refundGMV,
sum(rej.rejectedGMV)rejectedGMV ,
sum(pg.failedGMV) failedGMV,
sum(frd.fraudGMV) fraudGMV,
sum(cb.cbGMV) cbGMV
 from
(SELECT mid, month(txn_started_at) as Month,category,sub_category subCategory,
       sum(txn_amount) as attemptedGMV, sum(Case when txn_status = 'SUCCESS'  then txn_amount else 0 end) as seccessfulGMV,
       sum(Case when txn_status <> 'SUCCESS'  then txn_amount else 0 end) as failedGMV,
       sum(case when txn_status = 'SUCCESS' and refund_amount is not null and refund_amount > 0 then txn_amount end) as refundGMV
                FROM
                dwh.pg_olap
                WHERE category NOT IN ('OnPaytm', 'Test')
                AND mid IS NOT NULL
                AND txn_amount > 0
                AND txn_id IS NOT NULL
                AND ingest_date >=  date '2024-10-01'
                AND DATE (substr(cast(txn_started_at AS VARCHAR), 1, 10))  between date '2024-10-01' and date '2025-02-28'
group by 1,2,3,4) as pg

-- Mapping QR/EDC mids
LEFT JOIN (
  SELECT DISTINCT merchant_id AS o_mid
  FROM datalake.online_payment_merchants
 ) AS m_3 
 ON pg.mid = m_3.o_mid
 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'
 ) m_4 ON pg.mid = m_4.e_mid

 -- Mapping Channel here
  LEFT Join (
  select distinct
   pg_mid,
   channel
  from
   cdo.total_offline_merchant_base_snapshot_v3
 ) AS m_7 ON pg.mid = m_7.pg_mid
 left join
 (SELECT  distinct upi_mcc_code mcc,
          category ,
          sub_category  
          FROM paytmpgdb.mcc_code_mapping_snapshot_v3) mcc 
          on pg.category = mcc.category and pg.subCategory = mcc.sub_category

-- Mapping rejected GMV
left join 
(select mid,month(dateinserted) as Month,
sum(txn_amount) rejectedGMV
from (
select
transactionid,
max(dateinserted) dateinserted ,
max(cast(eventamount as double)/100) txn_amount            ,
max(paytmmerchantid) mid 
from
cdp_risk_transform.maquette_flattened_offus_snapshot_v3
where 
dl_last_updated >= date'2024-10-01'
and DATE (dateinserted)  between date '2024-10-01' and date '2025-02-28'
AND actionrecommended = 'BLOCK'
group by 1)
group by 1,2) as rej ON pg.mid = rej.mid and pg.Month=rej.Month

-- Mapping fraudGMV
left join
(select mid,month(txn_date) as Month,
sum(txn_amount) fraudGMV
from
        (select
          old_pg_txn_id as txn_id,
          min(cast(old_pg_txn_amount as double)) txn_amount,
          min(date(old_pg_txn_started_at)) txn_date,
          min(old_pg_ingest_date) old_pg_ingest_date,
          min(old_pg_mid) mid
        from
          frauds.fraud_combined_snapshot_v3
        where
        dl_last_updated >= date'2024-10-01'
        and DATE (old_pg_txn_started_at)  between date '2024-10-01' and date '2025-02-28'
          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'
          )
          and old_pg_category NOT IN ('OnPaytm', 'Test')
          and old_pg_txn_status = 'SUCCESS'
          AND old_pg_mid IS NOT NULL
          AND cast(old_pg_txn_amount as double) > 0
          AND old_pg_txn_id IS NOT NULL
        group by 1)
        group by 1,2) as frd on pg.mid=frd.mid and pg.Month=frd.Month
-- Mapping chargeBack
left join (select mid,
       month(date(substr(CAST (old_pg_txn_started_at AS varchar), 1, 10))) Month ,
       count(transaction_id) cbCount,
       sum(cbGMV) cbGMV
from (
select 
transaction_id, 
max(old_pg_mid) mid,
sum(chargeback_amount) cbGMV,
max(old_pg_txn_started_at) old_pg_txn_started_at
from
 cdp_risk_transform.transaction_level_chargeback_snapshot_v3
WHERE old_pg_ingest_date >= date'2024-10-01'
  AND date(substr(CAST (old_pg_txn_started_at AS varchar), 1, 10)) 
  between date'2024-10-01' and date '2025-02-28'
group by 1)
group by 1,2) as cb on pg.mid=cb.mid and pg.Month=cb.Month
where pg.mid = 'NEHAEL54380289516455'
group  by 1,2,3,4,5,6,7;