vertical emails

with summary as (
	select
	    identity_email,
	    identity_name,
	    company_industry,
	    company_domain,
	    company_name,
	    known_by_user_count,
	    identity_created_at,
	    identity_imported_from,
	   
	    
			-- OTHER FEATURES that might be interesting
			split_part(identity_name,' ',1) as first_name, 
	    identity_name,
	    identity_employment_title,
	    identity_twitter_followers,
	    
	    company_name,
	    company_description,
	    company_sector,
	    company_industry,
	    company_subindustry,
	    company_twitter_followers,
	    company_facebook_likes,
	    company_employees,
	    identity_employment_title,
	    identity_id,
	    user_id,
	    
	--    
	    last_email_sent_at,
	    -- limit to 10 people per domain
	    row_number() over (partition by company_name) as number_of_people_from_domain
	--    
	    
	from analytics.identity_info_and_state i
	where 
	    company_industry is not null
	    -- Not subscribed
	    and has_subscribed is null
	    and has_unsubscribed_from_emails is False
	  	and email_address_valid is True
	  	-- Typical filter is >= 2, can increase this to find 'higher' quality identities
	    and known_by_user_count >= 5 --between 5 and 10
	    
	    -- This attempts to ensure we've seen an email come from this identity. I don't consider this to be a great filter
	    and last_email_sent_by_identity_to_another_user_at >= getdate() - '3 years'::interval
	    
	    -- Input desired industry here - examples => ('Professional Services','Diversified Financial Services', 'Capital Markets'
	    and (i.company_industry ilike '%Technology%' or  i.company_industry ilike '%Internet%')
	    -- OPTIONAL filter for subindustry Example => 'consulting'
	--    and i.company_subindustry ilike '%consulting%'
	    
	    -- Haven't sent them an email in the past 30 days
	    and i.last_email_sent_at <= getdate() - '30 days'::interval
	    -- remove test accounts
	    and split_part(identity_name,' ',1) not ilike '%test%'
	    and company_domain not ilike '%org%'
	    and company_domain not ilike '%edu%'
	    and company_description not ilike '%venture%'
	    and company_description not ilike '%capital%'
	    --limit 400
)
select
*
from summary
where number_of_people_from_domain <= 10