Created
September 13, 2018 18:31
-
-
Save austindoeswork/ce391aca030a0828090f7998950f42d4 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\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 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment