[데이터분석] 코드카타 95

95. 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.

 

1) ratio between query rating and its position  (컬럼들(평가와 순위)간 평균비율..어케 구해?)

     -> 너무 겁먹기 말자.  position 과 rating 둘다 int 타입 컬럼들임. ->  avg (rating/position) 하면 됨.

 

2) percentage of all queries with rating less than 3  (3점미만 모든 쿼리 퍼센티지는..또 어떻게 구해..?)

     -> 얘도 생각보다 간단.. sum(rating<3)  (3보다 작은 rating의 합)을 전체 count(*) 로 나눈다 -- 요 부분은 물어봐야겠음. 어째서 rating(*)  이 아닌지.. 

     -> 어쨋든 그러면 3점 미만 모든 쿼리의 % 나옴..! 

select query_name,

       round(avg(rating/position),2) as quality,

       round(sum(rating<3)/count(*)*100,2) as poor_query_percentage

from Queries

where query_name is not null

group by 1

 

 

(참고블로그)

https://xkdls19.tistory.com/428#%EB%AC%B8%EC%A0%9C