SELECT
  a.mt,
  a.mid_type,
  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,
      COUNT(DISTINCT users) AS total_unique_users,
      COUNT(DISTINCT paytmmerchantid) AS total_unique_merchants
    FROM
      (
        SELECT
          DISTINCT IF(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          users,
          paytmmerchantid
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS x
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          users,
          paytmmerchantid,
          transactionid,
          txn_amount,
          cb_mid,
          cb_txnid,
          cb_amount,
          fraud_mid,
          fraud_txnid,
          fraud_amount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'PASS'
          AND txn_status = 'SUCCESS'
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS a ON x.mt = a.mt
  AND x.mid_type = a.mid_type
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          users,
          paytmmerchantid,
          transactionid,
          txn_amount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'PASS'
          AND txn_status = 'CLOSED'
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS b ON a.mt = b.mt
  AND a.mid_type = b.mid_type
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          users,
          paytmmerchantid,
          transactionid,
          txn_amount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'BLOCK'
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS c ON a.mt = c.mt
  AND a.mid_type = c.mid_type
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          users,
          paytmmerchantid,
          transactionid,
          txn_amount,
          fraud_mid,
          fraud_txnid,
          fraud_amount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'PASS'
          AND txn_status = 'SUCCESS'
          AND refund_amount > 0
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS d ON a.mt = d.mt
  AND a.mid_type = d.mid_type
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          cb_mid,
          cb_txnid,
          cb_amount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'PASS'
          AND txn_status = 'SUCCESS'
          AND final_cb_status = 'ACCEPTED'
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS f ON a.mt = f.mt
  AND a.mid_type = f.mid_type
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          cb_mid,
          cb_txnid,
          cb_amount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'PASS'
          AND txn_status = 'SUCCESS'
          AND final_cb_status = 'DEFENDED'
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS g ON a.mt = g.mt
  AND a.mid_type = g.mid_type
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          cb_mid,
          cb_txnid,
          cb_amount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'PASS'
          AND txn_status = 'SUCCESS'
          AND final_cb_status = 'OPEN'
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS h ON a.mt = h.mt
  AND a.mid_type = h.mid_type
  LEFT JOIN (
    SELECT
      mt,
      mid_type,
      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(
            dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
            AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1),
            'LMTD',
            IF(
              (
                dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
                AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
              ),
              'MTD',
              'NA'
            )
          ) AS mt,
          mid_type,
          cb_mid,
          cb_txnid,
          cb_amount,
          cb_RecoveredAmount
        FROM
          team_kingkong.rohit_edc_qr_users_wCB_wFraud_Dec
        WHERE
          actionrecommended = 'PASS'
          AND txn_status = 'SUCCESS'
          AND final_cb_status = 'ACCEPTED'
          AND (
            cb_RecoveredAmount IS NULL
            OR cb_RecoveredAmount < cb_amount
          )
          AND (
            (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', ADD_MONTHS(CURRENT_DATE, -1)))
              AND ADD_MONTHS(CURRENT_DATE - INTERVAL '1' DAY, -1)
            )
            OR (
              dateinserted BETWEEN DATE(DATE_TRUNC('MONTH', CURRENT_DATE))
              AND DATE(CURRENT_DATE - INTERVAL '1' DAY)
            )
          )
      )
    GROUP BY
      1,
      2
  ) AS i ON a.mt = i.mt
  AND a.mid_type = i.mid_type