Let’s say we want to know for A and B, what percentage of jobs complete. We’d also like to know what percentage of jobs finish under a certain time threshold. Now this is drastically simplified, but you can imagine that job type could be any complex set of variables you want to analyze by: region, language, type of rendering, whatever.

Here’s a query that will calculate the ratio of completed jobs by type:

select type,
avg(cast(
case
  when state='completed' then 1
  else 0
end
as float)
) as completed_ratio
from jobs
group by type

The way it works:

1. The case statement sets of value of 1 for the success condition, and 0 for any other.
2. These are integers, so we cast them as float, so we’ll get the averaging behavior we want.
3. We avg them, to get the average.


select type,
avg(cast(
case
  when datediff(minute, completed, started) < 3 then 1
  else 0
end
as float)
) as completed_ratio
from jobs
group by type