ONUS KPI Dashboard

-- ONUS GMV
SELECT yearMonth
, COUNT(a.transactionid) AS Txns
, round(SUM(a.amt),2) AS GMV
, COUNT(DISTINCT userid) as user_cnt
FROM
(SELECT DISTINCT CAST(eventamount AS DOUBLE) / 100 AS amt, transactionid, userid,
substr(cast(dl_last_updated as varchar(30)), 1, 7) AS yearMonth
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -5, CURRENT_DATE))
AND eventid IN (SELECT eventlinkid
FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
WHERE dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -5, CURRENT_DATE))
AND payresult IN ('payment_success')))a
GROUP BY yearMonth
ORDER BY yearMonth;

-- ONUS rejected GMV
select year_mnth,
count(transactionid) as txns,
sum(amt) as rej_gmv
, COUNT(DISTINCT userid) AS users_blocked
from
(select distinct userid, transactionid, cast(eventAmount as double) / 100 as amt,
substr(cast(dateinserted as varchar(30)), 1, 7) as year_mnth
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -6, CURRENT_DATE))
AND actionrecommended = 'BLOCK' AND SOURCE = 'PG') a
group by year_mnth
ORDER BY year_mnth;

-- ONUS FRAUD GMV
SELECT yearMonth
, COUNT(transactionid) AS Fraud_Txns
, round(SUM(amt),2) AS Fraud_GMV
, COUNT(DISTINCT userid) as Fraud_user_cnt
FROM
(SELECT DISTINCT A.transactionid, A.amt, A.userid, A.yearMonth FROM
(SELECT DISTINCT transactionid, CAST(eventamount AS DOUBLE) / 100 AS amt, userid,
substr(cast(dl_last_updated as varchar(30)), 1, 7) AS yearMonth
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -5, CURRENT_DATE))
AND eventid IN (SELECT eventlinkid
FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
WHERE dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -5, CURRENT_DATE))
AND payresult IN ('payment_success')))A
INNER JOIN
(SELECT DISTINCT transaction_id
FROM frauds.ppsl_cybercell_snapshot_v3
WHERE dl_last_updated >= DATE_TRUNC('month', DATE_ADD('month', -5, CURRENT_DATE))
AND transaction_id NOT IN ('', ' ', 'NA', 'N.A', '0') AND transaction_id IS NOT NULL)B
ON A.transactionid = B.transaction_id)X
GROUP BY yearMonth
ORDER BY yearMonth;