응엉이의 엉엉코딩일기

https://leetcode.com/problems/students-and-examinations/?envType=study-plan-v2&envId=top-sql-50 Students and Examinations - LeetCode Can you solve this real interview question? Students and Examinations - Table: Students +---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ student_id is leetcode..

https://leetcode.com/problems/confirmation-rate/?envType=study-plan-v2&envId=top-sql-50 Confirmation Rate - LeetCode Can you solve this real interview question? Confirmation Rate - Table: Signups +----------------+----------+ | Column Name | Type | +----------------+----------+ | user_id | int | | time_stamp | datetime | +----------------+----------+ user_id is the colum leetcode.com 처음에는 JOIN을 ..

https://leetcode.com/problems/average-time-of-process-per-machine/?envType=study-plan-v2&envId=top-sql-50 Average Time of Process per Machine - LeetCode Can you solve this real interview question? Average Time of Process per Machine - Table: Activity +----------------+---------+ | Column Name | Type | +----------------+---------+ | machine_id | int | | process_id | int | | activity_type | enum |..

https://leetcode.com/problems/customers-who-bought-all-products/description/?envType=study-plan-v2&envId=top-sql-50 Customers Who Bought All Products - LeetCode Can you solve this real interview question? Customers Who Bought All Products - Table: Customer +-------------+---------+ | Column Name | Type | +-------------+---------+ | customer_id | int | | product_key | int | +-------------+-------..

https://leetcode.com/problems/product-sales-analysis-iii/description/?envType=study-plan-v2&envId=top-sql-50 Product Sales Analysis III - LeetCode Can you solve this real interview question? Product Sales Analysis III - Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com SELECT s.product_id, MI..

https://leetcode.com/problems/rising-temperature/description/?envType=study-plan-v2&envId=top-sql-50 recordDate를 기준으로 JOIN (a가 previous day가 되도록) SELECT b.Id FROM weather a INNER JOIN weather b ON a.recordDate = b.recordDate - 1 -- a : yesterday WHERE a.temperature < b.temperature LEFT JOIN이 아닌 INNER JOIN을 써줘야 연속된 날짜를 구할 수 있다. DATE 계산을 조금 더 깔끔하게 적으면 이렇게 적을 수도 있다 ! SELECT w2.id FROM Weather w1 JO..

하루에 5문제 이상씩 풀 예정 # 1757. Recyclable and Low Fat Products https://leetcode.com/problems/recyclable-and-low-fat-products/?envType=study-plan-v2&envId=top-sql-50 Recyclable and Low Fat Products - LeetCode Can you solve this real interview question? Recyclable and Low Fat Products - Table: Products +-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | low_..

https://leetcode.com/problems/department-highest-salary/ LeetCode - The World's Leading Online Programming Learning Platform Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com SELECT d.name AS department, e.name AS Employee, e.salary AS Salary FROM employee e LEFT JOIN department d ON e.depart..

https://www.hackerrank.com/challenges/earnings-of-employees/problem?h_r=internal-search&isFullScreen=true 1. WHERE절 서브쿼리 이용 earnings의 가장 큰 값을 구한 후 WHERE절을 이용해서 그 값을 가진 사람 수를 필터링 SELECT salary * months AS earnings, COUNT(*) FROM employee WHERE salary * months = ( SELECT MAX(salary*months) FROM employee ) -- WHERE 절에서 서브쿼리를 쓰고싶을때는 AS 로 불러올수가없다 ... GROUP BY earnings 2. HAVING절 서브쿼리 이용 1번과 차이는 GROUP..

https://leetcode.com/problems/delete-duplicate-emails/ LeetCode - The World's Leading Online Programming Learning Platform Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 중복된 이메일을 삭제하는 문제이다. 테이블에서 어떤 column을 지우기 위해서는 DELETE 문을 사용해야 한다. 1. 서브쿼리를 활용해서 지우지 말아야하는 id와 이메일을 뽑고 2. 해당 서브쿼리의 id를 가져와..