[SQL] LeetCode SQL 50 Review

SELECT

---

BASIC JOINS

[1]

https://leetcode.com/problems/rising-temperature/?envType=study-plan-v2&envId=top-sql-50 

Rising Temperature - LeetCode

# Write your MySQL query statement below
SELECT w1.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1 AND w1.temperature > w2.temperature;

한 테이블의 같은 열 안의 두 값을 비교하는 경우 (같은 열 내에서 값의 비교)

해당 문제를 해결하기 위해서는 한 테이블에 대해서 두 번 참조해야 함..!

DATEDIFF

두 개의 날짜 값의 차이를 int로 반환하는 함수

DATEDIFF(interval, date1, date2)

https://www.w3schools.com/sql/func_sqlserver_datediff.asp

SQL Server DATEDIFF() Function

[2]

https://leetcode.com/problems/employee-bonus/description/

Employee Bonus - LeetCode

# Write your MySQL query statement below
SELECT NAME, BONUS
FROM EMPLOYEE E
LEFT JOIN BONUS B
#ON E.EMPID = B.EMPID
USING(EMPID)
WHERE B.BONUS < 1000 OR B.BONUS IS NULL

JOIN 연산을 사용할 때 USING 사용하는 습관을 들이기!

NULL 값은 자동적으로 없어지는 경우가 많으니까 해당 조건을 활용하려면 명시해야 함

[3]

https://leetcode.com/problems/students-and-examinations/description/

Students and Examinations - LeetCode

#우선 EXAMINATION 테이블에 대해서 원하는 결과를 위해서는 COUNT(*)를 했어야 함!
#SELECT SUBJECT_NAME, STUDENT_ID, COUNT(STUDENT_ID)
#FROM EXAMINATIONS E
#GROUP BY STUDENT_ID, SUBJECT_NAME

