behavior.sql

\timing
WITH
crs as (
		select
				cr.id as cr_id,
				cr.numerator_id as cr_nid,
				cr.denominator_id as cr_did,
				tct.team_id as team_id,
				tct.call_type_id as ct_id,
				u.id as u_id
		from compound_ratios cr
		inner join call_types_compound_ratios ctcr on ctcr.compound_ratio_id = cr.id
		inner join team_call_types tct on ctcr.call_type_id = tct.call_type_id
		inner join users u on u.team_id = tct.team_id
		where 1=1
		AND tct.team_id in (22) 		-- desired team ids
		--  AND ctcr.call_type_id in (115)  -- desired call type ids
),
calls as (
		select calls.id as call_id, calls.user_id as user_id from calls
		inner join call_types on call_types.id = calls.call_type_id and call_types.id in (select ct_id from crs)
		inner join users on users.id = calls.user_id and users.team_id in (select team_id from crs)
		WHERE calls.occurred_at >= date_trunc('month', now())
)
SELECT
		(
				select count(1) from call_compounds cc
						inner join calls on calls.call_id = cc.call_id and calls.user_id = u_id
						where cc.compound_id = cr_nid
				and EXISTS (select 1 from call_compounds cc where cc.compound_id = cr_did and cc.call_id = calls.call_id)
		)::float / NULLIF(
				(select count(1) from call_compounds cc
						inner join calls on calls.call_id = cc.call_id and calls.user_id = u_id
						where cc.compound_id = cr_did
		), 0)::float as ratio,
		cr_id as compound_ratio_id,
		team_id as team_id,
		ct_id as call_type_id,
		u_id as user_id
FROM crs