#118. [LeetCode / MySQL] 1667. Fix Names in a Table

문제  입력 코드SELECT user_id, CONCAT(UPPER(LEFT(name, 1)), LCASE(SUBSTRING(name, 2))) AS nameFROM UsersORDER BY user_idSELECT user_id, CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) AS nameFROM UsersORDER BY user_idSELECT user_id, CONCAT(UPPER(SUBSTR(name, 1, 1)), LOWER(RIGHT(name, LENGTH(name)-1))) AS nameFROM UsersORDER BY user_id   코드 설명#SELECT #CONCAT #UPPER ..

• format_list_bulleted SQL
• · 2024. 6. 20.
• textsms

#117. [LeetCode / MySQL] 185. Department Top Three Salaries

문제 입력 코드1. SUBQUERYSELECT d.name AS Department, e.name AS Employee, e.salary AS SalaryFROM Employee AS eJOIN Department AS dON e.departmentId = d.idWHERE 3 > (SELECT COUNT(DISTINCT e2.salary) FROM Employee AS e2 WHERE e2.salary > e.salary AND e.departmentId = e2.departmentId)  2. WINDOW 함수 - DENSE_RANK()SELECT Department, Employee, SalaryFROM (SELECT d.name AS D..

• format_list_bulleted SQL
• · 2024. 6. 20.
• textsms

#116. [LeetCode / MySQL] 585. Investments in 2016

문제  입력 코드SELECT ROUND(SUM(TIV_2016),2) AS tiv_2016FROM insuranceWHERE tiv_2015 IN (SELECT tiv_2015 FROM insurance GROUP BY tiv_2015 HAVING COUNT(pid) > 1) AND (lat, lon) IN (SELECT lat, lon FROM insurance GROUP BY lat, lon HAVING COUNT(pid) = 1);  코드 설명#SELECT #ROUND #SUM #GROUP BY #HAVING #COUNT #IN  문제 출처https://leetcode.com/problems/investments-in-2..

• format_list_bulleted SQL
• · 2024. 6. 19.
• textsms

#115. [LeetCode / MySQL] 602. Friend Requests II: Who Has the Most Friends

문제  입력 코드WITH base AS (SELECT requester_id AS id FROM RequestAcceptedUNION ALLSELECT accepter_id AS id FROM RequestAccepted)SELECT id, COUNT(*) AS num FROM base GROUP BY 1 ORDER BY 2 DESCLIMIT 1  코드 설명#UNION #UNION ALL #SELECT #COUNT #GROUP BY #ORDER BY #DESC #LIMIT  문제 출처https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/description/?envType=study-plan-v2&envId=top-sql-50

• format_list_bulleted SQL
• · 2024. 6. 19.
• textsms

#114. [LeetCode / MySQL] 1321. Restaurant Growth

문제  입력 코드1. SELECT visited_on,SUM(SUM(amount)) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount,ROUND(AVG(SUM(amount)) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),2) AS average_amountFROM CustomerGROUP BY visited_onORDER BY visited_on LIMIT 999999 OFFSET 6;  2. SELECT visited_on, amount, ROUND(average_amount,2) AS average_amountFROM (SELECT visited_on, SUM(amount) OVER (ORDER B..

• format_list_bulleted SQL
• · 2024. 6. 18.
• textsms

#113. [LeetCode / MySQL] 1341. Movie Rating

문제  입력 코드1. (SELECT name AS resultsFROM MovieRating JOIN Users USING(user_id)GROUP BY nameORDER BY COUNT(*) DESC, nameLIMIT 1)UNION ALL(SELECT title AS resultsFROM MovieRating JOIN Movies USING(movie_id)WHERE EXTRACT(YEAR_MONTH FROM created_at) = 202002GROUP BY titleORDER BY AVG(rating) DESC, titleLIMIT 1);  2. WITH TheMostActiveUser AS ( SELECT name FROM Users NATURAL JOIN ..

• format_list_bulleted SQL
• · 2024. 6. 18.
• textsms