🔓 09. LeetCode SQL 50 Easy LEVEL 문제풀이

모바일은 화면을 돌려 가로화면으로 보시는 게 읽으시기 편할 수 있습니다. 돌려서 보시는 걸 추천드릴게요!!

 

 

리트코드에 SQL 코딩테스트를 위해 선정된 50문제 번들이 있길래, 이 문제들을 통해서 SQL 공부를 해보고자 합니다.

리트코드는 행커랭크나 프로그래머스에 비해 히든케이스가 자세한 편이라 좀 더 깊게 학습하는 데에 좋을 것 같습니다.

아래는 해당 문제들의 링크입니다!!

 

SQL 50 - Study Plan - LeetCode

 

---

🔑 Q1757. Recyclable and Low Fat Products  

      

☑️ 문제

Recyclable and Low Fat Products - LeetCode

 

 

☑️ 풀이

SELECT product_id 
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y';

 

---

🔑 Q584. Find Customer Referee

       

☑️ 문제

Find Customer Referee - LeetCode

 

 

☑️ 풀이

SELECT name
FROM Customer
WHERE referee_id != 2 OR referee_id IS NULL;

 

---

🔑 Q595. Big Countries

       

☑️ 문제

Big Countries - LeetCode

 

 

☑️ 풀이

SELECT name, population, area
FROM World
WHERE population >= 25000000 OR area >= 3000000;

 

---

🔑 Q1148. Article Views I

       

☑️ 문제

Article Views I - LeetCode

 

 

☑️ 풀이

SELECT DISTINCT(author_id) AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY 1;

 

---

🔑 Q1683. Invalid Tweets

       

☑️ 문제

Invalid Tweets - LeetCode

 

 

☑️ 풀이

SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;

 

---

🔑 Q1378. Replace Employee ID With The Unique Identifier

       

☑️ 문제

Replace Employee ID With The Unique Identifier - LeetCode

 

 

☑️ 풀이

SELECT unique_id, E.name
FROM Employees AS E
LEFT JOIN EmployeeUNI AS U
ON E.id = U.id

 

---

🔑 Q1068. Product Sales Analysis I

       

☑️ 문제

Product Sales Analysis I - LeetCode

 

 

☑️ 풀이

SELECT product_name, year, price
FROM Sales AS S
INNER JOIN Product AS P
ON S.product_id = P.product_id;

 

---

🔑 Q1581. Customer Who Visited but Did Not Make Any Transactions

       

☑️ 문제

Customer Who Visited but Did Not Make Any Transactions - LeetCode

 

 

☑️ 풀이

SELECT customer_id, COUNT(customer_id) AS count_no_trans
FROM Visits AS V
LEFT JOIN Transactions AS T
ON V.visit_id = T.visit_id
WHERE T.visit_id IS NULL
GROUP BY 1;

 

---

⛔ Q1661. Average Time of Process per Machine

       

☑️ 문제

Average Time of Process per Machine - LeetCode

 

 

☑️ 풀이

- 괜히 어렵게 생각하지 말기!!

- 내가 알고 있는 지식 내에서 최대한 쉽게 풀 수 있는 방법 찾기

SELECT A1.machine_id, ROUND(AVG(A2.timestamp - A1.timestamp), 3) AS processing_time
FROM Activity AS A1
INNER JOIN Activity AS A2
ON A1.machine_id = A2.machine_id AND A1.process_id = A2.process_id 
AND A1.activity_type = 'start' AND A2.activity_type = 'end'
GROUP BY 1;

 

---

🔑 Q577. Employee Bonus

       

☑️ 문제

Employee Bonus - LeetCode

 

 

☑️ 풀이

SELECT name, bonus
FROM Employee AS E
LEFT JOIN Bonus AS B
ON E.empId = B.empId
WHERE bonus < 1000 OR bonus IS NULL;

 

---

⛔ Q1280. Students and Examinations

       

☑️ 문제

Students and Examinations - LeetCode

 

 

