%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)