#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

#112. [LeetCode / MySQL] 626. Exchange Seats

문제  입력 코드1. SELECT CASE WHEN id = (SELECT MAX(id) FROM Seat) AND (id % 2 = 1) THEN id WHEN id % 2 = 1 THEN id + 1 ELSE id - 1 END AS id, studentFROM SeatORDER BY id;  2. SELECT S.id, IFNULL( IF(S.id % 2 = 0, (SELECT student FROM Seat WHERE id = S.id - 1), (SELECT student FROM Seat WHERE id = S.id + 1) ..

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

#111. [LeetCode / MySQL] 1978. Employees Whose Manager Left the Company

문제  입력 코드1. SUBQUERYSELECT employee_id FROM EmployeesWHERE salary   2. LEFT JOINSELECT e1.employee_id FROM Employees AS e1LEFT JOIN Employees AS e2ON e1.manager_id = e2.employee_idWHERE e1.salary   코드 설명#SELECT #WHERE #NOT IN #ORDER BY #LEFT JOIN # JOIN #NULL #AS #ON  문제 출처https://leetcode.com/problems/employees-whose-manager-left-the-company/description/?envType=study-plan-v2&envId=top-sql-50

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