Cacher is the code snippet organizer for pro developers

select utm_source
  , sum(case when complete_to_decision < 1 then 1 else 0 end) as day
	, sum(case when complete_to_decision <= 1 then 1 else 0 end) as next_day
	, sum(case when complete_to_decision < 3 then 1 else 0 end) as three_day
	, sum(case when complete_to_decision < 5 then 1 else 0 end) as five_day
	, sum(case when complete_to_decision < 7 then 1 else 0 end) as one_week
	, sum(case when complete_to_decision < 14 then 1 else 0 end) as two_weeks
	, sum(case when complete_to_decision < 31 then 1 else 0 end) as month
	, count(*)
from (	
	select *
		, datediff('day', created_at, complete_at) as signup_to_complete
		, datediff('day', job_info_at, intent_at) as job_to_intent
		, datediff('day', intent_at, online_presence_at) as intent_to_online_presence
		, datediff('day', online_presence_at, history_at) as online_presence_to_history
		, datediff('day', history_at, complete_at) as history_to_complete 
		, datediff('day', complete_at, decision_at) as complete_to_decision
	from (		
		select actor_id
			, users.created_at
			, users.utm_source
			, roles.name as primary_role
			, places.country
			, places.state
			, month
			, max(job_info) as job_info_at
			, max(intent) as intent_at
			, max(online_presence) as online_presence_at
			, max(history) as history_at
			, max(complete) as complete_at
			, max(decision) as decision_at
			, count(optin_failed_prequal) as optin_failed_prequal
		from (	
			select actor_id
				, date_trunc('month', created_at) as month
				, case when type = 'CandidateAtJobInfo' then created_at else null end as job_info
				, case when type = 'CandidateAtOnboardingIntent' or type = 'CandidateAtIntent' then created_at else null end as intent
				, case when type = 'CandidateAtOnlinePresence' then created_at else null end as online_presence
				, case when type = 'CandidateAtHistory' then created_at else null end as history
				, case when type = 'CandidateCompletedOnboarding' then created_at else null end as complete
				, case when type = 'CandidateApproved' or type = 'CandidateRejected' then created_at else null end as decision
				, case when type = 'CandidateOptedInWithFailedPrequal' then id else null end as optin_failed_prequal
			from activities
			where created_at >= '2018-01-01'	
				and type in ('CandidateApproved','CandidateCompletedOnboarding','CandidateRejected','CandidateAtIntent','CandidateAtHistory','CandidateAtOnlinePresence','CandidateAtJobInfo','CandidateAtOnboardingIntent', 'CandidateOptedInWithFailedPrequal')
		) 
		left join users on users.id = actor_id
		left join places on places.id = users.place_id
		left join candidate_profiles as cp on cp.id = users.profile_id
		left join roles on cp.primary_role_id = roles.primary_role_id
		group by 1,2,3,4,5,6,7
	)
	where complete_at is not null
		and decision_at is not null
		and signup_to_complete <= 31
) group by 1
	order by 2 desc