SELECT DISTINCT substr(cast(txn_date_switch as varchar(30)), 1, 7) as txn_year_month
, payer_vpa_switch
, reporting_date
, ingestion_date
, 'Cybercell' AS source
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          DISTINCT transaction_id AS txnid,
          CAST(txn_amount AS DOUBLE) AS txn_amount,
          DATE(reporting_date) AS reporting_date,
          DATE(dl_last_updated) AS ingestion_date,
          ROW_NUMBER() OVER (PARTITION BY transaction_id) AS txn_id_duplicate
        FROM
          frauds.ppsl_cybercell_snapshot_v3
        WHERE
          DATE(dl_last_updated) >= DATE(CURRENT_DATE - INTERVAL '1' DAY)
          AND NOT transaction_id IN ('', ' ', 'NA', 'N.A', '0')
          AND NOT transaction_id IS NULL
      )
    WHERE
      txn_id_duplicate = 1
  ) AS b
  INNER JOIN (
    SELECT
      DISTINCT GET_JSON_OBJECT(
        payresultcontext,
        '$.payMethodResultList[0].extendInfo.payeeVpa'
      ) AS payee_vpa,
      transactionid,
      IF(NOT rrncode IS NULL, rrncode, banktxnid) AS rrn_pg,
      DATE(dl_last_updated) AS dl_last_updated
    FROM
      risk_maquette_data_async.pplus_payment_result_prod_async_snapshot_v3
    WHERE
      DATE(dl_last_updated) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
      AND DATE(gmt_occur_derived) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
      AND payresult IN ('payment_success')
      AND UPPER(paymethod) IN ('UPI')
  ) AS c ON b.txnid = c.transactionid
  AND c.dl_last_updated < b.reporting_date
  INNER JOIN (
    SELECT
      *
    FROM
      (
        SELECT
          DISTINCT b.created_on AS txn_date_switch,
          a.txn_id AS upi_txn_id_switch,
          a.vpa AS payer_vpa_switch,
          b.vpa AS payee_vpa_switch,
          a.amount AS txn_amount_switch,
          a.mcc AS payer_mcc_switch,
          b.mcc AS payee_mcc_switch,
          a.handle AS payer_handle,
          b.handle AS payee_handle,
          a.scope_cust_id,
          a.mobile_no AS payer_mobile_no,
          (
            CASE
              WHEN (
                LOWER(b.vpa) LIKE '%@paytm%'
                OR LOWER(b.vpa) LIKE '%@pt%'
              ) THEN 'paytm merchant/bene'
              ELSE 'others'
            END
          ) AS merchant_bene_type
        FROM
          switch.txn_participants_snapshot_v3 AS a
          INNER JOIN switch.txn_participants_snapshot_v3 AS b ON a.txn_id = b.txn_id
        WHERE
          DATE(a.dl_last_updated) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
          AND b.dl_last_updated >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
          AND a.created_on >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
          AND a.participant_type IN ('PAYER')
          AND b.participant_type IN ('PAYEE')
          AND (
            LOWER(a.vpa) LIKE '%@paytm%'
            OR LOWER(a.vpa) LIKE '%@pt%'
          )
      ) AS x
      INNER JOIN (
        SELECT
          DISTINCT txn_id,
          category,
          rrn
        FROM
          switch.txn_info_snapshot_v3
        WHERE
          DATE(dl_last_updated) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
          AND DATE(created_on) >= DATE(CURRENT_DATE - INTERVAL '90' DAY)
          AND UPPER(status) IN ('SUCCESS')
      ) AS f ON x.upi_txn_id_switch = f.txn_id
  ) AS y ON y.rrn = c.rrn_pg
  AND y.payee_vpa_switch = c.payee_vpa
  AND y.txn_date_switch < b.reporting_date