☑️ 풀이

- CROSS JOIN 함수

- 생각 빨리 해서 풀이 속도 높이기

WITH TEMP AS 
(
    SELECT *
    FROM Students 
    CROSS JOIN Subjects
)

SELECT T.student_id, T.student_name, T.subject_name, 
COUNT(E.subject_name) AS attended_exams
FROM TEMP AS T
LEFT JOIN Examinations AS E
ON T.student_id = E.student_id AND T.subject_name = E.subject_name
GROUP BY 1, 2, 3
ORDER BY 1, 3

 

---

🔑 Q620. Not Boring Movies

       

☑️ 문제

Not Boring Movies - LeetCode

 

 

☑️ 풀이

SELECT *
FROM Cinema
WHERE id % 2 = 1 AND description != 'boring'
ORDER BY rating DESC;

 

---

🔑 Q1251. Average Selling Price

       

☑️ 문제

Average Selling Price - LeetCode

 

 

☑️ 풀이

SELECT DISTINCT(P.product_id),
IFNULL(ROUND(
SUM(price * units) OVER(PARTITION BY product_id) 
/ SUM(units) OVER(PARTITION BY product_id),2),0) AS average_price
FROM Prices AS P
LEFT JOIN UnitsSold AS U
ON P.product_id = U.product_id AND purchase_date BETWEEN start_date AND end_date

 

---

🔑 Q1075. Project Employees I

       

☑️ 문제

Project Employees I - LeetCode

 

 

☑️ 풀이

SELECT DISTINCT(project_id), 
ROUND(AVG(experience_years) OVER(PARTITION BY project_id), 2) AS average_years
FROM Project AS P
INNER JOIN Employee AS E
ON P.employee_id = E.employee_id

 

---

🔑 Q1633. Percentage of Users Attended a Contest

       

☑️ 문제

Percentage of Users Attended a Contest - LeetCode

 

 

☑️ 풀이

SELECT contest_id, 
ROUND(COUNT(user_id) / (SELECT COUNT(user_id) FROM Users) * 100, 2) AS percentage
FROM Register 
GROUP BY 1
ORDER BY 2 DESC, 1;

 

---

⛔ Q1211. Queries Quality and Percentage

       

☑️ 문제

Queries Quality and Percentage - LeetCode

 

 

☑️ 풀이

- 재밌는 문제였음

- 용도를 구분할 수 있는 테이블이면 구분해서 푸는 것도 좋음

- 서브쿼리를 많이 사용하는 것 보다는 WITH 구문 사용해서 가독성과 성능 높이기

- 각 테이블 용도 구분 가능

WITH QS AS 
(
    SELECT DISTINCT(query_name), 
    ROUND(AVG(rating / position) OVER(PARTITION BY query_name), 2) AS quality 
    FROM Queries
),
P AS 
(
    SELECT Q.query_name, 
    IFNULL(ROUND(poor / COUNT(rating) * 100, 2), 0) AS poor_query_percentage
    FROM Queries AS Q
    LEFT JOIN 
    (
    	SELECT query_name, COUNT(rating) AS poor 
    	FROM Queries WHERE rating < 3 
        GROUP BY query_name
    ) AS C
    ON Q.query_name = C.query_name
    GROUP BY 1
)

SELECT QS.query_name, quality, poor_query_percentage
FROM QS
INNER JOIN P
ON QS.query_name = P.query_name

 

---

🔑 Q2356. Number of Unique Subjects Taught by Each Teacher

       

☑️ 문제

Number of Unique Subjects Taught by Each Teacher - LeetCode

 

 

☑️ 풀이

SELECT teacher_id, COUNT(DISTINCT(subject_id)) AS cnt
FROM Teacher
GROUP BY 1

 

---

🔑 Q1141. User Activity for the Past 30 Days I

       

☑️ 문제

User Activity for the Past 30 Days I - LeetCode

 

 

☑️ 풀이

- 그룹 잘 확인하고 쿼리 짜기!!