#테이블 3개에 대한 연산
SELECT s.student_id, s.student_name, sub.subject_name, COALESCE(e.attended_exams, 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN (
    SELECT student_id, subject_name, COUNT(*) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
) e USING (student_id, subject_name)
ORDER BY s.student_id, sub.subject_name;

3개의 테이블에 대한 연산!

[4]

https://leetcode.com/problems/average-time-of-process-per-machine/description/

Average Time of Process per Machine - LeetCode

#우선 동일한 테이블에 대해서 조인 연산을 해야 해
SELECT A1.MACHINE_ID, ROUND(AVG(A2.TIMESTAMP - A1.TIMESTAMP), 3) AS PROCESSING_TIME
FROM ACTIVITY A1
JOIN ACTIVITY A2
WHERE A1.MACHINE_ID = A2.MACHINE_ID
AND A1.PROCESS_ID = A2.PROCESS_ID
AND (A1.TIMESTAMP < A2.TIMESTAMP)
GROUP BY MACHINE_ID

한 테이블 안에서 값에 대한 비교나 연산을 할 때는 동일한 테이블에 대해서 조인연산을 한다고 인지 해야 함!

---

BASIC AGGREGATE FUNCTIONS

[1]

https://leetcode.com/problems/game-play-analysis-iv/description/

Game Play Analysis IV - LeetCode

# Write your MySQL query statement below
SELECT ROUND(COUNT(DISTINCT player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction
FROM Activity
WHERE
  (player_id, DATE_SUB(event_date, INTERVAL 1 DAY))
  IN (
    SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id
  )

DATE_SUB()

기준 날짜에 입력된 기간만큼을 빼는 함수

(DATE_ADD : 기준 날짜에 입력된 기간만큼을 더하는 함수)

DATE_SUB(date, INTERVAL value interval)

[2]

https://leetcode.com/problems/monthly-transactions-i/description/

Monthly Transactions I - LeetCode

#날짜형 변환할 떄는 DATE_FORMAT 사용하는 것 기억하기!
SELECT DATE_FORMAT(TRANS_DATE, '%Y-%m') AS MONTH, 
COUNTRY, 
COUNT(*) AS TRANS_COUNT, 
SUM(IF(STATE='APPROVED', 1, 0)) AS APPROVED_COUNT, 
#COUNT(STATE='APPROVED') AS APPROVED_COUNT, 
SUM(AMOUNT) AS TRANS_TOTAL_AMOUNT, 
SUM(IF(STATE='APPROVED', AMOUNT, 0)) AS APPROVED_TOTAL_AMOUNT
FROM TRANSACTIONS
GROUP BY COUNTRY, MONTH

DATE_FORMAT

날짜 포맷 형식 지정 및 변경하는 함수

DATE_FORMAT(date, format)

https://www.w3schools.com/sql/func_mysql_date_format.asp

MySQL DATE_FORMAT() Function

IF 함수

IF(조건, 참일 때 값, 거짓일 때 값)

[3]

https://leetcode.com/problems/queries-quality-and-percentage/description/

Queries Quality and Percentage - LeetCode

SELECT QUERY_NAME, 
ROUND(AVG(RATING / POSITION), 2) AS QUALITY, 
ROUND(SUM(RATING<3) / COUNT(QUERY_NAME) * 100, 2) AS POOR_QUERY_PERCENTAGE
FROM QUERIES
GROUP BY QUERY_NAME

AGGREGATE function(집계함수) 안에 조건을 넣을 수 있다!!! EX) SUM(RATING < 3)

[4]

https://leetcode.com/problems/average-selling-price/description/

Average Selling Price - LeetCode

# Write your MySQL query statement below
SELECT P.PRODUCT_ID, ROUND(SUM(P.PRICE*U.UNITS)/SUM(U.UNITS),2) AS AVERAGE_PRICE
FROM PRICES P
LEFT JOIN UNITSSOLD U
USING(PRODUCT_ID)
#조건을 더 상세하게 살필 필요가 있다!
WHERE U.PURCHASE_DATE >= P.START_DATE AND U.PURCHASE_DATE <= P.END_DATE
#WHERE u.purchase_date BETWEEN p.Start_date and p.end_date
GROUP BY P.PRODUCT_ID

날짜 값을 비교할 때 별도의 함수없이 비교 가능!

[5]

https://leetcode.com/problems/percentage-of-users-attended-a-contest/description/

Percentage of Users Attended a Contest - LeetCode

SELECT CONTEST_ID, ROUND((COUNT(DISTINCT(USER_ID)))/(SELECT COUNT(*) FROM USERS) * 100,2) AS PERCENTAGE
FROM REGISTER 
GROUP BY CONTEST_ID
ORDER BY 2 DESC, 1 ASC

(굳이 조인을 하지 않고) SELECT문 안에 SELECT문을 넣어서 풀 수 있다. 

그리고 ORDER BY에서 SELECT문에 들어갈 요소에 대한 힌트를 얻을 수 있었던 문제!

---

SORTING AND GROUPING

[1]

https://leetcode.com/problems/customers-who-bought-all-products/description/

Customers Who Bought All Products - LeetCode

SELECT customer_id 
FROM Customer 
GROUP BY customer_id
HAVING COUNT(distinct product_key) = (SELECT COUNT(product_key) FROM Product)

JOIN이 아니라 HAVING에 저렇게 조건을 넣을 수도 있구나..

---

ADVANCED SELECT AND JOINS

[1]

https://leetcode.com/problems/count-salary-categories/description/

Count Salary Categories - LeetCode

#UNION은 쿼리를 합쳐...
SELECT "Low Salary" AS CATEGORY, SUM(income < 20000) AS ACCOUNTS_COUNT
FROM ACCOUNTS
UNION
SELECT "Average Salary" AS CATEGORY, SUM(income >= 20000 and income <= 50000) AS ACCOUNTS_COUNT
FROM ACCOUNTS
UNION
SELECT "High Salary" AS CATEGORY, SUM(income > 50000) AS ACCOUNTS_COUNT
FROM ACCOUNTS

이걸 어떻게 합치지..? 싶으면 UNION을 떠올려

---

SUBQUERIES

[1]

https://leetcode.com/problems/movie-rating/description/

Movie Rating - LeetCode

#살짝 정신이 나갈 것 같지만 괜찮다
#UNION과 UNION ALL 구분!
(SELECT NAME AS "RESULTS" 
FROM MOVIERATING M
LEFT JOIN USERS U
USING(USER_ID)
GROUP BY NAME
ORDER BY COUNT(M.USER_ID) DESC, 1
LIMIT 1)
UNION ALL
(SELECT TITLE AS "RESULTS"
FROM MOVIERATING R, MOVIES M
WHERE R.MOVIE_ID = M.MOVIE_ID AND CREATED_AT LIKE '2020-02%'
GROUP BY R.MOVIE_ID
ORDER BY AVG(RATING) DESC, 1
LIMIT 1
)

UNION vs. UNION ALL

UNION 함수를 사용할 때 괄호를 치는 것을 잊지 말자!

UNION ALL은 중복되는 값이 가능하지만, UNION은 중복되지 않는 유일한 값만이 가능하다

---

ADVANCED STRING FUNCTIONS / REGEX / CLAUSE

[1]

https://leetcode.com/problems/second-highest-salary/?envType=study-plan-v2&envId=top-sql-50 

Second Highest Salary - LeetCode

#WHERE에 MAX 값을 제외하라는 조건을 넣는 방법
SELECT MAX(SALARY) AS SecondHighestSalary 
FROM EMPLOYEE 
WHERE SALARY <> (SELECT MAX(SALARY) FROM EMPLOYEE);

#LIMIT과 OFFSET을 활용하는 방법
SELECT(
  SELECT DISTINCT Salary 
  FROM Employee
  ORDER BY Salary DESC
  LIMIT 1 OFFSET 1
) AS SecondHighestSalary;

LIMIT

결과 중에서 처음부터 특정 갯수만 가져오기 / 가지고 올 데이터의 총 개수를 지정한다고 생각하면 됨!

SELECT * FROM 테이블명 LIMIT 10; -- 처음 부터 10개만 출력하기 (1 ~ 10)

SELECT * FROM 테이블명 LIMIT 100, 10; -- 100번째부터 그 후 10개 출력하기 (101 ~ 110)

OFFSET

어디서 부터 값을 가져올 지 지정하는 역할 / 시작하는 인덱스를 지정한다고 생각하면 됨!

SELECT * FROM 테이블명 ORDERS LIMIT 20 OFFSET 5; -- 5번째 행 부터 25행 까지 출력 (6 ~ 25)
-- limit 5, 20 과 같다고 보면 된다.
SELECT * FROM 테이블명 ORDERS LIMIT 5, 20

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-LIMIT-OFFSET

[MYSQL] 📚 LIMIT / OFFSET 쿼리

[2]

https://leetcode.com/problems/group-sold-products-by-the-date/description/

Group Sold Products By The Date - LeetCode

# Write your MySQL query statement below
SELECT SELL_DATE, COUNT(DISTINCT PRODUCT) AS NUM_SOLD, GROUP_CONCAT(DISTINCT PRODUCT ORDER BY PRODUCT ASC SEPARATOR ',') AS PRODUCTS
FROM ACTIVITIES 
GROUP BY SELL_DATE 
ORDER BY 1

GROUP_CONCAT

조건에 맞는 값들을 묶어서 출력하는 함수

SELECT '그룹 대상 컬럼', GROUP_CONCAT('CONCAT 대상 컬럼') FROM [테이블 명]
GROUP BY '그룹 대상 컬럼';

• '그룹 대상 컬럼' : GROUP BY 대상 컬럼 명
• 'Concat 대상 컬럼' : 그룹 대상 기준으로 CONCAT 할 컬럼

[3]

https://leetcode.com/problems/fix-names-in-a-table/description/

Fix Names in a Table - LeetCode

# Write your MySQL query statement below
SELECT USER_ID, CONCAT(UPPER(LEFT(NAME, 1)), LOWER(RIGHT(NAME, LENGTH(NAME)-1))) AS NAME
FROM USERS
ORDER BY 1

CONCAT

두 문자열을 합쳐서 출력하는 함수

CONCAT(string1, string2, ...., string_n)

 

UPPER

전부 대문자로 변경하여 출력하는 함수

UPPER(text)

LOWER

전부 소문자로 변경하여 출력하는 함수

LOWER(text)

LEFT

문자열의 왼쪽부터 일정수치까지의 문자열을 자르는 함수

LEFT(string, number_of_chars)

RIGHT

문자열의 오른쪽부터 일정수치까지의 문자열을 자르는 함수

RIGHT(string, number_of_chars)