QA » openQA Project » openQA Infrastructure

I tried

with passed_jobs as (select distinct jobs.id, result, job_groups.name as group_name from jobs left join job_groups on jobs.group_id = job_groups.id where result = 'passed') select group_name, round(count(passed_jobs.id) * 100. / (select count(*) from passed_jobs), 2)::numeric(5,2)::float as ratio_passed_by_group_long_term from passed_jobs group by group_name

but this gives low numbers per job group. I guess we need to divide by the number of all jobs per job group. The "with" statement should not already filter by 'passed'.

Maybe better:

with all_jobs as (select distinct jobs.*, job_groups.name as group_name from jobs left join job_groups on jobs.group_id = job_groups.id) select group_name, round(count(all_jobs.id) * 100. / (select count(*) from all_jobs where result = 'passed'), 2)::numeric(5,2)::float as ratio_passed_by_group_long_term from all_jobs group by group_name;

takes 25s. Query is still slow and numbers for many jobs seem low but could be realistic taking into account that we consider mostly failed jobs linked to bugs "important" hence the ratio is always smaller than realistically representative