-- OFFUS overall GMV
select substr(cast(dateinserted as varchar(30)), 1, 7) AS yearMonth
, COUNT(transactionid) AS txn_cnt,
SUM(cast(eventamount as double)/100) as txn_amount
, COUNT(DISTINCT case when paymethod = 'UPI' then vpa
when paymethod in ('CREDIT_CARD', 'DEBIT_CARD','EMI','EMI_DC') then globalcardindex end) as user_cnt
from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
where dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -5, CURRENT_DATE))
AND actionrecommended <> 'BLOCK'
GROUP BY substr(cast(dateinserted as varchar(30)), 1, 7);

-- OFFUS REJECTED GMV
CREATE TABLE team_team_risk.offus_rejected_gmv AS
select substr(cast(dateinserted as varchar(30)), 1, 7) AS yearMonth
, COUNT(transactionid) AS txn_cnt,
SUM(cast(eventamount as double)/100) as txn_amount
, COUNT(DISTINCT case when paymethod = 'UPI' then vpa
when paymethod in ('CREDIT_CARD', 'DEBIT_CARD','EMI','EMI_DC') then globalcardindex end) as users_blocked
from cdp_risk_transform.maquette_flattened_offus_snapshot_v3
where dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -5, CURRENT_DATE))
AND actionrecommended = 'BLOCK'
GROUP BY substr(cast(dateinserted as varchar(30)), 1, 7);

-- OFFUS FRAUD GMV
SELECT substr(cast(B.txn_date as varchar(30)), 1, 7) as year_month
, COUNT(txn_id) AS fraud_cnt
, SUM(txn_amt) AS fraud_amt
, COUNT(DISTINCT B.users) AS user_cnt
FROM
    (SELECT DISTINCT old_pg_txn_id as txn_id, cast(old_pg_txn_amount as double) as txn_amt
    from frauds.fraud_combined_snapshot_v3
    where old_pg_ingest_date >= DATE_TRUNC('month', DATE_ADD('month', -6, CURRENT_DATE))
    and dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -6, CURRENT_DATE))
    and date(old_pg_txn_started_at) >= DATE_TRUNC('month', DATE_ADD('month', -6, CURRENT_DATE))
    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 old_pg_txn_id, old_pg_txn_amount)A
INNER JOIN
    (SELECT DISTINCT dl_last_updated as txn_date, transactionid, case when paymethod = 'UPI' then vpa
    when paymethod in ('CREDIT_CARD', 'DEBIT_CARD','EMI','EMI_DC') then globalcardindex end as users
    FROM cdp_risk_transform.maquette_flattened_offus_snapshot_v3
    WHERE dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -6, CURRENT_DATE)))B
ON A.txn_id = B.transactionid
GROUP BY substr(cast(txn_date as varchar(30)), 1, 7)
ORDER BY year_month;