mastertable

%jdbc(hive)
set tez.queue.name = default;
set hive.execution.engine=tez;
set hive.fetch.task.conversion=none;
SET hive.tez.container.size=24576;
SET hive.tez.java.opts=-Xmx20480m;
set hive.exec.orc.skip.corrupt.data=true;




select
  td.receiveruser merchant, month(td.updated_date) as month, to_date(mom.onboarding_at) as onboarding_date

--overall txn  
coalesce(max(case when td.errorcode = 'SUCCESS' then td.totaltransactionamount else 0 end),0) as max_current_amount,
coalesce(max(case when  td.errorcode = 'SUCCESS' then td.totaltransactionamount else 0 end),0) as max_previous_amount,
coalesce(count(case when td.errorcode = 'SUCCESS' then td.transaction_id else 0 end),0) as previous_txn_cnt,
coalesce(count(case when td.errorcode = 'SUCCESS' then td.transaction_id else 0 end),0) as current_txn_cnt,
coalesce(sum(case when td.errorcode = 'SUCCESS' then td.totaltransactionamount else 0 end),0) as previous_txn_amount,
coalesce(sum(case when td.errorcode = 'SUCCESS' then td.totaltransactionamount else 0 end),0) as current_txn_amount,
coalesce(sum(case when td.errorcode = 'SUCCESS' then md.fraud_amt_rs else 0 end),0) as fraud_amount,
coalesce(count(case when td.errorcode = 'SUCCESS' then md.transaction_id else 0 end),0) as fraud_txn_cnt,
coalesce(count(case when td.error_code = 'SUCCESS' then omp.store_id else 0 end),0) as store_cnt,
coalesce(count(case when tps.type = 'WALLET' then td.transaction_id end),0) ppe_wal_success_txn,
coalesce(sum(case when tps.type = 'WALLET' then td.totaltransactionamount end),0) as ppe_wal_success_amt,
coalesce(count(case when tps.type = 'EGV'  then td.transaction_id end),0) EGV_success_txn,
coalesce(sum(case when tps.type = 'EGV'  then td.totaltransactionamount end),0) as EGV_success_amt,
coalesce(sum(case when td.workflow in ('MERCHANT_REVERSAL_V2') and td.origination_mode in ('B2B_PG') td.error_code = 'SUCCESS' then td.totaltransactionamount end),0) as reversal_amount,
coalesce(count(case when td.workflow in ('MERCHANT_REVERSAL_V2') and td.origination_mode in ('B2B_PG') td.error_code = 'SUCCESS' then td.transaction_id end),0) as reversal_txn,
coalesce(sum(case when  td.origination_mode in ('B2B_PG') td.error_code = 'SUCCESS' then td.totaltransactionamount end),0) as B2B_amount,
coalesce(sum(case when  td.origination_mode in ('B2B_PG') td.error_code = 'SUCCESS' then td.transaction_id end),0) as B2B_cnt


 from
  (
  select
      receiveruser,updated_date, transaction_id, totaltransactionamount,workflowtype, error_code, receiversubtype
  from fraud.transaction_details_v3
  where 1=1
    and updated_date between '2023-05-14' and "2023-05-30" 
    and workflowtype in ('CONSUMER_TO_MERCHANT', 'CONSUMER_TO_MERCHANT_V2', 'EXTERNAL_TO_MERCHANT','EXTERNAL_TO_MERCHANT_V2') and (receiversubtype In ('OFFLINE_UNORGANISED','P2P_MERCHANT','P2M_LIMITED') or origination_mode in ('B2B_PG')) 
    and pay_transaction_status = 'COMPLETED' and errorcode = 'SUCCESS'
  )td
  join(
  select
    transaction_id,
    is_saved_card,
    type,
    shard_id,
    year,
    month
  from payment.transaction_payer_sources
  where 1=1
  (  CONCAT(YEAR, SUBSTRING(Concat(0,MONTH),-2)) >= DATE_FORMAT(DATE_ADD('2023-02-01',0),'yyyyMM')
        and 
    CONCAT(YEAR, SUBSTRING(Concat(0,MONTH),-2)) <= DATE_FORMAT(DATE_ADD(date_sub('2023-04-30',1),0),'yyyyMM'))
    and to_date(created) > '2023-05-14'
    )tps 
    on (t.transaction_id = tps.transaction_id and t.month = tps.month and t.year = tps.year)
    join 
    (SELECT receiveruserid,  transaction_id,fraud_amt_rs
        from fraud.mark_transaction_details_v2
         where 1=1
        where date_tx between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2) and mark_date between DATE_SUB('2023-05-02',32) and DATE_SUB('2023-05-02',2)
        GROUP BY receiveruserid)md
        On tps.transaction_id = md.transaction_id
        join 
  (
  select merchant_id, date(onboarded_at) as onboarding_date
        from merchant_onboarding.merchants
        group by merchant_id, onboarded_at)mom
        on td.receiveruser = mom.merchant_id
        
 group by month(updated_date), td.receiveruser,to_date(mom.onboarding_at)