SQL/LeetCode (16) 썸네일형 리스트형 [SQL] LeetCode: 1164. Product Price at a Given Date Product Price at a Given Date - LeetCode 주어진 컬럼은 product_id, new_price, chage_date (제품의 가격의 변화가 기록된 테이블) 이걸 가지고 2019-08-16 이라는 특정 날짜의 제품별 가격을 출력하는 문제이다. 뭔가 간단한 거 같은데 해결하는 데 꽤 오랜 시간이 걸렸다. - 가격이 변화한 경우에만 데이터가 존재 - 가격 변화 전 모든 제품의 가격은 10 [풀이] -- 1. 출력: product_id, price -- 2. 필터: 2019-08-16의 가격 -- 3. 정렬: X WITH sub AS( SELECT product_id AS pid ,MAX(change_date) AS last_change_date FROM products WHER.. [SQL] LeetCode: 180. Consecutive Numbers 180. Consecutive Numbers 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 -- 1. 출력: 3번 이상 연속으로 나오는 숫자 -- 2. 필터: 3번 이상 연속 -- 3. 정렬: X SELECT DISTINCT num AS ConsecutiveNums FROM( SELECT * ,LAG(num,1) OVER (ORDER BY id) AS lag.. [SQL] LeetCode: 610. Triangle Judgement 610. Triangle Judgement 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 -- 1.출력: x,y,z, 삼각형 만들 수 있는지 여부 -- 2.필터: X -- 3.정렬: X SELECT * ,CASE WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes' ELSE 'No' END AS 'triangle' .. [SQL] LeetCode : 184. Department Highest Salary 184. 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 -- 1. 출력: department name, employee name, salary -- 2. 필터: the highest salary in each of the departments -- 3. 정렬: X WITH max AS( SELECT e.name .. [SQL] LeetCode: 1789. Primary Department for Each Employee 1789. Primary Department for Each Employee 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 [풀이1] - 처음 풀었던 풀이; Window 함수 이용해서 풀이함 -- 1. 출력: employee_id, department_id -- 2. 필터: 직원별 primary department, 한 부서 소속은 그 department 출.. [SQL] LeetCode: 1731. The Number of Employees Which Report to Each Employee 1731. The Number of Employees Which Report to Each Employee 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 [문제] For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them. .. [SQL] LeetCode: 177. Nth Highest Salary 177. Nth Highest Salary # 사용자 정의 함수 문제 문제 Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null. 풀이1) CASE문 + FROM절 서브쿼리 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( SELECT CASE WHEN COUNT(sub.salary) < N THEN NULL ELSE MIN(sub.salary) END FROM ( SELECT DISTINCT salary FROM employee OR.. [SQL] LeetCode: 185. Department Top Three Salaries 185. Department Top Three Salaries 문제 A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department. Write an SQL query to find the employees who are high earners in each of the departments. Return the result table in any order. 풀이 # .. 이전 1 2 다음