1211. Queries Quality and Percentage

Table: Queries

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| query_name | varchar |
| result | varchar |
| position | int |
| rating | int |
+-------------+---------+
This table may have duplicate rows.
This table contains information collected from some queries on a database.
The position column has a value from 1 to 500.
The rating column has a value from 1 to 5. Query with rating less than 3 is a poor query.

• 테이블 정의
  이 테이블은 중복된 행이 있다.
  이 테이블은 테이터 베이스에 일부 쿼리로부터 수집된 정보가 포함되어있다.
  position 컬럼은 1~500까지의 값이 있다.
  rating 컬럼은 1~5의 값이 있으며 3보다 작은 rating은 좋지 않은 쿼리다.

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.

• 문제
  쿼리 품질을 정의한다:
  rating과 position 간의 비율 평균이다.
  3보다 작은 rating의 모든 쿼리들의 백분율은 좋지 않은 쿼리의 백분율이다..
  각 query_name에서 품질 과 'poor_query_percentage.의 해결방안을 적어라.
  두 quality와 poor_query_percentage는 2개의 소수점을 반올림 해라
  그 결과 테이블은 순서에 상관 없다.
  그 결과 형식은 아래 예제를 따른다.

Example 1:

Input:
Queries table:
+------------+-------------------+----------+--------+
| queryname | result | position | rating |
+------------+-------------------+----------+--------+
| Dog | Golden Retriever | 1 | 5 |
| Dog | German Shepherd | 2 | 5 |
| Dog | Mule | 200 | 1 |
| Cat | Shirazi | 5 | 2 |
| Cat | Siamese | 3 | 3 |
| Cat | Sphynx | 7 | 4 |
+------------+-------------------+----------+--------+
Output:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
+------------+---------+-----------------------+
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

flow
1. Dog와 Cat을 그룹화 해야한다.
2. quality 와 poor_query_percentage의 평균 비율을 두 가지 나눠서 정의해야 한다.
3. quality 은 AVG(rating/position) 로 구한다.
4. poor_query_percentage은 CASE WHEN문을 활용해 3미만은 1 아닌 경우는 0으로 나누어
3미만의 갯수만 추출한다.

첫 번째 풀이

select 
	query_name 
    ,round(AVG(rating/position), 2) quality
from Queries
group by query_name

quality 계산하여 조회하고 query_name으로 그룹화 한 후 round함수로 소수점 둘 째자리를 반환한다.

두 번째 풀이

select 
	query_name 
	,round(sum(case when rating  < 3 then 1 else 0 end) * 100 / count(*)
, 2) as poor_query_percentage
from Queries
group by query_name

위 와 같은 방식으로 정의하고 rating이 3 미만일 경우를 추출하여 poor_query_percentage를 만들어 준다.

1번과 2번 풀이 합치기

with q1 as 
(
select 
	query_name 
    ,round(AVG(rating/position), 2) quality
from Queries
group by query_name
),
p2 as 
(
select 
	query_name
	,round(sum(case when rating  < 3 then 1 else 0 end) * 100 / count(*)
    , 2) as poor_query_percentage
from Queries
group by query_name
)

이를 사용하기 쉽게 with문으로 바꿔준다.

최종 쿼리

with q1 as 
(
select 
	query_name 
    ,round(AVG(rating/position), 2) quality
from Queries
group by query_name
),
p2 as 
(
select 
	query_name 
    ,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 q1.query_name , q1.quality , p2.poor_query_percentage
from q1 join p2 on q1.query_name=p2.query_name

q1과 q2를 join해주고 필요한컬럼을 해당 테이블에서 가져와 조회한다.

---

서브쿼리를 이용한 풀이

SELECT 
	 q1.query_name
     ,ROUND(q1.quality, 2) AS quality
     ,ROUND(q2.poor_query_percentage, 2) AS poor_query_percentage
FROM (
    SELECT 
    	query_name
  	   ,AVG(rating / position) AS quality
    FROM Queries
    GROUP BY query_name
) q1
JOIN (
    SELECT 
    	  query_name
          ,(SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) 
           AS poor_query_percentage
    FROM Queries
    GROUP BY query_name
) q2
ON q1.query_name = q2.query_name;