ONUS team_kingkong.rejection_olap_v2

select mnth, dt,
  paytmmerchantid,
  paymethod,
  strategy_name,
  count(distinct userid) as users,
  count(distinct transactionid) as txns,
  sum(amt) as rej_gmv,
  count(distinct case
      when rn = 1 then transactionid
      else NULL end) as uniq_txns,
  sum(caseywhen rn = 1 then amt
      else 0 end) as uniq_gmv
from
  (select
      a.*,
      row_number() over(
        partition by mnth,
        dt,
        userid,
       paytmmerchantid,
        paymethod,
        strategy_name
        order by
          amt desc
      ) as rn
    from
      (
        select
          distinct userid,
          transactionid,
          json_extract_scalar(actionrecommendedrules,'$.actionRecommendedRules[0]') as strategy_name,
          cast(eventAmount as double) / 100 as amt,
          date(substr(cast(dateinserted as varchar(30)), 1, 10)) as dt,
          substr(cast(dateinserted as varchar(30)), 1, 7) as mnth,
          paymethod,
          eventName,
          addAndPay,
          paytmmerchantid
        FROM
          cdp_risk_transform.maquette_flattened_onus_snapshot_v3
        WHERE
          dl_last_updated >= date'2024-01-01'
 --date_format(current_date(), 'yyyy-MM-01')

          AND actionrecommended = 'BLOCK'
          AND SOURCE = 'PG'
       
      ) a
      left join (
        select
          *
        from
          team_kingkong.voc_mid_categorization
        where
          mid != ''
      ) m1 on a.paytmmerchantid = m1.mid
  )
group by 1, 2, 3, 4, 5
order by 1, 2, 3, 4, 5