%jdbc(hive) set tez.queue.name=phonepe_verified; set hive.execution.engine=tez; SELECT DISTINCT DATE_SUB('2023-07-02',32) starting_date ,DATE_SUB('2023-07-02',2) ending_date ,nvl(c.active_days,0) active_days ,'weekly' date_range ,c.identifier ,'Merchant_high_nonqr' as red_flag ,c.int_amt as value ,'NA' comment ,case when (d.identifier is null AND e.identifier IS NULL) then 'New' else 'Repeat' end as new_or_repeat ,'AML' `group` ,'FRA' `type` ,'Alerts' type_fra ,'User' issue_type ,'UPI' sub_issue_type ,'2023-07-02' as run_date from (select a.receiveruser as identifier ,a.active_days active_days ,a.int_amt ,a.non_qr_pct from (select receiveruser ,count(distinct senderuserid) int_uid ,sum(totaltransactionamount) int_amt ,count(distinct updated_date) active_days ,SUM(case when initiation_mode <> 2 then 1 else 0 end)/count(distinct transaction_id) as non_qr_pct ,count ( distinct sender_state) as sender_state from fraud.transaction_details_v3 where updated_date BETWEEN DATE_SUB('2023-07-02',32) and DATE_SUB('2023-07-02',2) and pay_transaction_status = 'COMPLETED' and transfermode = 'PEER_TO_MERCHANT' and receiversubtype IN ('P2P_MERCHANT','P2M_LIMITED') group by receiveruser having int_amt > 1000000 and sender_state > 10 and int_uid > 100)a LEFT JOIN (select max(updated) tm, merchant_id from merchant.merchants where blacklisted = 1 and blacklist_reason = 'SAM' group by merchant_id)b on a.receiveruser = b.merchant_id where (case when a.receiveruser = b.merchant_id then 1 else 0 end) = 0 -- GROUP BY a.receiveruser, a.active_days active_days, a.int_amt, a.non_qr_pct order by non_qr_pct desc limit 8)c left join (select identifier from fraud.aml_freshdesk)d on c.identifier = d.identifier left join (select identifier from fraud.aml_freshdesk_month)e on c.identifier = e.identifier
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter