CASE 16

PHOTO EMBED

Tue Apr 11 2023 06:28:11 GMT+0000 (Coordinated Universal Time)

Saved by @shubhangi_burle 

%jdbc(hive)
set tez.queue.name=phonepe_verified;
set hive.execution.engine=tez;

-- CASE 16
SELECT '2023-05-22' AS run_date_monday, group_name, requester
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 30) THEN ticket_id ELSE NULL END) AS Week1_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 29) AND DATE_SUB('2023-05-22', 23) THEN ticket_id ELSE NULL END) AS Week2_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 22) AND DATE_SUB('2023-05-22', 16) THEN ticket_id ELSE NULL END) AS Week3_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 15) AND DATE_SUB('2023-05-22', 9) THEN ticket_id ELSE NULL END) AS Week4_tickets
, COUNT(DISTINCT CASE WHEN created_time BETWEEN DATE_SUB('2023-05-22', 8) AND DATE_SUB('2023-05-22', 2) THEN ticket_id ELSE NULL END) AS Week5_tickets
FROM
    (SELECT DISTINCT ticket_id, `group` as group_name, created_time
    , CASE WHEN contact_id LIKE '%gmail.com' OR contact_id LIKE '%nearbuy.com' OR contact_id LIKE '%paynimo.com' OR contact_id LIKE '%mmtcpamp.com' OR contact_id LIKE '%safegold.in' OR contact_id LIKE '%irctc.co.in' OR contact_id LIKE '%freecharge.com' OR contact_id LIKE '%paytm.com' OR contact_id LIKE '%flipkart.com' OR contact_id LIKE '%zipcash.in' OR contact_id LIKE '%google.com' OR contact_id LIKE '%outlook.in' OR contact_id LIKE '%veri5digital.com' OR contact_id LIKE '%payswiff.com' OR contact_id LIKE '%olamoney.com' OR contact_id LIKE '%paypointindia.com' OR contact_id LIKE '%linkedin.com' OR contact_id LIKE '%payzapp.in' OR contact_id LIKE '%rediffmail.com' OR contact_id LIKE '%gyftr.com' OR contact_id LIKE '%cybercert.in' OR contact_id LIKE '%bajajfinserv.in' OR contact_id LIKE '%cashfree.com' OR contact_id LIKE '%ril.com' OR contact_id LIKE '%citruspay.com' OR contact_id LIKE '%yahoo.co.in' OR contact_id LIKE '%balancehero.com' OR contact_id LIKE '%ica.in' OR contact_id LIKE '%matchmove.com' OR contact_id LIKE '%ultracash.com' OR contact_id LIKE '%dream11.com' OR contact_id LIKE '%kpmg.com' OR contact_id LIKE '%juspay.in' OR contact_id LIKE '%yuvapay.com' OR contact_id LIKE '%icloud.com' OR contact_id LIKE '%yamoney.ru' OR contact_id LIKE '%transactionanalysts.com' OR contact_id LIKE '%yahoo.com' OR contact_id LIKE '%iris.phonepe.com' OR contact_id LIKE '%ford.com' OR contact_id LIKE '%ppbtp.com' OR contact_id LIKE '%hotmail.com' OR contact_id LIKE '%abhibus.com' OR contact_id LIKE '%partner.flipkart.com' OR contact_id LIKE '%wsretail.in' OR contact_id LIKE '%cleartrip.com' OR contact_id LIKE '%srl.in' OR contact_id LIKE '%airpay.co.in' OR contact_id LIKE '%outlook.com' OR contact_id LIKE '%kalyanjanata.in' OR contact_id LIKE '%olacabs.com' OR contact_id LIKE '%ecomexpress.in' OR contact_id LIKE '%payumoney.com' OR contact_id LIKE '%pay1.in' OR contact_id LIKE '%amazon.com' OR contact_id LIKE '%remitguru.com' OR contact_id LIKE '%twitter.com' OR contact_id LIKE '%spicemoney.com' OR contact_id LIKE '%curefit.com' OR contact_id LIKE '%qwikcilver.com' OR contact_id LIKE '%wika.com' OR contact_id LIKE '%yahoo.in' OR contact_id LIKE '%shopify.zendesk.com' OR contact_id LIKE '%service-now.com' OR contact_id LIKE '%cybersecurityindia.org' OR contact_id LIKE '%zee.esselgroup.com' OR contact_id LIKE '%dell.com' OR contact_id LIKE '%cyberorgindia.com' OR contact_id LIKE '%olx.in' OR contact_id LIKE '%bounces.amazon.com' OR contact_id LIKE '%go-mmt.com' OR contact_id LIKE '%housing.com' OR contact_id LIKE '%gamail.com' OR contact_id LIKE '%aexp.com' OR contact_id LIKE '%chemtrade.in' OR contact_id LIKE '%indoreinstitute.com' OR contact_id LIKE '%live.com' OR contact_id LIKE '%grofers.com' OR contact_id LIKE '%ispirt.in' OR contact_id LIKE '%atomtech.in' OR contact_id LIKE '%woohoo.in' OR contact_id LIKE '%rpsg.in' OR contact_id LIKE '%mobikwik.com' OR contact_id LIKE '%grahakshikayatmanch.in' OR contact_id LIKE '%amazon.in' OR contact_id LIKE '%phonecom.atlassian.net' OR contact_id LIKE '%ballebaazi.com' OR contact_id LIKE '%zomato.com' OR contact_id LIKE '%rocketmail.com' OR contact_id LIKE '%medplusindia.com' OR contact_id LIKE '%jublfood.com' OR contact_id LIKE '%cx.freelancer.com' OR contact_id LIKE '%aetheralloys.com' OR contact_id LIKE '%womenly.in' OR contact_id LIKE '%fspl.co.in' OR contact_id LIKE '%supercell.mail2.helpshift.com' OR contact_id LIKE '%acbl.in' OR contact_id LIKE '%vishweshwarbank.com' OR contact_id LIKE '%zeta.in' OR contact_id LIKE '%ecomcharge.com' OR contact_id LIKE '%bel.co.in' OR contact_id LIKE '%uber.com' OR contact_id LIKE '%hllhites.com' OR contact_id LIKE '%cyberjagrithi.com' OR contact_id LIKE '%1mg.com' OR contact_id LIKE '%ymail.com' OR contact_id LIKE '%zopsmart.com' OR contact_id LIKE '%onsoft.no' OR contact_id LIKE '%mintra.com' OR contact_id LIKE '%aeronfly.com' OR contact_id LIKE '%fisdom.com' OR contact_id LIKE '%npstx.com' OR contact_id LIKE '%dangeedums.com' OR contact_id LIKE '%microlucid.com' OR contact_id LIKE '%gupshup.io' OR contact_id LIKE '%live.in' OR contact_id LIKE '%webstechno.com' OR contact_id LIKE '%tutorialspoint.com' OR contact_id LIKE '%lawinsider.zendesk.com' OR contact_id LIKE '%ixigo.com' OR contact_id LIKE '%mekrafts.com' OR contact_id LIKE '%pra.ufpb.br' OR contact_id LIKE '%compesa.com.br' OR contact_id LIKE '%field-servicetrack.com' OR contact_id LIKE '%gadgets360.com' OR contact_id LIKE '%tssouthernpower.com' OR contact_id LIKE '%heritageresortbikaner.com' OR contact_id LIKE '%isgec.co.in' OR contact_id LIKE '%bgss.in' OR contact_id LIKE '%grahakhelpline.com' OR contact_id LIKE '%nitrkl.ac.in' OR contact_id LIKE '%sionasys.com' OR contact_id LIKE '%osomgame.com' OR contact_id LIKE '%fampay.in' OR contact_id LIKE '%swiggy.in' OR contact_id LIKE '%dpogroup.com' OR contact_id LIKE '%thebakingculture.com' OR contact_id LIKE '%iris.phonpe.com' OR contact_id LIKE '%iris.phonepe.comqq' OR contact_id LIKE '%instamojo.com' OR contact_id LIKE '%worldphone.in' OR contact_id LIKE '%infrasofttech.com' OR contact_id LIKE '%freshworks.com' OR contact_id LIKE '%concentrix.com' OR contact_id LIKE '%safexpay.com' OR contact_id LIKE '%sabpaisa.zohosupport.com' OR contact_id LIKE '%messagebird.com' OR contact_id LIKE '%dailycare-464bb6994436.intercom-mail.com' OR contact_id LIKE '%xn--n2bqs1ai.xn--81bsj3a4b8ad2j.xn' OR contact_id LIKE '%cashe.co.in' OR contact_id LIKE '%in.airtel.com' OR contact_id LIKE '%jagrookupbhoktamanch.com' OR contact_id LIKE '%dpworld.com' OR contact_id LIKE '%nobroker.in' OR contact_id LIKE '%louderband.com' OR contact_id LIKE '%therummyguru.com' OR contact_id LIKE '%gmal.com' OR contact_id LIKE '%zerodha.com' OR contact_id LIKE '%ippbonline.in' OR contact_id LIKE '%dhani.com' OR contact_id LIKE '%assocham.co.in' OR contact_id LIKE '%drsheths.com' OR contact_id LIKE '%sadahaq.org' OR contact_id LIKE '%iitrpr.ac.in' OR contact_id LIKE '%walkover.in' OR contact_id LIKE '%hdworks.in' OR contact_id LIKE '%cs.linkedin.com' OR contact_id LIKE '%nnacell.com' OR contact_id LIKE '%arambaghfoodmart.com' OR contact_id LIKE '%aesl.in' OR contact_id LIKE '%juspay4.zohodesk.com' OR contact_id LIKE '%easebuzz.in' OR contact_id LIKE '%rummydangal.com' OR contact_id LIKE '%kreditbee.in' OR contact_id LIKE '%snapdeal.com' OR contact_id LIKE '%pinnacleimpex.com' OR contact_id LIKE '%bigfungame.zendesk.com' OR contact_id LIKE '%grahakshikayatmanch.com' OR contact_id LIKE '%dotpe.in' OR contact_id LIKE '%vplustech.in' OR contact_id LIKE '%witzeal.com' OR contact_id LIKE '%gtthreers.com' OR contact_id LIKE '%nexgenonline.in' OR contact_id LIKE '%blackbuck.com' OR contact_id LIKE '%paysprint.in' OR contact_id LIKE '%eko.co.in' OR contact_id LIKE '%desertcart.ae' OR contact_id LIKE '%gameskraft.com' OR contact_id LIKE '%rummyculture.com' OR contact_id LIKE '%deliveryplus.co.in' OR contact_id LIKE '%shamrocktech.in' OR contact_id LIKE '%buyhatke.com' OR contact_id LIKE '%mahanagargas.com' OR contact_id LIKE '%shaadi.com' OR contact_id LIKE '%aud.ac.in' OR contact_id LIKE '%memento-genealogie.fr' OR contact_id LIKE '%picodigital.co.in' OR contact_id LIKE '%zaakpay.com' OR contact_id LIKE '%bikano.com' OR contact_id LIKE '%cars24.com' OR contact_id LIKE '%cmrintl.com' OR contact_id LIKE '%openplaytech.zohodesk.com' OR contact_id LIKE '%badgebuddies.com' OR contact_id LIKE '%earlysalary.com' OR contact_id LIKE '%avenues.info' OR contact_id LIKE '%howzat.com' OR contact_id LIKE '%flobiz.in' OR contact_id LIKE '%lightoo.in' OR contact_id LIKE '%titan.co.in' OR contact_id LIKE '%records.facebook.com' OR contact_id LIKE '%xiaomi.com' OR contact_id LIKE '%excitel.com' OR contact_id LIKE '%cards.pe' OR contact_id LIKE '%a23.com' OR contact_id LIKE '%akinfopark.com' OR contact_id LIKE '%vouchagram.com' OR contact_id LIKE '%abpnetwork.com' OR contact_id LIKE '%gobg.in' OR contact_id LIKE '%tvscredit.com' OR contact_id LIKE '%infoedge.com' OR contact_id LIKE '%theporter.in' OR contact_id LIKE '%practo.com' OR contact_id LIKE '%dishd2h.com' OR contact_id LIKE '%kiya.ai' OR contact_id LIKE '%khelplayrummy.com' OR contact_id LIKE '%rvce.edu.in' OR contact_id LIKE '%zupee.in' OR contact_id LIKE '%magicpin.in' OR contact_id LIKE '%ongrid.in' OR contact_id LIKE '%d2h.com' OR contact_id LIKE '%tataplay.com' OR contact_id LIKE '%eroute.in' OR contact_id LIKE '%quickheal.co.in' OR contact_id LIKE '%slicepay.zendesk.com' THEN 'CUSTOMER'
    
    WHEN contact_id LIKE '%billdesk.in' OR contact_id LIKE '%billdesk.com' OR contact_id LIKE '%sbi.co.in' OR contact_id LIKE '%rblbank.com' OR contact_id LIKE '%hdfcbank.com' OR contact_id LIKE '%payu.in' OR contact_id LIKE '%yesbank.in' OR contact_id LIKE '%sbicard.com' OR contact_id LIKE '%pnb.co.in' OR contact_id LIKE '%bankofbaroda.com' OR contact_id LIKE '%axisbank.com' OR contact_id LIKE '%ingenico.com' OR contact_id LIKE '%kotak.com' OR contact_id LIKE '%idfcfirstbank.com' OR contact_id LIKE '%kvbmail.com' OR contact_id LIKE '%razorpay.com' OR contact_id LIKE '%bandhanbank.com' OR contact_id LIKE '%indusind.com' OR contact_id LIKE '%worldline.com' OR contact_id LIKE '%federalbank.co.in' OR contact_id LIKE '%dcbbank.com' OR contact_id LIKE '%unitedbank.co.in' OR contact_id LIKE '%ktkbank.com' OR contact_id LIKE '%citi.com' OR contact_id LIKE '%iob.in' OR contact_id LIKE '%obc.co.in' OR contact_id LIKE '%tbsbl.com' OR contact_id LIKE '%bobfinancial.com' OR contact_id LIKE '%airtelbank.com' OR contact_id LIKE '%unionbankofindia.com' OR contact_id LIKE '%cityunionbank.in' OR contact_id LIKE '%aubank.in' OR contact_id LIKE '%jkbmail.com' OR contact_id LIKE '%jkgb.in' OR contact_id LIKE '%jiopaymentsbank.com' OR contact_id LIKE '%denabank.co.in' OR contact_id LIKE '%ucobank.co.in' OR contact_id LIKE '%iobnet.co.in' OR contact_id LIKE '%indianbank.co.in' OR contact_id LIKE '%abhyudayabank.net' OR contact_id LIKE '%pinelabs.com' OR contact_id LIKE '%tmbank.in' OR contact_id LIKE '%bankofindia.co.in' OR contact_id LIKE '%equitasbank.com' OR contact_id LIKE '%paytmbank.com' OR contact_id LIKE '%canarabank.com' OR contact_id LIKE '%psb.co.in' OR contact_id LIKE '%finobank.com' OR contact_id LIKE '%saraswatbank.com' OR contact_id LIKE '%varachhabank.in' OR contact_id LIKE '%syndicatebank.co.in' OR contact_id LIKE '%vijaycooperativebank.com' OR contact_id LIKE '%ccavenue.com' OR contact_id LIKE '%svcbank.com' OR contact_id LIKE '%icicibank.com' OR contact_id LIKE '%adcbank.coop' OR contact_id LIKE '%greaterbank.com' OR contact_id LIKE '%hastibank.org' OR contact_id LIKE '%shgbank.co.in' OR contact_id LIKE '%lvbank.in' OR contact_id LIKE '%sudicobank.com' OR contact_id LIKE '%hsbc.co.in' OR contact_id LIKE '%sgbrrb.org' OR contact_id LIKE '%allahabadbank.in' OR contact_id LIKE '%bmcbank.co.in' OR contact_id LIKE '%ratnagiridccb.co.in' OR contact_id LIKE '%janasevabank.in' OR contact_id LIKE '%sreenidhibank.com' OR contact_id LIKE '%sc.com' OR contact_id LIKE '%karnatakaapex.com' OR contact_id LIKE '%firstdata.com' OR contact_id LIKE '%shivalikbank.com' OR contact_id LIKE '%marathacooperativebank.com' OR contact_id LIKE '%maladbank.com' OR contact_id LIKE '%tjsb.co.in' OR contact_id LIKE '%barodagujaratrrb.co.in' OR contact_id LIKE '%consumerchanakya.com' OR contact_id LIKE '%centralbank.co.in' OR contact_id LIKE '%siyaram.com' OR contact_id LIKE '%bharatpay.co.in' OR contact_id LIKE '%sib.co.in' OR contact_id LIKE '%nainitalbank.co.in' OR contact_id LIKE '%dbgb.co.in' OR contact_id LIKE '%kalupurbank.com' OR contact_id LIKE '%agvb.co.in' OR contact_id LIKE '%apgvbank.in' OR contact_id LIKE '%dspcoopbank.com' OR contact_id LIKE '%dnsb.co.in' OR contact_id LIKE '%dhanbank.co.in' OR contact_id LIKE '%aeronpay.in' OR contact_id LIKE '%idbi.co.in' OR contact_id LIKE '%csb.co.in' OR contact_id LIKE '%mehsananagrikbank.com' OR contact_id LIKE '%easemytrip.com' OR contact_id LIKE '%sbipayments.com' OR contact_id LIKE '%mail.pnb.co.in' OR contact_id LIKE '%euronetworldwide.com' OR contact_id LIKE '%amazonpay.in' OR contact_id LIKE '%axisdirect.in' OR contact_id LIKE '%icicims.com' OR contact_id LIKE '%codapayments.com' OR contact_id LIKE '%barodarajasthanrrb.co.in' OR contact_id LIKE '%licindia.com' OR contact_id LIKE '%pbgbank.co.in' OR contact_id LIKE '%bharatpe.com' OR contact_id LIKE '%novopay.in' OR contact_id LIKE '%jaihindbank.com' OR contact_id LIKE '%mahabank.co.in' OR contact_id LIKE '%nkgsb-bank.com' OR contact_id LIKE '%barodauprrb.co.in' OR contact_id LIKE '%cgbank.in' OR contact_id LIKE '%janasevabankpune.in' OR contact_id LIKE '%in.airtel.com' OR contact_id LIKE '%bharatbank.co.in' OR contact_id LIKE '%idfcbank.com' OR contact_id LIKE '%nsdlbank.co.in' OR contact_id LIKE '%pardipeoplesbank.in' OR contact_id LIKE '%fincarebank.com' OR contact_id LIKE '%ext.icicibank.com' OR contact_id LIKE '%gpparsikbank.net' OR contact_id LIKE '%khamgaonbank.in' OR contact_id LIKE '%sbmbank.co.in' OR contact_id LIKE '%kanakamahalakshmibank.com' OR contact_id LIKE '%unionbankofindia.bank' OR contact_id LIKE '%ippopay.com' OR contact_id LIKE '%jiomart.com' OR contact_id LIKE '%dbs.com' OR contact_id LIKE '%getonecard.app' OR contact_id LIKE '%nesl.co.in' OR contact_id LIKE '%decentro.tech' OR contact_id LIKE '%tssconsultancy.com' THEN 'BANK'
    
    WHEN contact_id LIKE '%ksp.gov.in' OR contact_id LIKE '%uttarakhandpolice.uk.gov.in' OR contact_id LIKE '%nic.in' OR contact_id LIKE '%tspolice.gov.in' OR contact_id LIKE '%policewb.gov.in' OR contact_id LIKE '%mp.gov.in' OR contact_id LIKE '%mppolice.gov.in' OR contact_id LIKE '%mahapolice.gov.in' OR contact_id LIKE '%gov.in' OR contact_id LIKE '%kolkatapolice.gov.in' OR contact_id LIKE '%kerala.gov.in' OR contact_id LIKE '%rajpolice.gov.in' OR contact_id LIKE '%gujarat.gov.in' OR contact_id LIKE '%delhipolice.gov.in' OR contact_id LIKE '%telangana.gov.in' OR contact_id LIKE '%meity.gov.in' OR contact_id LIKE '%punjabpolice.gov.in' OR contact_id LIKE '%jhpolice.gov.in' OR contact_id LIKE '%vza.appolice.gov.in' OR contact_id LIKE '%odishapolice.gov.in' OR contact_id LIKE '%jharkhandmail.gov.in' OR contact_id LIKE '%ap.gov.in' OR contact_id LIKE '%punjab.gov.in' OR contact_id LIKE '%hry.gov.in' OR contact_id LIKE '%vspc.appolice.gov.in' OR contact_id LIKE '%tptu.appolice.gov.in' OR contact_id LIKE '%jkpolice.gov.in' OR contact_id LIKE '%npci.org.in' OR contact_id LIKE '%kri.appolice.gov.in' OR contact_id LIKE '%cbi.gov.in' OR contact_id LIKE '%kdp.appolice.gov.in' OR contact_id LIKE '%pkm.appolice.gov.in' OR contact_id LIKE '%hry.nic.in' OR contact_id LIKE '%pa.railnet.gov.in' OR contact_id LIKE '%cybercelljaipur.in' OR contact_id LIKE '%police.rajasthan.in' OR contact_id LIKE '%upstf.com' OR contact_id LIKE '%up.gov.in' OR contact_id LIKE '%atp.appolice.gov.in' OR contact_id LIKE '%ctr.appolice.gov.in' OR contact_id LIKE '%dli.railnet.gov.in' OR contact_id LIKE '%rjyu.appolice.gov.in' OR contact_id LIKE '%jhs.railnet.gov.in' OR contact_id LIKE '%eg.appolice.gov.in' OR contact_id LIKE '%rajasthan.in' OR contact_id LIKE '%appolice.gov.in' OR contact_id LIKE '%fiserv.com' OR contact_id LIKE '%hp.gov.in' OR contact_id LIKE '%assampolice.gov.in' OR contact_id LIKE '%mb.railnet.gov.in' OR contact_id LIKE '%svpnpa.gov.in' OR contact_id LIKE '%leos.gov.in' OR contact_id LIKE '%rbi.org.in' OR contact_id LIKE '%wg.appolice.gov.in' OR contact_id LIKE '%ka.gov.in' OR contact_id LIKE '%and.nic.in' OR contact_id LIKE '%goapolice.gov.in' OR contact_id LIKE '%tripurapolice.nic.in' OR contact_id LIKE '%vzm.appolice.gov.in' OR contact_id LIKE '%skl.appolice.gov.in' OR contact_id LIKE '%lko.railnet.gov.in' OR contact_id LIKE '%tncctns.gov.in' OR contact_id LIKE '%gtru.appolice.gov.in' OR contact_id LIKE '%nr.railnet.gov.in' OR contact_id LIKE '%apint.gov.in' OR contact_id LIKE '%nlr.appolice.gov.in' OR contact_id LIKE '%sebi.gov.in' OR contact_id LIKE '%cg.gov.in' OR contact_id LIKE '%ias.nic.in' OR contact_id LIKE '%vspr.appolice.gov.in' OR contact_id LIKE '%arunpol.nic.in' OR contact_id LIKE '%scr.railnet.gov.in' OR contact_id LIKE '%cybercrime.appolice.gov.in' OR contact_id LIKE '%cbn.nic.in' OR contact_id LIKE '%lmg.railnet.gov.in' OR contact_id LIKE '%gov.in.local' OR contact_id LIKE '%bb.railnet.gov.in' OR contact_id LIKE '%police.ladakh.gov.in' OR contact_id LIKE '%sbc.railnet.gov.in' OR contact_id LIKE '%bcssarl.com' OR contact_id LIKE '%rentokil-pci.com' OR contact_id LIKE '%mha.gov.in' OR contact_id LIKE '%odisha.gov.in' OR contact_id LIKE '%nashikpolice.com' OR contact_id LIKE '%bangla.gov.in' OR contact_id LIKE '%tn.gov.in' OR contact_id LIKE '%ecor.railnet.gov.in' OR contact_id LIKE '%keralgov.in' OR contact_id LIKE '%mail.ksp.gov.in' OR contact_id LIKE '%nadia.gov.in' OR contact_id LIKE '%cyber-pol.in' OR contact_id LIKE '%kgp.railnet.gov.in' OR contact_id LIKE '%fiuindia.gov.in' OR contact_id LIKE '%ips.gov.in' OR contact_id LIKE '%nfr.railnet.gov.in' OR contact_id LIKE '%umb.railnet.gov.in' OR contact_id LIKE '%astra.xlri.ac.in' OR contact_id LIKE '%kur.railnet.gov.in' OR contact_id LIKE '%leavemanagement.in' OR contact_id LIKE '%cidwestbengal.gov.in' OR contact_id LIKE '%gtrr.appolice.gov.in' OR contact_id LIKE '%fzr.railnet.gov.in' OR contact_id LIKE '%mah.gov.in' OR contact_id LIKE '%sikkimpolice.nic.in' OR contact_id LIKE '%nodal.appolice.gov.in' OR contact_id LIKE '%jbp.railnet.gov.in' OR contact_id LIKE '%adi.railnet.gov.in' OR contact_id LIKE '%rtm.railnet.gov.in' OR contact_id LIKE '%nagpurpolice.gov.in' OR contact_id LIKE '%bct.railnet.gov.in' OR contact_id LIKE '%wat.railnet.gov.in' OR contact_id LIKE '%izn.railnet.gov.in' OR contact_id LIKE '%ecr.railnet.gov.in' OR contact_id LIKE '%ljn.railnet.gov.in' OR contact_id LIKE '%sr.railnet.gov.in' OR contact_id LIKE '%ncr.railnet.gov.in' OR contact_id LIKE '%wr.railnet.gov.in' OR contact_id LIKE '%hub.nic.in' OR contact_id LIKE '%hwh.railnet.gov.in' OR contact_id LIKE '%kkd.appolice.gov.in' OR contact_id LIKE '%nashikcitypolice.gov.in' OR contact_id LIKE '%ser.railnet.gov.in' OR contact_id LIKE '%meghalaya.gov.in' OR contact_id LIKE '%paschimbardhaman.gov.in' OR contact_id LIKE '%ndl.appolice.gov.in' THEN 'LEA/NPCI/RBI'
    
    WHEN contact_id LIKE '%phonepe.com' OR contact_id LIKE '%phonepecs.com' OR contact_id LIKE '%freshdesk.com' OR contact_id LIKE '%phonepe.in' OR contact_id LIKE '%phonepeadmin.com' OR contact_id LIKE '%iris.phoneepe.com' OR contact_id LIKE '%iris.phonepe.co' OR contact_id LIKE '%phonepeassist.freshdesk.com' OR contact_id LIKE '%iris.phonepe.comc' OR contact_id LIKE '%iris.pho' OR contact_id LIKE '%phonepebdesecurity.freshdesk.com' OR contact_id LIKE '%iris.phonepe.comb' OR contact_id LIKE '%iris.phonepe.comw' OR contact_id LIKE '%iris.phonepe.come.com' OR contact_id LIKE '%iris.phon' OR contact_id LIKE '%iris.phonep.com' OR contact_id LIKE '%iris.phonepe' OR contact_id LIKE '%iris.ph' OR contact_id LIKE '%paymatrixhelp.freshdesk.com' THEN 'INTERNAL'
    
    WHEN contact_id LIKE '%pyrelle.com' THEN 'JUNK EMAIL ID'
    END AS requester
    FROM freshdesk.properties
    WHERE year IN (year(DATE_SUB('2023-05-22', 36)) , year(DATE_SUB('2023-05-22', 2)))
    AND month >= month(DATE_SUB('2023-05-22', 36)) AND month <= month(DATE_SUB('2023-05-22', 2))
    AND Date(created_time) BETWEEN DATE_SUB('2023-05-22', 36) AND DATE_SUB('2023-05-22', 2)
    AND `group` IN ('Risk Team', 'security', 'Cybercell', 'FRA-Escalations', 'Blocked Fraud', 'Lien Email', 'NPCI Escalations'))X
GROUP BY group_name, requester
content_copyCOPY