LeetCode Database

https://leetcode.com/problems/find-median-given-frequency-of-numbers/#/description

The Numbers table keeps the value of number and its frequency.

+----------+-------------+
|  Number  |  Frequency  |
+----------+-------------|
|  0       |  7          |
|  1       |  1          |
|  2       |  3          |
|  3       |  1          |
+----------+-------------+

In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0) / 2 = 0.

Write a query to find the median of all numbers and name the result as median.

https://discuss.leetcode.com/topic/167/managers-with-at-least-5-direct-reports

Given an employee table, find all managers with at least 5 direct reports.

Id	Name	Department	ManagerId
101	John	A	null
102	Dan	A	101
103	James	A	101
104	Amy	A	101
105	Anne	A	101
106	Ron	B	101

---

For the above table, your SQL query should return "John".

SELECT Name FROM Employee AS e INNER JOIN (SELECT ManagerId as m, COUNT(id) as c FROM Employee GROUP BY ManagerId HAVING c>=5) as e2
ON e.id = e2.m

https://leetcode.com/problems/delete-duplicate-emails/

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

http://dartmooryao.blogspot.com/2016/06/leetcode-196-delete-duplicate-emails.html
Idea 1:
Select the record with min value
and delete other records (Use "not in")

delete from Person
where Id not in
(select Id from
 (select min(Id) as Id from Person group by Email) p );

Idea 2:
Join the same table
Delete the record from one of the table that have larger Id and the same email

delete p1 from
Person p1, Person p2
where p1.Email = p2.Email
and p1.Id > p2.Id
https://leetcode.com/problems/rising-temperature/

Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

http://dartmooryao.blogspot.com/2016/06/leetcode-197-rising-temperature.html

Join the same table, and use "DateDiff" to calculate the two dates.
Note that "DateDiff" return positive value when the first date is larger, otherwise negative number.

select distinct w1.Id
from Weather w1, Weather w2
where DateDiff(w1.Date, w2.Date) = 1
and w1.Temperature > w2.Temperature

https://leetcode.com/problems/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 n^th highest salary where n = 2 is 200. If there is no n^th highest salary, then the query should return null.

http://dartmooryao.blogspot.com/2016/06/leetcode-177-nth-highest-salary.html

(1) Use Offset again
(2) Since offset number is N-1, and offset value cannot be a math function (such as n-1), we have to minus n before we use in the offset value

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee
      order by Salary desc
      limit 1 offset N
  );
END

https://leetcode.com/problems/second-highest-salary/

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

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

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

http://dartmooryao.blogspot.com/2016/06/leetcode-176-second-highest-salary.html
Better solution: use "offset"!!
(1) The column name must be "SecondHighestSalary"
(2) It's required to return "null" when it's empty, so we union it with null

select distinct Salary as SecondHighestSalary from Employee
union select null as SecondHighestSalary
order by SecondHighestSalary desc
limit 1 offset 1

https://www.hackerrank.com/challenges/weather-observation-station-20

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to  decimal places.

Note: Oracle solutions are not permitted for this challenge.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

http://dartmooryao.blogspot.com/2016/06/hakerrank-sql-weather-observation.html
calculate the index of two possible median

set @mid1 := (select round((count(*)+1)/2) from station);
set @mid2 := (select round(count(*)/2) from station);
set @rowNo = 0;

select round(avg(lat_n),4) from
(select @rowNo:=@rowNo+1 as rowNo, lat_n from station order by lat_n) t
where rowNo = @mid1 or rowNo = @mid2;


==============================
The idea here is to count the number of larger and smaller than the target, and use it in the where condition!!

select round(lat_n,4) from station s
where (select count(lat_n) from station where lat_n>s.lat_n)
= (select count(lat_n) from station where lat_n<s.lat_n);