응엉이의 엉엉코딩일기

https://leetcode.com/problems/primary-department-for-each-employee/?envType=study-plan-v2&envId=top-sql-50 -- department가 1개 (SELECT employee_id, department_id FROM employee WHERE employee_id IN ( SELECT employee_id FROM employee GROUP BY employee_id HAVING COUNT(DISTINCT department_id) = 1) ) UNION -- department가 2개 (SELECT employee_id, department_id FROM employee WHERE employee_id IN ( SELECT ..

https://leetcode.com/problems/immediate-food-delivery-ii/?envType=study-plan-v2&envId=top-sql-50 Immediate Food Delivery II - LeetCode Can you solve this real interview question? Immediate Food Delivery II - Table: Delivery +-----------------------------+---------+ | Column Name | Type | +-----------------------------+---------+ | delivery_id | int | | customer_id | int | | order_date | d leetco..

https://leetcode.com/problems/average-selling-price/submissions/1194313569/?envType=study-plan-v2&envId=top-sql-50 Average Selling Price - LeetCode Can you solve this real interview question? Average Selling Price - Table: Prices +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------..

https://leetcode.com/problems/monthly-transactions-i/submissions/1193358504/?envType=study-plan-v2&envId=top-sql-50 Monthly Transactions I - LeetCode Can you solve this real interview question? Monthly Transactions I - Table: Transactions +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_dat..

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..