[Leetcode]570.Managers with at least 5 Direct Reports, 1211.Queries Quality and Percentage

1. https://leetcode.com/problems/managers-with-at-least-5-direct-reports/

LeetCode - The World's Leading Online Programming Learning Platform

Q. 아래와 같은 테이블이 있다고 하자. 

Write a solution to find managers with at least five direct reports.

Return the result table in any order.

The result format is in the following example.

A. 사실 조건문 내의 조건문만 안다면 어렵지 않다 :) 다만 Having 절에서 Count 함수를 불러오는 데선 애먹었

SELECT name 
FROM Employee
WHERE id IN (
    SELECT managerId
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(managerId) >= 5
)

And yes, accepted ㅎㅋㅎ

2.  Queries Quality and Percentage (1211)

LeetCode - The World's Leading Online Programming Learning Platform

Q.

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의 평균을 구한 것이 각 항목별 queries quality가 되는 것이고, 3개 중 1개니까(dog의 경우 3 미만인 거!) 1/3이라는 것이다. 구하는 식을 알았으니 각 칼럼만 빼오면 되겠다 ! 그리고 Round 함수를 써서 select할 대상을 확실하게 정해주면 됨

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
;

Round를 하도 오랜만에 써서 좀 낯설었기에 submit, run 다 해봤는데 all accepted ~