Merchant_high_nonqr to MONTHLY

%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