Here are my solutions to all SQL problems on leetcode.
-- how to do rank with out window function
select t1.category, count(distinct t2.id) as dense_rank
from table t1, table t2
where t1.id = t2.id and t1.value <= t2.value
group by t1.category
order by t1.category, dense_rank
select t1.category, count(t2.id) as rank
from table t1, table t2
where t1.id = t2.id and t1.value <= t2.value
group by t1.category
order by t1.category, rank
- self join
- DATEDIFF(a,b) is a - b
select a.Id from Weather a
join Weather b
on DATEDIFF(a.Date, b.Date) = 1
where a.Temperature > b.Temperature
- LIMIT 10 OFFSET 10 will show 11-20
- IFNULL, return an alternative value if an expression is NULL
- IFNULL(value, alternative)
SELECT IFNULL(
(
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL
) AS SecondHighestSalary
- LIMIT 10 OFFSET 10 will show 11-20
- IFNULL, return an alternative value if an expression is NULL
- IFNULL(value, alternative)
SELECT IFNULL(
(
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL
) AS SecondHighestSalary
- self join
select a.Name Employee from Employee a
inner join Employee b
on a.ManagerId = b.Id # b is the manager table
where a.salary > b.salary
- UPDATE
- CASE
update salary
set
sex = case sex
when 'm' then 'f'
else 'm'
end;
- top from each category
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
- Rank
SELECT
Score,
(SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s
ORDER BY Score desc
- Get duplicate
- Group by, Having
select Email
from Person
group by Email
having count(Email) > 1
- delete
- keeping only unique emails based on its smallest Id
delete p1 from Person p1, Person p2
where p1.Email = p2.Email
and p1.Id > p2.Id
- Top N
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select IFNULL((select a.Salary from Employee a,Employee b
where a.Salary <= b.Salary
group by a.Salary
having count(distinct b.Salary) = N),NULL)
);
END
- wildcard
- % anything afterwards
- _ on char
- case when
# unbanned
# Oct 1, 2013 and Oct 3, 2013
select
t.Request_at Day,
round(sum(case when t.Status like 'cancelled_%' then 1 else 0 end)/count(*),2) Rate
from Trips t
inner join Users u
on t.Client_Id = u.Users_Id and u.Banned='No'
where t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at
- Top 1 in each category
select a.Name Department, b.Name Employee, b. Salary from Department a
inner join Employee b
on a.Id = b.DepartmentId
where (b.DepartmentId, b.Salary) in
(select DepartmentId, max(Salary)
from Employee
group by DepartmentId)
select a.Name Customers from Customers a
where a.Id not in
(select distinct CustomerId from Orders)
- case when
- mod
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND counts = id THEN id
ELSE id - 1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;
- mod
select * from cinema
where description not like '%boring%'
and MOD(id,2) = 1
order by rating desc
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id
;
- group by having
select class
from courses
group by class
having count(distinct student) >= 5
- avg
- abs
select avg(n.Number) median
from Numbers n
where n.Frequency
>= abs((select sum(Frequency) from Numbers where Number<=n.Number) -
(select sum(Frequency) from Numbers where Number>=n.Number))
select min(shortest) shortest from
(select abs(a.x-b.x) shortest from point a, point b
where a.x != b.x) a
- case when
select id, (
case
when p_id is NULL then root
when id in p_id then inner
else leaf
) Type
from tree
-ifnull
select
round(
ifnull(
(select count(*) from (select distinct requester_id, accepter_id from request_accepted) as A)
/
(select count(*) from (select distinct sender_id, send_to_id from friend_request) as B),
0)
, 2) as accept_rate;
- pivot
SELECT
America, Asia, Europe
FROM
(SELECT @as:=0, @am:=0, @eu:=0) t,
(SELECT
@as:=@as + 1 AS asid, name AS Asia
FROM
student
WHERE
continent = 'Asia'
ORDER BY Asia) AS t1
RIGHT JOIN
(SELECT
@am:=@am + 1 AS amid, name AS America
FROM
student
WHERE
continent = 'America'
ORDER BY America) AS t2 ON asid = amid
LEFT JOIN
(SELECT
@eu:=@eu + 1 AS euid, name AS Europe
FROM
student
WHERE
continent = 'Europe'
ORDER BY Europe) AS t3 ON amid = euid
;
- IFNULL
select a.dept_name, IFNULL(b.student_number,0) student_number
from department a
left join (select dept_id, count(distinct student_id) student_number
from student
group by dept_id) b
on a.dept_id = b.dept_id
order by b.student_number desc
or
SELECT
dept_name, COUNT(student_id) AS student_number
FROM
department
LEFT OUTER JOIN
student ON department.dept_id = student.dept_id
GROUP BY department.dept_name
ORDER BY student_number DESC , department.dept_name
;
- case when
- sign
- abs
SELECT
a.Id, a.Company, a.Salary
FROM
a,
Employee b
WHERE
a.Company = b.Company
GROUP BY a.Company , a.Salary
HAVING SUM(CASE
WHEN a.Salary = b.Salary THEN 1
ELSE 0
END) >= ABS(SUM(SIGN(a.Salary - b.Salary)))
ORDER BY a.Id
;
SELECT
customer_number
FROM
orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1
select a.follower, count(distinct b.follower) num
from follow a, follow b
where a.follower = b.followee
group by a.follower
order by a.follower
SELECT
name AS 'Name'
FROM
Candidate
JOIN
(SELECT
Candidateid
FROM
Vote
GROUP BY Candidateid
ORDER BY COUNT(*) DESC
LIMIT 1) AS winner
WHERE
Candidate.id = winner.Candidateid
- sqrt
- pow
- distance
select round(sqrt(min(pow(a.x-b.x,2)+pow(a.y-b.y,2))),2) shortest
from point_2d a, point_2d b
where (a.x,a.y)!=(b.x,b.y)
select sum(a.TIV_2016) TIV_2016
from insurance a
where
(a.LAT,a.LON) not in
(select LAT, LON from insurance
where insurance.PID != a.PID)
and a.TIV_2015 in
(select distinct TIV_2015 from insurance
where insurance.PID != a.PID)
SELECT
s.name
FROM
salesperson s
WHERE
s.sales_id NOT IN (SELECT
o.sales_id
FROM
orders o
LEFT JOIN
company c ON o.com_id = c.com_id
WHERE
c.name = 'RED')
select *,
(
case
when ((x+y)>z) and ((x+z)>y) and ((z+y)>x) then 'Yes'
else 'No'
end
) as 'triangle'
from triangle
select Name from Employee
where Id in (select a.ManagerId from Employee a
group by a.ManagerId
having count(distinct a.Id) >= 5)
select max(a.num) num from
(select a.num from number a, number b
where a.num = b.num
group by a.num
having count(b.num) = 1) a
SELECT
E1.id,
E1.month,
(IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary
FROM
(SELECT
id, MAX(month) AS month
FROM
Employee
GROUP BY id
HAVING COUNT(*) > 1) AS maxmonth
LEFT JOIN
Employee E1 ON (maxmonth.id = E1.id
AND maxmonth.month > E1.month)
LEFT JOIN
Employee E2 ON (E2.id = E1.id
AND E2.month = E1.month - 1)
LEFT JOIN
Employee E3 ON (E3.id = E1.id
AND E3.month = E1.month - 2)
ORDER BY id ASC , month DESC
;
- count if
select a.question_id survey_log from
(select question_id, count(*) anwser
from survey_log
where action = 'answer'
group by question_id) a,
(select count(*) total from survey_log where action = 'show') b
order by a.anwser/b.total desc
limit 1
SELECT
question_id AS 'survey_log'
FROM
survey_log
GROUP BY question_id
ORDER BY COUNT(answer_id) / COUNT(IF(action = 'show', 1, 0)) DESC
LIMIT 1;
select a.pay_month, a.department_id, (case
when a.salary = b.salary then 'same'
when a.salary > b.salary then 'higher'
else 'lower'
end) comparison from
(select a.department_id,date_format(pay_date, '%Y-%m') as pay_month, avg(amount) salary from employee a
inner join salary b
on a.employee_id = b.employee_id
group by a.department_id, pay_month) a,
(select date_format(pay_date, '%Y-%m') as pay_month, avg(amount) salary from salary
group by pay_month ) b
where a.pay_month = b.pay_month
select distinct a.seat_id from cinema a, cinema b
where abs(a.seat_id - b.seat_id) = 1
and a.free = 1
and b.free = 1
order by a.seat_id
- union
select id, sum(num) num from
((select accepter_id id, count( requester_id) num from request_accepted
group by accepter_id)
union all
(select requester_id id, count( accepter_id) num from request_accepted
group by requester_id)) x
group by id
order by sum(num) desc
limit 1
select name from customer
where referee_id != 2 or referee_id is null