[TIL] 코드카타 오답노트

📝 TIL - 2024.10.23

with 구문만 3개에 섭쿼리 인라인구문까지해서 넘 복잡하게 만들어졌다.
이날, 좀 머리가 안돌아가기도 했고, 그동안 익숙한 with 구문 & 조인해서 나열하는 방식으로만 구조를 짜다 보니 효율이 떨어지는 거같다.

🔍 오늘 배운 주요 포인트:

다른 팀원의 짧은 쿼리를 보니, 아 quality가 rating/position의 평균값이었군,,, 알게됐고,
poor_query_percentage또한 if 문을 써서 좀더 쉽게 표현할 수 있다는 걸 확인했음
다음엔 나도 쉽게좀 짜봐야지... 넘 불필요하게 복잡하게 생각했던 거같다.

👩 코드카타 출처 :

링크

📄 질문 발췌 :

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

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.

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

📄 내가 푼 풀이 방법 :

위의 문제를 번역해보자면,
Dog Queries 별로 quality, poor query_percentage 계산해서 보여달라는 건데
quality의 경우 rating/position 의 합을 dog queries의 수로 나눈 값으로
poor query_percentage의 경우 rating이 3 이하인 dog query의 비중을 구하면 되는 걸로 보임

각각의 식이 복잡해서 나는 quality 따로, poor_ query_percentage 따로 구해서 query_name 을 키값으로 조인해주면 되겠다 이렇게 생각했는데,
생각보다 구문이 길어져버렸다.

# Write your MySQL query statement below
WITH aa AS --- query 네임별 갯수 구하는 공식 테이블 ----
(SELECT query_name,
       count(query_name) as cnt_total
FROM Queries
GROUP BY 1),
bb AS  --- rating이 3 이하인 query 네임별 갯수 구하는 공식 테이블 ---
(SELECT query_name,
       count(query_name) as cnt_3
FROM Queries
WHERE rating < 3
GROUP BY 1),
cc AS --- poor_query_percentage 공식 테이블 ---
(
SELECT aa.query_name,
       round(cnt_3/cnt_total*100,2) as poor_query_percentage
FROM aa 
LEFT JOIN bb 
ON aa.query_name = bb.query_name
GROUP BY 1
) 
SELECT t.query_name,
       t.quality,
       cc.poor_query_percentage
FROM
(SELECT query_name,
       round(sum(rating/position)/count(query_name),2) as quality
FROM queries
GROUP BY 1
)t 
LEFT JOIN cc
ON t.query_name = cc.query_name

📄 팀원이 공유해준 간단한 풀이 :

SELECT
    query_name
    , round(avg(rating / position),2) as 'quality' 
    , round(sum(if(rating < 3, 1, 0)) / count(1) * 100, 2) as 'poor_query_percentage'
FROM
    queries
WHERE
    query_name IS NOT NULL
GROUP BY
    query_name
;