
with 

b as -- all tests on one weekday
(select distinct codedevent_id, conceptid from codedevent_snomed where consultationdate = '2020-02-03'),

a as -- of which had comparators 
(select codedevent_id from codedeventrange 
where comparator != 4 and codedevent_id in (select codedevent_id from b)
),

c as (select conceptid, count(distinct a.codedevent_id) as num, count(distinct b.codedevent_id) as test_count
from b left join a on a.codedevent_id = b.codedevent_id
group by conceptid
having count(distinct b.codedevent_id)>50
and count(distinct a.codedevent_id)>0
)

select *, 100*num/test_count as pc
from c
order by pc desc, test_count desc


