본문 바로가기

SQL/LeetCode

 (16)
[SQL] LeetCode: 184. Department Highest Salary (윈도우 함수) 184. Department Highest Salary 문제 Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in any order. 풀이1) 윈도우 함수 MAX( ) + FROM절 서브쿼리 사용 SELECT department , employee , salary FROM( SELECT d.name AS department , e.name AS employee , e.salary , MAX(salary) OVER (PARTITION BY e.departmentid) AS max_salary FROM employee e INNER JOIN depa..
[SQL] LeetCode: 180. Consecutive Numbers 180. Consecutive Numbers 문제 Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order. 풀이1) JOIN 사용 SELECT DISTINCT a.num AS ConsecutiveNums FROM logs a LEFT JOIN logs b ON b.id = ( a.id + 1) LEFT JOIN logs c ON c.id = ( a.id + 2) WHERE a.num = b.num AND b.num= c.num 풀이2) 윈도우 함수 - LAG함수 사용 + FORM절 서브쿼리 SELECT DISTINCT num AS Conse..
[SQL] LeetCode: 184. Department Highest Salary 184. Department Highest Salary 문제 Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in any order. # 부서별로 가장 높은 연봉을 받는 직원 출력 똑같은 연봉을 받는 직원이 여러명이면 모두 출력해야 함. 나의 풀이 : JOIN 후 WHERE절 서브쿼리 이용 SELECT d.name AS Department ,e.name AS Employee ,e.salary AS Salary FROM employee e INNER JOIN department d ON e.departmentid = d.id WHERE (e.dep..
[SQL] LeetCode: 196. Delete Duplicate Emails 196. Delete Duplicate Emails 문제 Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one. After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Perso..
[SQL] LeetCode: 627. Swap Salary 627. Swap Salary 문제 Write an SQL query to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables. Note that you must write a single update statement, do not write any select statement for this problem. 풀이 UPDATE salary SET sex = IF(sex = 'f', 'm','f') +) 다른 풀이 UPDATE salary SET sex = CASE WHEN sex = 'f' ..
[SQL] LeetCode: SELF JOIN 문제 181. Employees Earning More Than Their Managers 문제 Write an SQL query to find the employees who earn more than their managers. Return the result table in any order. 풀이 SELECT a.name AS Employee FROM employee AS a LEFT JOIN employee AS b ON a.managerid = b.id WHERE a.salary > b.salary 197. Rising Temperature 문제 Write an SQL query to find all dates' Id with higher temperatures compared to its prev..
[SQL] LeetCode : LEFT JOIN 문제 183. Customers Who Never Order 문제 Write an SQL query to report all customers who never order anything. Return the result table in any order. 풀이 SELECT customers.name AS Customers FROM customers LEFT JOIN orders ON customers.id = orders.customerid WHERE orders.id IS NULL
[SQL] LeetCode : 1193. Monthly Transactions I Monthly Transactions I - LeetCode 문제 Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount. Return the result table in any order. 풀이 SELECT SUBSTR(trans_date,1,7) AS month ,country ,COUNT(trans_date) AS trans_count ,COUNT(IF(state='approved',1,NULL)) AS approved_count ,SUM(amount) AS tr..

티스토리툴바