题目链接:262. 行程和用户 - 力扣(LeetCode)
select Request_at as `Day`,
round(count(case when Status != 'completed' then Id end)/count(*),2) as `Cancellation Rate`
from Trips
where Client_Id not in( select Users_Id as Client_Id from Users where Banned = 'Yes')
and Request_at between '2013-10-01' and '2013-10-03'
group by Request_at
注意这里的count和case结合的用法。然后就是round取小数位数的用法。
用这种sum的方法也很好:
select query_name,
round(avg(rating/position),2) quality,
round(sum(case when rating<3 then 1 else 0 end)/count(*),4)*100 poor_query_percentage
from queries
group by query_name;