[sum & case when] Queries Quality and Percentage

 

링크

https://leetcode.com/problems/queries-quality-and-percentage/description/

 

문제

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.

The result format is in the following example.

정답

with cnt_name as
(
    select query_name, count(query_name) as query_cnt
    from queries
    group by query_name
)
select query_name,
        round(sum(quality) / query_cnt, 2) as quality,
        round((sum(case when rating < 3 then 1 else 0 end) / query_cnt) * 100, 2) as poor_query_percentage
from (
    select q.query_name,
            position,
            rating,
            query_cnt,
            (rating / position) as quality
    from queries q
    left join cnt_name n
    on q.query_name = n.query_name
    ) a
group by query_name

select
query_name,
round(avg(cast(rating as decimal) / position), 2) as quality,
round(sum(case when rating < 3 then 1 else 0 end) * 100 / count(*), 2) as poor_query_percentage
from
queries
group by
query_name;

SELECT query_name,
       ROUND(AVG(rating / position), 2) AS quality,
       ROUND(AVG(IF(rating < 3, 1, 0)) * 100, 2) AS poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY query_name;

새롭게 이해한 내용

• if구문 혹은 case when도 바로 sum, avg 등 바로 적용 가능
• 그룹 함수 적용 이후에 바로 계산 가능