LeetCode SQL Problems ②

Medium

180. Consecutive Numbers

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id is the primary key for this table.

Write an SQL query to find all numbers that appear at least three times consecutively.

Return the result table in any order.

The query result format is in the following example:

Logs table:
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Result table:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 is the only number that appears consecutively for at least three times.

Solution

SELECT DISTINCT l1.num as ConsecutiveNums 
FROM logs l1 
JOIN logs l2 ON l2.id = l1.id+1 
JOIN logs l3 ON l3.id = l1.id+2 
WHERE l1.num = l2.num AND l2.num = l3.num

184. Department Highest Salary

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Solution

SELECT Department, Employee, Salary 
FROM (SELECT d.name Department, e.name Employee, e.salary Esalary, MAX(e.salary) OVER (PARTITION BY d.id) Salary 
FROM employee e 
JOIN department d ON d.id = e.departmentid) c 
WHERE Esalary = Salary

177. Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Solution

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT 
BEGIN   
RETURN (       
# Write your MySQL query statement below.       
SELECT DISTINCT IF(rnk IS NULL, NULL, salary)        
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk       FROM employee) sub       
WHERE rnk = N   
); 
END