SELECT activity_date AS day, COUNT(DISTINCT(user_id)) AS active_users
FROM Activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY 1

 

---

🔑 Q596. Classes More Than 5 Students

       

☑️ 문제

Classes More Than 5 Students - LeetCode

 

 

☑️ 풀이

- 쉽게쉽게 생각하기

SELECT class
FROM Courses
GROUP BY 1
HAVING COUNT(student) >= 5

 

---

🔑 Q1729. Find Followers Count

       

☑️ 문제

Find Followers Count - LeetCode

 

 

☑️ 풀이

SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY 1
ORDER BY 1

 

---

🔑 Q619. Biggest Single Number

       

☑️ 문제

Biggest Single Number - LeetCode

 

 

☑️ 풀이

- 조건 두개 : 분리해서 생각하는 습관 들이기

SELECT MAX(num) AS num
FROM
(
    SELECT num
    FROM MyNumbers
    GROUP BY 1
    HAVING COUNT(num) = 1
) AS TEMP

 

---

⛔ Q1731. The Number of Employees Which Report to Each Employee

       

☑️ 문제

The Number of Employees Which Report to Each Employee - LeetCode

 

 

☑️ 풀이

SELECT E.employee_id, name, reports_count, average_age
FROM Employees AS E
INNER JOIN
(
    SELECT reports_to, 
    COUNT(employee_id) AS reports_count, ROUND(AVG(age), 0) AS average_age
    FROM Employees 
    GROUP BY 1
    HAVING reports_count >= 1 AND reports_to IS NOT NULL
) AS TEMP
ON E.employee_id = TEMP.reports_to
ORDER BY 1;

 

---

🔑 Q 1789. Primary Department for Each Employee   

 

☑️ 문제

Primary Department for Each Employee - LeetCode

 

 

☑️ 풀이

-- primary department 존재 유무 마킹
WITH TEMP AS 
(
    SELECT *,
    SUM(CASE WHEN primary_flag = 'N' THEN 0 ELSE 1 END) AS mark 
    FROM Employee
    GROUP BY employee_id
)

-- primary department가 존재하지 않는 경우
SELECT employee_id, department_id FROM Employee
WHERE employee_id 
IN
(
    SELECT employee_id FROM TEMP WHERE mark = 0
)
GROUP BY 1 HAVING COUNT(department_id) = 1

UNION ALL

-- primary department가 존재하는 경우
SELECT employee_id, department_id FROM Employee
WHERE employee_id 
IN
(
    SELECT employee_id FROM TEMP WHERE mark = 1
)
AND primary_flag = 'Y'

 

---

🔑 Q610. Triangle Judgement

       

☑️ 문제

Triangle Judgement - LeetCode

 

 

☑️ 풀이

SELECT x, y, z,
CASE
WHEN x >= y AND x >= z AND x < y + z THEN 'Yes'
WHEN y >= x AND y >= z AND y < x + z THEN 'Yes'
WHEN z >= y AND z >= x AND z < y + x THEN 'Yes'
ELSE 'No' END AS triangle
FROM Triangle

 

---

⛔ Q1978. Employees Whose Manager Left the Company

       

☑️ 문제

Employees Whose Manager Left the Company - LeetCode

 

 

☑️ 풀이

- 너무 어렵게 생각하고 풀어서 아쉬웠던 문제

- 쉽게쉽게, 문제 조건에 맞게 생각하기

SELECT E1.employee_id
FROM Employees AS E1
LEFT JOIN Employees AS E2
ON E1.manager_id = E2.employee_id
WHERE E1.manager_id IS NOT NULL AND E2.employee_id IS NULL AND E1.salary < 30000
ORDER BY 1

 

---

🔑 Q1667. Fix Names in a Table

       

☑️ 문제

Fix Names in a Table - LeetCode

 

 

☑️ 풀이

SELECT user_id, 
CONCAT(UPPER(SUBSTR(name, 1, 1)), LOWER(SUBSTR(name, 2))) AS name
FROM Users
ORDER BY 1

 

