[코드카타] SQL 34 Queries Quality and Percentage

[코드카타] SQL 34 Queries Quality and Percentage

34. Queries Quality and Percentage
https://leetcode.com/problems/queries-quality-and-percentage/

We define query quality as:
 : The average of the ratio between query rating and its position.
We also define poor query percentage as:
 : The percentage of all queries with rating less than 3.
Write a solution to find each query_name, the quality and poor_query_percentage.
Both quality and poor_query_percentage should be rounded to 2 decimal places.
Return the result table in any order.

select query_name, 
       round(avg((rating/position)),2) quality, 
       round(count(case when rating < 3 then 1 end)/count(*)*100, 2) poor_query_percentage 
from Queries
where query_name is not null
group by query_name

- is not null을 사용한 이유 test에서 null값이 존재하는 경우가 있기 때문
- count() 함수 안에 조건을 어떻게 하느냐에 따라 원하는 결과를 도출할 수 있음
- count() 대신에 sum()을 사용할 수 있음
  round(sum(case when rating < 3 then 1 else 0 end)/count(*)*100, 2)