1211.Queries Quality and Percentage

* Problem

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.

 

Explanation: 
Dog queries quality is ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog queries poor_ query_percentage is (1 / 3) * 100 = 33.33

Cat queries quality equals ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat queries poor_ query_percentage is (1 / 3) * 100 = 33.33

 

* 1st try(Wrong)

# Write your MySQL query statement below select query_name,            round((sum(rating/position)/count(query_name)),2) as quality,            round(avg(case when rating < 3 then 1 else 0 end)*100,2) as poor_query_percentage     from Queries     group by query_name

 

query_name이 null인 값에 대한 고려가 안되어 test case 에서 걸렸다. (* 항상 케이스 고려 명심할것!!)

 

* Final Solution (Success)

# Write your MySQL query statement below select query_name,             round((sum(rating/position)/count(query_name)),2) as quality,             round(avg(case when rating < 3 then 1 else 0 end)*100,2) as poor_query_percentage     from Queries where query_name is not null     group by query_name