구름 아카이브

*** 다시 풀어볼 것 184. Department Highest Salary https://leetcode.com/problems/Department-Highest-Salary/ # 각각의 부서에서 누가 가장 고소득자인지 # sel 부서, 직원, 급여 # 순서 상관 x SELECT d.name AS Department , e.name AS Employee , e.salary AS Salary FROM employee as e INNER JOIN ( SELECT departmentID, MAX(salary) AS max_salary FROM Employee GROUP BY departmentID ) AS dh ON e.departmentID = dh.departmentID AND e.salary = dh..

Consecutive Numbers https://leetcode.com/problems/consecutive-numbers/ # 최소 3번이상 연속적으로 나타나는 숫자 찾기 # 순서는 상관없음 SELECT DISTINCT l.num AS ConsecutiveNums -- 중복제거 FROM logs AS l INNER JOIN logs AS l_next ON l.id + 1 = l_next.id INNER JOIN logs AS l_next2 ON l.id + 2 = l_next2.id # three times consecutively WHERE l.num = l_next.num AND l_next.num = l_next2.num

Reformat Department Table https://leetcode.com/problems/reformat-department-table/ SELECT id, SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS JAN_Revenue, SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS Feb_Revenue, SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS Mar_Revenue, SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS Apr_Revenue, SUM(CASE WHEN month='May..

Customers Who Never Order https://leetcode.com/problems/Customers-Who-Never-Order/ SELECT name AS Customers FROM Customers AS c LEFT JOIN Orders AS o ON o.customerId = c.id WHERE o.id is null

Employees Earning More Than Their Managers https://leetcode.com/problems/Employees-Earning-More-Than-Their-Managers/ SELECT employee.name AS Employee FROM employee INNER JOIN employee as Mannager ON employee.managerID = Mannager.id WHERE employee.salary > Mannager.salary

Rising Temperature https://leetcode.com/problems/Rising-Temperature/ SELECT today.id FROM weather AS today INNER JOIN weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate WHERE today.temperature > yesterday.temperature # # {"headers": ["id", "recordDate", "temperature", "id", "recordDate", "temperature"], # # "values": # # [[2, "2015-01-02", 25, 1, "2015-01-0..

Big Countries https://leetcode.com/problems/Big-Countries/ SELECT name, population, area FROM WORLD WHERE area >=3000000 OR population >=25000000

Not Boring Movies https://leetcode.com/problems/Not-Boring-Movies/ SELECT id, movie, description, rating FROM Cinema WHERE MOD(id,2)= 1 AND description != 'boring' ORDER BY rating DESC

Duplicate Emails https://leetcode.com/problems/Duplicate-Emails/ SELECT email FROM Person GROUP BY Person.email HAVING COUNT(email) > 1

Combine Two Tables https://leetcode.com/problems/Combine-Two-Tables/ SELECT firstName, lastName, city, state FROM Person LEFT JOIN Address ON Person.personId =Address.personId # {"headers": # ["firstName", "lastName", "city", "state"], # "values": # [["Allen", "Wang", null, null], # ["Bob", "Alice", "New York City", "New York"]]}