-- Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck
DROP TABLE team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches;
-- CREATE TABLE team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches AS
INSERT INTO team_kingkong.onus_Fastag_Trusted_VRN_CCDC_Weekly_Monthly_limitCheck_breaches
with onus_txn_base as
(SELECT DISTINCT A.*, case when m1.mid is not null then category else 'Others' end as business_category FROM
(select userid, transactionid as txn_id,
cast(eventAmount as double) / 100 as txn_amount,
dateinserted as txn_date,
substr(cast(dateinserted as varchar(30)), 1, 7) as yearMonth,
paymethod, paytmmerchantid, responsestatus, actionrecommended, velocitytimestamp
, subscriberid as vrn
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND SOURCE = 'PG'
AND responsestatus IN ('SUCCESS') AND actionrecommended <> 'BLOCK'
AND paytmmerchantid IN ('PTMFVT32998068120662') AND paymethod IN ('DEBIT_CARD', 'CREDIT_CARD')
AND eventid IN (SELECT eventlinkid
FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
WHERE dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND payresult = 'payment_success')) a
left join
(select * from team_kingkong.voc_mid_categorization where mid != '') m1
on a.paytmmerchantid = m1.mid)
SELECT *, CASE
WHEN (txn_amount + week_txn_amount) >= week_threshold
AND (txn_amount + month_txn_amount) >= month_threshold THEN 'week+month_limit_breached'
WHEN (txn_amount + week_txn_amount) >= week_threshold THEN 'week_limit_breached'
WHEN (txn_amount + month_txn_amount) >= month_threshold THEN 'month_limit_breached'
ELSE NULL END AS breach_reason FROM
(SELECT A.*
, SUM(IF(DATE(B.txn_date) BETWEEN DATE(DATE(A.txn_date) - INTERVAL '7' DAY) AND DATE(A.txn_date), B.txn_amount, NULL)) AS week_txn_amount
, 25295 AS week_threshold
-- No.of attempted txns per calendar month > 30 (consider only the CCBP transactions)
, SUM(IF(DATE(B.txn_date) BETWEEN date_trunc('month', DATE(A.txn_date)) AND DATE(A.txn_date), B.txn_amount, NULL)) AS month_txn_amount
, 50590 AS month_threshold
FROM
(SELECT * FROM onus_txn_base
WHERE DATE(txn_date) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
)A
INNER JOIN
(SELECT * FROM onus_txn_base)B
ON A.vrn = B.vrn AND A.txn_id <> B.txn_id AND B.velocitytimestamp < A.velocitytimestamp
AND DATE(B.txn_date) BETWEEN DATE(A.txn_date - INTERVAL '30' DAY) AND DATE(A.txn_date)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12)
WHERE ((txn_amount + week_txn_amount) >= week_threshold) OR ((txn_amount + month_txn_amount) >= month_threshold);
--- on_us_loan_repayments_user_limits
DROP TABLE team_kingkong.onus_on_us_loan_repayments_user_limits_breaches;
-- CREATE TABLE team_kingkong.onus_on_us_loan_repayments_user_limits_breaches AS
INSERT INTO team_kingkong.onus_on_us_loan_repayments_user_limits_breaches
with onus_txn_base as
(
SELECT DISTINCT A.*, case when m1.mid is not null then category else 'Others' end as business_category FROM
(select userid, subscriberid, transactionid as txn_id,
cast(eventAmount as double) / 100 as txn_amount,
dateinserted as txn_date,
substr(cast(dateinserted as varchar(30)), 1, 7) as yearMonth,
paymethod, paytmmerchantid, responsestatus, actionrecommended, velocitytimestamp
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND SOURCE = 'PG'
AND verticalid = '56' AND categoryid = '37217' AND responsestatus = 'SUCCESS' AND actionrecommended <> 'BLOCK'
AND eventid IN (SELECT eventlinkid
FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
WHERE dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND payresult = 'payment_success')) a
left join
(select * from team_kingkong.voc_mid_categorization where mid != '') m1
on a.paytmmerchantid = m1.mid
)
SELECT *, 'monthly threshold breached' as breach_reason FROM
(SELECT A.*
, COUNT(DISTINCT IF(DATE(B.txn_date) BETWEEN DATE(DATE(A.txn_date) - INTERVAL '2592000' SECOND) AND DATE(A.txn_date), B.txn_id, NULL)) AS txn_succ_month
, 20 AS txn_succ_month_threshold
FROM
(SELECT * FROM onus_txn_base
WHERE DATE(txn_date) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31')A
INNER JOIN
(SELECT * FROM onus_txn_base)B
ON A.userid = B.userid AND A.subscriberid = B.subscriberid AND A.txn_id <> B.txn_id AND B.velocitytimestamp < A.velocitytimestamp
AND DATE(B.txn_date) BETWEEN DATE(A.txn_date - INTERVAL '30' DAY) AND DATE(A.txn_date)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12)
WHERE txn_succ_month >= txn_succ_month_threshold;
-- Fastag_NonTrustedUser_CCDC_Monthly_limitCheck
DROP TABLE team_kingkong.onus_Fastag_NonTrustedUser_CCDC_Monthly_limitCheck_breaches;
-- CREATE TABLE team_kingkong.onus_Fastag_NonTrustedUser_CCDC_Monthly_limitCheck_breaches AS
INSERT INTO team_kingkong.onus_Fastag_NonTrustedUser_CCDC_Monthly_limitCheck_breaches
with onus_txn_base as
(SELECT DISTINCT A.*, case when m1.mid is not null then category else 'Others' end as business_category FROM
(select userid, transactionid as txn_id,
cast(eventAmount as double) / 100 as txn_amount,
dateinserted as txn_date,
substr(cast(dateinserted as varchar(30)), 1, 7) as yearMonth,
paymethod, paytmmerchantid, responsestatus, actionrecommended, velocitytimestamp
, subscriberid as vrn
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND SOURCE = 'PG'
AND responsestatus IN ('SUCCESS') AND actionrecommended <> 'BLOCK'
AND paytmmerchantid IN ('PTMFVT32998068120662') AND paymethod IN ('DEBIT_CARD', 'CREDIT_CARD')
AND eventid IN (SELECT eventlinkid
FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
WHERE dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND payresult = 'payment_success')) a
left join
(select * from team_kingkong.voc_mid_categorization where mid != '') m1
on a.paytmmerchantid = m1.mid)
SELECT *, CASE
WHEN (txn_amount + week_txn_amount) >= week_threshold AND (txn_amount + month_txn_amount) >= month_threshold THEN 'week and month threshold breached'
WHEN (txn_amount + week_txn_amount) >= week_threshold THEN 'week threshold breached'
WHEN (txn_amount + month_txn_amount) >= month_threshold THEN 'month threshold breached'
ELSE NULL END AS breach_reason FROM
(SELECT A.*
, SUM(IF(DATE(B.txn_date) BETWEEN DATE(DATE(A.txn_date) - INTERVAL '7' DAY) AND DATE(A.txn_date), B.txn_amount, NULL)) AS week_txn_amount
, 1011800 AS week_threshold
, SUM(IF(DATE(B.txn_date) BETWEEN date_trunc('month', DATE(A.txn_date)) AND DATE(A.txn_date), B.txn_amount, NULL)) AS month_txn_amount
, 2023600 AS month_threshold
FROM
(SELECT * FROM onus_txn_base
WHERE DATE(txn_date) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31'
)A
INNER JOIN
(SELECT * FROM onus_txn_base)B
ON A.vrn = B.vrn AND A.txn_id <> B.txn_id AND B.velocitytimestamp < A.velocitytimestamp
AND DATE(B.txn_date) BETWEEN DATE(A.txn_date - INTERVAL '30' DAY) AND DATE(A.txn_date)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12)
WHERE ((txn_amount + week_txn_amount) >= week_threshold) OR ((txn_amount + month_txn_amount) >= month_threshold);
DROP TABLE team_kingkong.onus_CCBP_GMV_per_user_1d_7d_30d_breaches;
-- CREATE TABLE team_kingkong.onus_CCBP_GMV_per_user_1d_7d_30d_breaches AS
INSERT INTO team_kingkong.onus_CCBP_GMV_per_user_1d_7d_30d_breaches
with onus_txn_base as
(SELECT DISTINCT A.*, case when m1.mid is not null then category else 'Others' end as business_category FROM
(select userid, transactionid as txn_id,
cast(eventAmount as double) / 100 as txn_amount,
dateinserted,
substr(cast(dateinserted as varchar(30)), 1, 7) as yearMonth,
paymethod, paytmmerchantid, velocitytimestamp
FROM cdp_risk_transform.maquette_flattened_onus_snapshot_v3
WHERE DATE(dl_last_updated) BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND SOURCE = 'PG'
AND paytmmerchantid IN ('PTMCBP84799392178473','PTMVIS48435535949128','PTMCBP11428987150800')
AND eventid IN (SELECT eventlinkid
FROM risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
WHERE dl_last_updated BETWEEN DATE(DATE'2025-01-01' - INTERVAL '30' DAY) AND DATE'2025-01-31'
AND payresult = 'payment_success')
AND responsestatus ='SUCCESS' AND actionrecommended <> 'BLOCK') a
left join
(select * from team_kingkong.voc_mid_categorization where mid != '') m1
on a.paytmmerchantid = m1.mid)
SELECT *, CASE
WHEN (txn_amount + txn_txn_amount_same_day) > txn_txn_amount_same_day_threshold
AND (txn_amount + txn_txn_amount_7_day) > txn_txn_amount_7_day_threshold
AND (txn_amount + txn_txn_amount_cal_month) > txn_txn_amount_cal_month_threshold THEN 'same day, 7-day and month threshold breached'
WHEN (txn_amount + txn_txn_amount_same_day) > txn_txn_amount_same_day_threshold
AND (txn_amount + txn_txn_amount_7_day) > txn_txn_amount_7_day_threshold THEN 'same day and 7-day threshold breached'
WHEN (txn_amount + txn_txn_amount_same_day) > txn_txn_amount_same_day_threshold
AND (txn_amount + txn_txn_amount_cal_month) > txn_txn_amount_cal_month_threshold THEN 'same day and month threshold breached'
WHEN (txn_amount + txn_txn_amount_7_day) > txn_txn_amount_7_day_threshold
AND (txn_amount + txn_txn_amount_cal_month) > txn_txn_amount_cal_month_threshold THEN '7-day and month threshold breached'
WHEN (txn_amount + txn_txn_amount_same_day) > txn_txn_amount_same_day_threshold THEN 'same day threshold breached'
WHEN (txn_amount + txn_txn_amount_7_day) > txn_txn_amount_7_day_threshold THEN '7-day threshold breached'
WHEN (txn_amount + txn_txn_amount_cal_month) > txn_txn_amount_cal_month_threshold THEN 'month threshold breached'
ELSE NULL END AS breach_reason FROM
(SELECT A.*
-- No.of successful txns per user per calendar day > 8 (consider only the CCBP transactions)
, SUM(IF(DATE(B.dateinserted) = DATE(A.dateinserted), B.txn_amount, 0)) AS txn_txn_amount_same_day
, 100000000/100 AS txn_txn_amount_same_day_threshold
-- No.of successful txns last 7 days > 15 (consider only the CCBP transactions)
, SUM(IF(B.dateinserted BETWEEN (DATE(A.dateinserted) - INTERVAL '604800' SECOND) AND A.dateinserted, B.txn_amount, 0)) AS txn_txn_amount_7_day
, 250000000/100 AS txn_txn_amount_7_day_threshold
-- No.of successful txns per calendar month > 25 (consider only the CCBP transactions)
, SUM(IF(B.dateinserted BETWEEN date_trunc('month', DATE(A.dateinserted)) AND A.dateinserted, B.txn_amount, 0)) AS txn_txn_amount_cal_month
, 500000000/100 AS txn_txn_amount_cal_month_threshold
FROM
(SELECT * FROM onus_txn_base
WHERE DATE(dateinserted) BETWEEN DATE'2025-01-01' AND DATE'2025-01-31')A
INNER JOIN
(SELECT * FROM onus_txn_base)B
ON A.userid = B.userid AND A.txn_id <> B.txn_id AND B.velocitytimestamp < A.velocitytimestamp
AND DATE(B.dateinserted) BETWEEN DATE(A.dateinserted - INTERVAL '30' DAY) AND DATE(A.dateinserted)
GROUP BY 1,2,3,4,5,6,7,8,9)
WHERE ((txn_amount + txn_txn_amount_same_day) > txn_txn_amount_same_day_threshold) OR ((txn_amount + txn_txn_amount_7_day) > txn_txn_amount_7_day_threshold) OR ((txn_amount + txn_txn_amount_cal_month) > txn_txn_amount_cal_month_threshold)
;
