CREATE TABLE team_kingkong.offus_lmtd_mtd_base_data2 AS
SELECT
a.mt,
a.mid_type,
a.isindian,
a.merchantcategory,
a.paymethod,
a.mcc,
a.entity_id,
success_users,
refunded_success_users,
rejected_users,
failed_users,
total_unique_users,
success_merchants,
refunded_success_merchants,
rejected_merchants,
failed_merchants,
total_unique_merchants,
success_txns,
refunded_success_txns,
rejected_txns,
failed_txns,
success_gmv,
refunded_success_gmv,
rejected_gmv,
failed_gmv,
fraud_merchants,
refunded_fraud_merchants,
fraud_txns,
refunded_fraud_txns,
fraud_gmv,
refunded_fraud_gmv,
CAST((fraud_gmv / success_gmv) * 10000 AS DECIMAL(18, 2)) AS gross_fts,
CAST(
(
(fraud_gmv - refunded_fraud_gmv) / (success_gmv - refunded_success_gmv)
) * 10000 AS DECIMAL(18, 2)
) AS net_fts,
cb_merchants,
cb_txns,
cb_gmv,
CAST((cb_gmv / success_gmv) * 10000 AS DECIMAL(18, 2)) AS cts,
accepted_cb_merchants,
accepted_cb_txns,
accepted_cb_gmv,
defended_cb_merchants,
defended_cb_txns,
defended_cb_gmv,
open_cb_merchants,
open_cb_txns,
open_cb_gmv,
rec_pend_cb_merchants,
rec_pend_cb_txns,
rec_pend_cb_gmv
FROM
(
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT users) AS total_unique_users,
COUNT(DISTINCT paytmmerchantid) AS total_unique_merchants
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
users,
paytmmerchantid
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS x
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT users) AS success_users,
COUNT(DISTINCT paytmmerchantid) AS success_merchants,
COUNT(transactionid) AS success_txns,
SUM(txn_amount) AS success_gmv,
COUNT(DISTINCT cb_mid) AS cb_merchants,
COUNT(cb_txnid) AS cb_txns,
SUM(cb_amount) AS cb_gmv,
COUNT(DISTINCT fraud_mid) AS fraud_merchants,
COUNT(fraud_txnid) AS fraud_txns,
SUM(fraud_amount) AS fraud_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
users,
paytmmerchantid,
txn_amount,
cb_mid,
cb_txnid,
cb_amount,
fraud_mid,
fraud_txnid,
fraud_amount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'PASS'
AND txn_status = 'SUCCESS'
AND DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS a ON x.mt = a.mt
AND x.mid_type = a.mid_type
AND a.isindian = x.isindian
AND a.merchantcategory = x.merchantcategory
AND a.paymethod = x.paymethod
AND a.mcc = x.mcc
AND a.entity_id = x.entity_id
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT users) AS failed_users,
COUNT(DISTINCT paytmmerchantid) AS failed_merchants,
COUNT(transactionid) AS failed_txns,
SUM(txn_amount) AS failed_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
users,
paytmmerchantid,
txn_amount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'PASS'
AND txn_status = 'CLOSED'
AND DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS b ON a.mt = b.mt
AND a.mid_type = b.mid_type
AND a.isindian = b.isindian
AND a.merchantcategory = b.merchantcategory
AND a.paymethod = b.paymethod
AND a.mcc = b.mcc
AND a.entity_id = b.entity_id
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT users) AS rejected_users,
COUNT(DISTINCT paytmmerchantid) AS rejected_merchants,
COUNT(transactionid) AS rejected_txns,
SUM(txn_amount) AS rejected_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
users,
paytmmerchantid,
txn_amount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'BLOCK'
AND DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS c ON a.mt = c.mt
AND a.mid_type = c.mid_type
AND a.isindian = c.isindian
AND a.merchantcategory = c.merchantcategory
AND a.paymethod = c.paymethod
AND a.mcc = c.mcc
AND a.entity_id = c.entity_id
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT users) AS refunded_success_users,
COUNT(DISTINCT paytmmerchantid) AS refunded_success_merchants,
COUNT(transactionid) AS refunded_success_txns,
SUM(txn_amount) AS refunded_success_gmv,
COUNT(DISTINCT fraud_mid) AS refunded_fraud_merchants,
COUNT(fraud_txnid) AS refunded_fraud_txns,
SUM(fraud_amount) AS refunded_fraud_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
users,
paytmmerchantid,
txn_amount,
fraud_mid,
fraud_txnid,
fraud_amount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'PASS'
AND txn_status = 'SUCCESS'
AND refund_amount > 0
AND DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS d ON a.mt = d.mt
AND a.mid_type = d.mid_type
AND a.isindian = d.isindian
AND a.merchantcategory = d.merchantcategory
AND a.paymethod = d.paymethod
AND a.mcc = d.mcc
AND a.entity_id = d.entity_id
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT cb_mid) AS accepted_cb_merchants,
COUNT(cb_txnid) AS accepted_cb_txns,
SUM(cb_amount) AS accepted_cb_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
cb_mid,
cb_txnid,
cb_amount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'PASS'
AND txn_status = 'SUCCESS'
AND final_cb_status = 'ACCEPTED'
AND (
(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23'
)
OR (
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
)
)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS f ON a.mt = f.mt
AND a.mid_type = f.mid_type
AND a.isindian = f.isindian
AND a.merchantcategory = f.merchantcategory
AND a.paymethod = f.paymethod
AND a.mcc = f.mcc
AND a.entity_id = f.entity_id
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT cb_mid) AS defended_cb_merchants,
COUNT(cb_txnid) AS defended_cb_txns,
SUM(cb_amount) AS defended_cb_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
cb_mid,
cb_txnid,
cb_amount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'PASS'
AND txn_status = 'SUCCESS'
AND final_cb_status = 'DEFENDED'
AND DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS g ON a.mt = g.mt
AND a.mid_type = g.mid_type
AND a.isindian = g.isindian
AND a.merchantcategory = g.merchantcategory
AND a.paymethod = g.paymethod
AND a.mcc = g.mcc
AND a.entity_id = g.entity_id
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT cb_mid) AS open_cb_merchants,
COUNT(cb_txnid) AS open_cb_txns,
SUM(cb_amount) AS open_cb_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
cb_mid,
cb_txnid,
cb_amount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'PASS'
AND txn_status = 'SUCCESS'
AND final_cb_status = 'OPEN'
AND DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS h ON a.mt = h.mt
AND a.mid_type = h.mid_type
AND a.isindian = h.isindian
AND a.merchantcategory = h.merchantcategory
AND a.paymethod = h.paymethod
AND a.mcc = h.mcc
AND a.entity_id = h.entity_id
LEFT JOIN (
SELECT
mt,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
COUNT(DISTINCT cb_mid) AS rec_pend_cb_merchants,
COUNT(cb_txnid) AS rec_pend_cb_txns,
SUM(cb_amount - cb_RecoveredAmount) AS rec_pend_cb_gmv
FROM
(
SELECT
DISTINCT IF(
DATE(dateinserted) BETWEEN DATE'2025-02-01'
AND DATE'2025-02-23',
'LMTD',
IF(
(
DATE(dateinserted) BETWEEN DATE'2025-03-01'
AND DATE'2025-03-23'
),
'MTD',
'NA'
)
) AS mt,
transactionid,
mid_type,
isindian,
merchantcategory,
paymethod,
mcc,
entity_id,
cb_mid,
cb_txnid,
cb_amount,
cb_RecoveredAmount
FROM
team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
WHERE
actionrecommended = 'PASS'
AND txn_status = 'SUCCESS'
AND final_cb_status = 'ACCEPTED'
AND (
cb_RecoveredAmount IS NULL
OR cb_RecoveredAmount < cb_amount
)
AND DATE(dateinserted) >= CAST('2025-02-01' AS DATE)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7
) AS i ON a.mt = i.mt
AND a.mid_type = i.mid_type
AND a.isindian = i.isindian
AND a.merchantcategory = i.merchantcategory
AND a.paymethod = i.paymethod
AND a.mcc = i.mcc
AND a.entity_id = i.entity_id;
