본문 바로가기

SQL18

[LeetCode] Trips and Users (date between) # Write your MySQL query statement below select Trips.Request_at as Day, round(sum(if(status != 'completed',1,0)) / count(*), 2) as 'Cancellation Rate' from Trips, Users where Trips.Client_Id = Users.Users_Id and Users.Banned = "No" and Trips.Request_at BETWEEN '2013-10-01' and '2013-10-03' group by Trips.Request_at 출처: tanwirkhan.medium.com/leet-code-262-trips-and-users-5899bf04ec0e 2021. 4. 10.
[LeetCode] Department Top Three Salaries (partition by, rank, sub query) select b.Name as Department , a.Name as Employee, a.Salary from (select *, dense_rank() over (partition by DepartmentId order by salary desc) as sal_rank from Employee ) a join Department b on a.DepartmentId = b.Id where a.sal_rank 2021. 4. 10.
[LeetCode] Department Highest Salary ( sub query, partition by) # Write your MySQL query statement below select a.Department, a.Employee, a.Salary from (select D.Name as Department, E.Name as Employee, E.Salary, rank() over (partition by D.Name order by Salary desc) as 'rank' from Employee E join Department D on E.DepartmentId = D.Id) as a where a.rank = 1; 서브쿼리부터 보자, from employee 에서 join 으로 employee and deparment을 수행 select 수행 select에서 rank를 사용하는데 여기서 part.. 2021. 4. 10.
SQL 실행 순서와 문법 순서 출처: police84.tistory.com/69 2021. 4. 10.
[LeetCode] Consecutive Numbers (self join twice) # Write your MySQL query statement below SELECT DISTINCT l1.Num as ConsecutiveNums FROM Logs l1 JOIN Logs l2 ON l1.Id = l2.Id - 1 JOIN Logs l3 ON l2.Id = l3.Id - 1 WHERE l1.Num = l2.Num AND l2.Num = l3.Num ; self join 을 2번한다. 총 3개의 table이 join한다 결과 {"headers": ["Id", "Num", "Id", "Num", "Id", "Num"], "values": [[1, 1, 2, 1, 3, 1], [2, 1, 3, 1, 4, 2], [3, 1, 4, 2, 5, 1], [4, 2, 5, 1, 6, 2], [5, 1.. 2021. 4. 10.
[LeetCode] Rank Scores (rank, dense rank, row number) SELECT Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS 'Rank' FROM Scores RANK 중복 값에 대해서 동일한 순위 그리고 중복 값 다음 값에 대해서 중복순위 + 중복값 개수 의 순위를 출력합니다. DENSE_RANK 중복 값에 대해서 동일한 순위 그리고 중복 값 다음 값에 대해서 중복순위 + 1 의 순위를 출력합니다. ROW_NUMBER 중복 값에 대해서 순차적인 순위 그리고 중복 값 다음 값에 대해서 또한 순차적인 순위를 출력합니다. 출처: https://doorbw.tistory.com/221 [Tigercow.Door] 출처: doorbw.tistory.com/221 2021. 4. 10.
[LeetCode] Nth Highest Salary (set, limit) CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN SET N = N - 1; RETURN ( SELECT DISTINCT Salary -- 장점: 서브쿼리를 안써도됨, IF문or CASE문 작성 필요없음. Aggregation function 작성 필요없어짐. FROM employee ORDER BY Salary DESC LIMIT N, 1 -- N-1번째까지는 지우고 그 N번째를 가져오라는 의미 ); END Limit 구문에 대해 정리하기 SELECT 열명 FROM 테이블명 LIMIT 시작행, 개수 예제 코드 실행 및 결과 # {"headers": ["Id", "Salary"], "values": [[3, 300]]} # {"headers": .. 2021. 4. 9.
[LeetCode] Reformat Department Table (sum, case, when, then) SELECT id , SUM(CASE WHEN month = "Jan" THEN revenue END) AS Jan_Revenue , SUM(CASE WHEN month = "Feb" THEN revenue END) AS Feb_Revenue , SUM(CASE WHEN month = "Mar" THEN revenue END) AS Mar_Revenue , SUM(CASE WHEN month = "Apr" THEN revenue END) AS Apr_Revenue , SUM(CASE WHEN month = "May" THEN revenue END) AS May_Revenue , SUM(CASE WHEN month = "Jun" THEN revenue END) AS Jun_Revenue , SUM(CASE.. 2021. 4. 9.
[LeetCode] Swap Salary (update, ENUM, SET, CASE, IF) ENUM data type link ENUM(val1, val2, val3, ...) A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them UPDATE salary SET sex = IF(sex='m', 'f', 'm'); UPDATE salary SET sex = CASE sex WHEN '.. 2021. 4. 9.

티스토리툴바