CREATE TABLE team_kingkong.offus_monthly_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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS mt,
      mid_type,
      isindian,
      merchantcategory,
      paymethod,
      mcc,
      entity_id,
      COUNT(DISTINCT users) AS total_unique_users,
      COUNT(DISTINCT paytmmerchantid) AS total_unique_merchants
    FROM
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      mt <> MONTH(CURRENT_DATE)
    GROUP BY
      1,
      2,
      3,
      4,
      5,
      6,
      7
  ) AS x
  LEFT JOIN (
    SELECT
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      actionrecommended = 'PASS'
      AND txn_status = 'SUCCESS'
      AND mt <> MONTH(CURRENT_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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      actionrecommended = 'PASS'
      AND txn_status = 'CLOSED'
      AND mt <> MONTH(CURRENT_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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      actionrecommended = 'BLOCK'
      AND mt <> MONTH(CURRENT_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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      actionrecommended = 'PASS'
      AND txn_status = 'SUCCESS'
      AND refund_amount > 0
      AND mt <> MONTH(CURRENT_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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      actionrecommended = 'PASS'
      AND txn_status = 'SUCCESS'
      AND final_cb_status = 'ACCEPTED'
      AND mt <> MONTH(CURRENT_DATE)
    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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      actionrecommended = 'PASS'
      AND txn_status = 'SUCCESS'
      AND final_cb_status = 'DEFENDED'
      AND mt <> MONTH(CURRENT_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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec2_corrected
    WHERE
      actionrecommended = 'PASS'
      AND txn_status = 'SUCCESS'
      AND final_cb_status = 'OPEN'
      AND mt <> MONTH(CURRENT_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
      DISTINCT IF(
        mt < 10,
        CONCAT('0', CAST(mt AS VARCHAR)),
        CAST(mt AS VARCHAR)
      ) AS 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
      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 mt <> MONTH(CURRENT_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;