select lower(hdc.email) as user_email
, coalesce(hdc.designation, de.designation) as user_designation
, case 
when lower(user_designation) like '%data_engineer%' then 'Data Engineer'
when lower(user_designation) like '%business_analyst%'
		 or lower(user_designation) like '%data_analyst%' 
		 or lower(user_designation) like '%analytics_engineer%' 
	 then 'Data Analyst'
when lower(user_designation) like '%data_leader%' or lower(user_designation) like '%data_leader(cto)%' then 'Founder/CXO'
when lower(user_designation) like '%digital_marketer%' then 'Sales and Marketing'
-- when lower(user_designation) like '%data_scientist%' then 'Data Scientist'
when lower(user_designation) like '%programmer%' then 'Engineer/Tech'

-- Manual


when lower(user_designation) like '%data_%' then 'Data Engineer'
when lower(user_designation) like '%tec%' or lower(user_designation) like '%software%' or lower(user_designation) like '%engineer%' 
    or lower(user_designation) like '%data%' or lower(user_designation) like '%developer%' or user_designation like '%IT%' 
    or lower(user_designation) like '%devops%' or lower(user_designation) like '%solution%'or lower(user_designation) like '%system%'
    then 'Engineer/Tech'
when lower(user_designation) like '%analy%' or lower(user_designation) like '%business%' or user_designation like '%BI%' 
  or lower(user_designation) like '%intelligence%'
	then 'Data Analyst'
when lower(user_designation) like '%found%' or lower(user_designation) like '%owner%' or lower(user_designation) like '%chief%'
	or lower(user_designation) like '%partner%'
    or user_designation like '%CEO%' or user_designation like '%CTO%' or user_designation like '%CFO%' or user_designation like '%COO%' or user_designation like '%CXO%' 
    or user_designation like '%VP%' or lower(user_designation) like '%president%'  then 'Founder/CXO'
when lower(user_designation) like '%market%' or lower(user_designation) like '%sale%' 
	or lower(user_designation) like '%strategist%' 	or lower(user_designation) like '%digital%' 
	or lower(user_designation) like '%social%' or lower(user_designation) like '%media%'  
	then 'Sales and Marketing'
when lower(user_designation) like 'na' or lower(user_designation) like '-' then 'NA'
when lower(user_designation) is not null then 'Other'
else 'NA'
end as persona
from hubspot_direct_contacts hdc 
left join data_enrichmnet___upwork_rizwan de on lower(de.email) = lower(hdc.email)
where lower(user_email) in 
(
'sanjana.11744@stu.pearlacademy.com','support@sravie.in','nitin17100@alumni.iiitnr.ac.in','papoulakisl@tracking-platform.com','javiermenendez@easternmedicalmso.com','businesshead@evolock.co.in','nate.pham@littlespoon.com','syorgason@freeus.com','data@plumhq.com','linus.etube@sedelcapital.com','jluther@cerity.com','sfprodadmin@simplilearn.net','alex@inspiredhomeco.com','hevodata@vivaldi.net','t.nghaywi@primeware-me.com','jos@fanruan.com','juan.cortes@andesmotor.cl','nhvora@karishye.in','tsj@jyskit.dk','gabi@donut.app','prachiti@imaginarium.io','amin@naasdigital.com','harvey@parlor.io','david.barrera@planetariomedellin.org','timquinn@timquinn.com','shawn@vtldesign.com','yossi.watanabe@pathccm.com','ceo4.0@grouporion.co','NAJ@PHC2DAMMAM.onmicrosoft.com','arnoldangeles@peninsula.com','sigve.albretsen@diforma.no','anand.ns@india.com','ambrish.dwivedi@crs.org','chhoem.sothy@gmail.com','vamshi@minoanexperience.com','andre@twoweb.digital','michael@sidelines.io','aman@stepsetgo.com','david.pardo@medwing.com','nephtaliflores@mtech-systems.com','administrator@prompthr.in','aaron@plusevholdings.com','destek@ferhatburakmaden.com','ted@firestar.dev','krishna@itechsol.net','a.rodriguez@gesforma.com','dalton@lojasbeep.com.br','gbenga.a@autochek.africa','teun@tvgpm.com','slister@skillsurvey.com','danylo@vakhnenko.de','vakhnenko@absolit.de','dlai@sunwing.ca','paul.apaza@metricaandina.com','abbasuddin.it2019@kgec.ac.in','amy.pardue@goodbuygear.com','aditya@moeving.com','frank@roux.mx','sundaram@adobe.com','trilok.negi@motherson.com','philip@fullviewsolutions.co.za','aleksey.svistun@doublegood.com','loralie.hall@allysonlindt.co','dhassard@electricsymphonymedia.com','wchou@phase2technology.com','raj.vysetty@sesamecare.com','rolf.dalhaug@sands.org.uk','nataliap@agibusinessgroup.com','rohins@meyer.com','prajashekar@stratapps.com','mbalslow@basico.dk','team@hemptoday.com','aline@nw2.co','juan.hevia@memorable.io','saidul.alam@mirzafarms.com','kevyn.tuleu@layers.education','will@redefinedgrowthmarketing.com','rajat@gadept.com','chau.pham@platform.inc','yossy@upnfm.com','axel@genia.media','rajat@flyenbot.com','noven@liberant.com.au','michael@revool.it','bilgi@bitaki.com','picqueth@coventry.ac.uk','bastien.didi@gl-events.com','remy@fairplaycasino.com','marketing@enbill.in','bryce@ashbyhq.com','hola@beband.es','hola@littlejungle.es','hola@petitbeau.es','imed@361seo.fr','itprimary@obninc.com','zoe@betterfoodfoundation.org','alvarosalazar@hopeagenciacreativa.com','apoorv.dangi@vertisystem.in','till.aufderheide@taod.de','sumanta.mishra@magicedtech.com','felipe.coxa@pagaleve.com.br','phillip.wegrzyn@htstores.com','sayid-xasan@somnow.org','mgrecco@heavyrain.tech','simon@unstyled.com','flavian.mosquera@blueshift.com.br','joe.oh@blipshift.com','tony@honkmobile.com','mouctar001@gmsg.com','loiol@lorino.com','natalie@revopsautomated.com','xhita@shalion.com','daniel.podgaichenko@mindgeek.com','alisson.gerardi@octadesk.com','jesse.klotz@krauss-gmbh.com','Sunil.kavi@venetoulis.org','b.vasilevski@axeltra.com','biteam_social@citigo.com.vn','fidasah442@bunlets.com','manjula.chikkanna@genproresearch.com','jouni70@talktalk.net','didaggelos@soft-tech.gr','dfuentes@stratechcorp.com','manhlt@beedu.vn','dilip@hiverhq.com','preethi.singh@preethi.com','ceo@awgmedia.cz','support@rranktec.com'
)