[SQL]leetcode medium 뿌시기

2021/10/31

1. #608. Tree Node (14min)

Tree: id | p_id

id is the primary key column for this table. 

*Each node in the tree can be one of three types: Leaf/Root/Inner(neither a leaf node nor a root node)

Write an SQL query to report the type of each node in the tree. Return the result table ordered by id in ascending order. The query result format is in the following example.

 

--output: id | type

# Write your MySQL query statement below SELECT id,        CASE WHEN Tree.id = (SELECT atree.id FROM Tree atree WHERE atree.p_id IS NULL) THEN 'Root'             WHEN Tree.id IN (SELECT atree.p_id FROM Tree atree) THEN 'Inner'             ELSE 'Leaf' END As type FROM Tree

변수명 대소문자 주의, id와 p_id 구분 잘하기

 

2. #1164. Product Price at a Given Date (45 min)

Products: product_id | new_price | change_date (date)

(product_id, change_date) is the primary key 

each row of this table indicates that the price of some product was changed to a new price at some date.

Write an SQL query to find the prices of all products on 2019-08-16.

Assume the price of all products before any change is 10. Return the result table in any order.

SELECT t1.product_id, IFNULL(t2.new_price,10) AS price FROM (SELECT DISTINCT product_id FROM Products) AS t1 LEFT JOIN (SELECT product_id, new_price FROM Products            WHERE (product_id, change_date) IN (SELECT product_id, MAX(change_date)                                                              FROM Products                                                             WHERE change_date<='2019-08-16'                                                             GROUP BY product_id)) AS t2 ON t1.product_id = t2.product_id

IFNULL 하고 ON 빼먹지말자.

왼쪽(상품명) LEFT JOIN 오른쪽(가장 최근 바뀐 가격)

FROM (섭쿼리1) AS t1 LEFT JOIN (섭쿼리2) AS t2

여기서 AS는 선택이고 (소문자로쓸경우), 섭쿼리쓰면 SELECT, ON에 정확하게 소속 써주기. 

 

3. #1270. All People Report to the Given Manager #18min

Employees: employee_id | employee_name | manager_id

employee_id is the primary key for this table.

Write an SQL query to find employee_id of all employees that directly or indirectly report their work to the head of the company. The indirect relation between managers will not exceed three managers as the company is small.

Return the result table in any order.

SELECT e1.employee_id FROM Employees e1,      Employees e2,      Employees e3 WHERE e1.manager_id = e2.employee_id   AND e2.manager_id = e3.employee_id   AND e3.manager_id = 1    AND e1.employee_id != 1    SELECT e1.employee_id FROM Employees e1 JOIN Employees e2 ON e1.manager_id = e2.employee_id JOIN Employees e3 ON e2.manager_id = e3.employee_id WHERE e3.manager_id = 1 AND e1.employee_id != 1

4. #1132. Reported Posts II (50min)

Actions: user_id | post_id | action_date(date) | action | extra(varchar)

no primary key for this table. 

action column is an ENUM type ('view', 'like', 'reaction', 'comment', 'report', 'share')

Removals: post_id | remove_date(date)

post_id is the primary key of the table. 

 

Write an SQL query to find the average daily percentage of posts that got removed after being reported as spam, rounded to 2 decimal places. The query result format is in the following example.

SELECT ROUND(AVG(cnt),2) AS average_daily_percent FROM (SELECT (COUNT(DISTINCT r.post_id)/COUNT(DISTINCT a.post_id)*100) AS cnt  FROM Actions a  LEFT JOIN Removals r  ON a.post_id = r.post_id  WHERE action = 'report' AND extra='spam'  GROUP BY action_date) tmp

괄호 잘 보기!

report와 spam이 같은 row에 있다는 것을 캐치하고 활용하기