---

⛔ Q1527. Patients With a Condition

       

☑️ 문제

Patients With a Condition - LeetCode

 

 

☑️ 풀이

- 정규표현식

- 시작 : ^

- 공백 : \s

SELECT *
FROM Patients
WHERE conditions REGEXP('^DIAB1') OR conditions REGEXP('\\sDIAB1')

 

---

🔑 Q196. Delete Duplicate Emails

       

☑️ 문제

Delete Duplicate Emails - LeetCode

 

 

☑️ 풀이

DELETE P1 FROM Person AS P1, Person AS P2
WHERE P1.id > P2.id AND P1.email = P2.email

 

☑️ DELETE 구문

DELETE T1 FROM Table1 AS T1, Table2 AS T2
WHERE condition ~~

 

---

🔑 Q1484. Group Sold Products By The Date

       

☑️ 문제

Group Sold Products By The Date - LeetCode

 

 

☑️ 풀이

SELECT sell_date, COUNT(DISTINCT(product)) AS num_sold,
GROUP_CONCAT(DISTINCT(product)) AS products
FROM Activities
GROUP BY 1
ORDER BY 1, 3

 

☑️ GROUP_CONCAT( ) 구문

SELECT GROUP_CONCAT(col)
FROM Table
GROUP BY col

 

---

🔑 Q1327. List the Products Ordered in a Period

       

☑️ 문제

List the Products Ordered in a Period - LeetCode

 

 

☑️ 풀이

SELECT product_name, unit
FROM Products AS P
INNER JOIN
(
    SELECT DISTINCT(product_id), 
    SUM(unit) OVER(PARTITION BY product_id) AS unit 
    FROM Orders 
    WHERE order_date LIKE '2020-02-%'
) AS T
ON P.product_id = T.product_id
WHERE unit >= 100

 

---

⛔ Q1517. Find Users With Valid E-Mails

       

☑️ 문제

Find Users With Valid E-Mails - LeetCode

 

 

☑️ 풀이

- 정규표현식

- 반드시 들어가는 패턴 : [ ]

- 패턴이 0개 혹은 한번 이상 반복되는 경우 : *

- 패턴이 한번 이상 반복되는 경우 : +

- 특수문자 : 문자 앞에 \ 붙여서 표현

SELECT *
FROM Users
WHERE mail REGEXP('^[a-zA-Z][a-zA-Z0-9\_\.\-]*\@leetcode[\.]com$')

 

---

 

대략 한 달 전에 한번씩 풀어보고, 본 포스팅을 작성하면서 다시 한번씩 풀어보는 중입니다. 처음 풀 때는 리트코드만의 히든케이스에 정신 못차리고 Easy 문제 하나 푸는 데에도 꽤나 많은 시간이 들었는데, 지금은 나름 괜찮게 풀고 있는 것 같아 마음에 드는 요즘입니다. 오히려 쿼리 제출하고 시간 줄이는 데 더 오래 걸리는 것 같네요,,,

 

WINDOW 함수를 사용할 줄 아니까 많이 편해진 것 같습니다. 이걸 모를 때는 어떻게 풀었을까 생각을 할 수 없을 정도입니다. 괜히 MySQL 고급 문법이 아니구나를 느끼는 요즘입니다,,, 꾸준히 사용하면서 몸에 익혀야겠습니다!!

 

그리고 정규표현식과 관련된 부분은 더 공부해야 할 것 같습니다. 파이썬에서의 정규식 문법과 같기 때문에, 예전에 배운 내용을 다시 한번 복습해야겠네요 ㅎㅎ

 

마지막으로, 풀이 과정에서 괜히 어렵게 생각하는 경우가 많다고 느꼈습니다. 좀 더 많은 문제를 풀어보고 다양한 레퍼런스를 수집하되, 다른 사람들이 작성한 쿼리를 보고 디벨롭하는 과정에 좀 더 시간을 투자해야겠습니닷!!

 

---