ajinkyasirsikar/leetcode_sql.md

## leetcode_sql.md

      
    Raw
  

              leetcode_sql.md
            
          
    Here are my solutions to all SQL problems on leetcode.
Rank

-- 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

197. Rising Temperature


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
176. Second Highest Salary


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
176. Second Highest Salary


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
181. Employees Earning More Than Their Managers


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
627. Swap Salary


UPDATE
CASE

update salary

set 
    sex = case sex
        when 'm' then 'f'
        else 'm'
    end;
185. Department Top Three Salaries


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
        )
;
178. Rank Scores


Rank

SELECT
  Score,
  (SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s
ORDER BY Score desc
182. Duplicate Emails


Get duplicate
Group by, Having

select Email
from Person
group by Email
having count(Email) > 1
196. Delete Duplicate Emails


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
177. Nth Highest Salary


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
262. Trips and Users


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

184. Department Highest Salary


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)
183. Customers Who Never Order

select a.Name Customers from Customers a
where a.Id not in
(select distinct CustomerId from Orders)

626. Exchange Seats


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;

620. Not Boring Movies


mod

select * from cinema
where description not like '%boring%'
and MOD(id,2) = 1
order by rating desc
601. Human Traffic of Stadium

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
;
596. Classes More Than 5 Students


group by having

select class
from courses
group by class
having count(distinct student) >= 5

571. Find Median Given Frequency of Numbers


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))

613. Shortest Distance in a Line

select min(shortest) shortest from
(select abs(a.x-b.x) shortest from point a, point b
where a.x != b.x) a
608. Tree Node


case when

select id, (
    case 
        when p_id is NULL then root
        when id in p_id then inner
        else leaf
    ) Type
from tree
597. Friend Requests I: Overall Acceptance Rate

-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;

618. Students Report By Geography


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
;
580. Count Student Number in Departments


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
;
569. Median Employee Salary


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
;
586. Customer Placing the Largest Number of Orders

SELECT
    customer_number
FROM
    orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1

614. Second Degree Follower

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
574. Winning Candidate

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
612. Shortest Distance in a Plane


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)
585. Investments in 2016

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)
607. Sales Person

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')

610. Triangle Judgement

select *,
    (
    case
        when ((x+y)>z) and ((x+z)>y) and ((z+y)>x) then 'Yes'
        else 'No'
    end
    ) as 'triangle'
    from triangle
570. Managers with at Least 5 Direct Reports

select Name from Employee
where Id in (select a.ManagerId from Employee a
group by a.ManagerId
having count(distinct a.Id) >= 5)
619. Biggest Single Number

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

579. Find Cumulative Salary of an Employee

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
;
578. Get Highest Answer Rate Question


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;
615. Average Salary: Departments VS Company

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
603. Consecutive Available Seats

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
602. Friend Requests II: Who Has the Most Friends


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
584. Find Customer Referee

select name from customer
where referee_id != 2 or referee_id is null