1. Shortest Distance in a Line
https://leetcode.com/articles/shortest-distance-in-a-line/
Table point holds the x coordinate of some points on x-axis in a plane, which are all integers.
Write a query to find the shortest distance between two points in these points.
| x |
|-----|
| -1 |
| 0 |
| 2 |
The shortest distance is '1' obviously, which is from point '-1' to '0'. So the output is as below:
| shortest|
|---------|
| 1 |
Note: Every point is unique, which means there is no duplicates in table point.
Follow-up: What if all these points have an id and are arranged from the left most to the right most of x axis?
2. Find Customer Referee
Given a table customer holding customers information and the referee.
Write a query to return the list of customers NOT referred by the person with id '2'.
For the sample data above, the result is:
一开始想用 SELECT name FROM customer where referee_id !=2 但这无法选择NULL的行， 所以还要加上条件 referee_id is NULL。
3. Customer Placing the Largest Number of Orders
Query the customer_number from the orders table for the customer who has placed the largest number of orders.
It is guaranteed that exactly one customer will have placed more orders than any other customer.
The orders table is defined as follows:
Results:
如果结果只有一个可以用 SELECT customer_number FROM orders GROUP BY customer_number ORDER BY COUNT(*) DESC LIMIT1；
但如果有多个结果的话， 可以用 select customer_number from customer_number
group by customer_number having count(customer_number) >= all
(select count(customer_number) from customer_number group by customer_number)
all 和any函数是用来返回大于的result
4. Triangle Judgement
A pupil Tim gets homework to identify whether three line segments could possibly form a triangle.However, this assignment is very heavy because there are hundreds of records to calculate. Could you help Tim by writing a query to judge whether these three sides can form a triangle, assuming table triangle holds the length of the three sides x, y and z.
SELECT *, CASE WHEN x+y>z AND x+z>y AND y+z>x THEN 'YES' ELSE 'No' END AS triangle FROM triangle
5. Consecutive Available Seats
Several friends at a cinema ticket office would like to reserve consecutive available seats.
Can you help to query all the consecutive available seats order by the seat_id using the following cinema table?
SELECT DISTINCT a.seat_id FROM cinema a JOIN cinema b ON ABS(a.seat_id-b.seat_id) =1 and a.free=true and b.free = true ORDER BY a.seat_id
6. Friend Requests I: Overall Acceptance Rate
In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well. Now given two tables as below:
Write a query to find the overall acceptance rate of requests rounded to 2 decimals, which is the number of acceptance divide the number of requests. For the sample data above, your query should return the following result：
7.Biggest Single Number
Table my_numbers contains many numbers in column num including duplicated ones.
Can you write a SQL query to find the biggest number, which only appears once.
+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
For the sample data above, your query should return the following result:
+---+
|num|
+---+
| 6 |
SELECT num FROM number GROUP BY num HAVING COUNT(*)=1 ORDER BY num DESC LIMIT 1
或者 SELECT MAX(t.num) AS num FROM (SELECT num FROM number GROUP BY num HAVING COUNT(*) = 1) AS t
8. Managers with at Least 5 Direct Reports
SELECT Name FROM Employee AS t1 JOIN (SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT (ManagerId) >=5) AS t2 ON t1.Id =t2.ManagerId;
9. Tree Note
SELECT id, (CASE WHEN p_id IS NULL THEN 'Root' WHEN id in (SELECT p_id FROM Tree) THEN 'Inner'
ELSE 'Leaf' END) as Type From Tree;
10. Shortest Distance in a Plane
SELECT ROUND (SQRT(MIN((POW(p1.x-p2.x,2)+POW(p1.y-p2.y,2)))),2) AS shortest FROM point_2d p1 JOIN point_2d p2 ON(p1.x<=p2.x AND p1.y<p2.y) OR (p1.x<=p2.x AND p1.y>p2.y) OR (p1.x<p2.x AND p1.y=p2.y);
11. Investments in 2016
SELECT SUM (insurance.TIV_2016) AS TIV_2016 FROM insurance WHERE insurance.TIV_2015 IN (SELECT TIV_2015 FROM insurance GROUP BY TIV_2015 HAVING COUNT(*) >1) AND CONCAT(LAT,LON) IN (SELECT CONCAT (LAT,LON) FROM insurance GROUP BY LAT, LON HAVING COUNT(*)=1);
12. Friend Requests II: Who Has Most Friend?
SELECT request_id AS id, COUNT(*) AS num FROM (
SELECT request_id FROM Friend f UNION all SELECT accepter_id FROM Friend f2) AS t
GROUP BY request_id ORDER BY num DESC LIMIT 1
Follow-up:
In the real world, multiple people could have the same most number of friends, can you find all these people in this case?
union 和 union all 的区别 ：UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
13. Count Student Number in Departments
SELECT department.dept_name, count(student.student_id) AS num FROM student RIGHT OUTER JOIN department on student.dep_id=department.dep_id GROUP BY department.dept_name;
Right JOIN 和 Right OUTER JOIN
14. Winning Candidate
SELECT Candidate.Name From
(SELECT CandidateId, Count(*) as count FROM Vote GROUP BY CandidateId) AS t
LEFT JOIN Candidate ON t.CandidateId=Candidate.id ORDER BY count DESC LIMIT 1;
15. Get Highest Answer Rate Question
SELECT question_id AS 'survey_log' FROM survey_log GROUP BY question_id ORDER BY COUNT (answer_id) / SUM(IF (action='show',1,0) ) DESC LIMIT 1;
或者用Count (IF(action='show',1,NULL))
15. Second Degree Follower
SELECT follow.follower, count(*) AS num FROM follow join follow f2
ON follow.follower=f2.followee
GROUP BY f2.followee;
16. Find Median Given Frequency of Numbers
SELECT ROUND(AVG(Number),2) AS median FROM (
SELECT Number, Frequency, AccFreq, SumFreq FROM ((
SELECT Number,Frequency, @curFreq := @curFreq + Frequency AS AccFreq
FROM Numbers n, (SELECT @curFreq := 0) r ORDER BY Number) t1,
(SELECT SUM(Frequency) AS SumFreq FROM Numbers) t2)) t
WHERE AccFreq BETWEEN SumFreq/2 AND SumFreq/2 +Frequency;
User-Defined Variables: 可用set来设置变量初始值
17. Median Employee Salary
SELECT DISTINCT t1.Company, t1.salary FROM (
(SELECT e1.Company, count(1) AS 'rank', e1.salary FROM employees e1
LEFT JOIN employees e2 on e1.Company=e2.Company
WHERE e1.salary <= e2.salary GROUP BY e1.Company, e1.Id, e1.salary) t1 LEFT JOIN
(SELECT Company, COUNT(*) as count FROM employees GROUP BY Company) t2
ON t1.Company=t2.Company) WHERE rank/count BETWEEN 0.5 AND 0.5+1/count;
18. Students Report By Geography
select min(America) as America, min(Asia) as Asia, min(Europe) as Europe from(
SELECT (case when continent = 'America' then @am:=@am+1 when continent = 'Asia' then @as:=@as+1
when continent = 'Europe' then @eu:=@eu+1
end) as rowline,(CASE WHEN continent="America" Then name END) AS America,
(CASE WHEN continent="Asia" Then name END) AS Asia,
(CASE WHEN continent="Europe" Then name END) AS Europe
FROM student_loc,(select @am:=0, @as := 0, @eu:=0) temp
order by name) m group by rowline;
19.Average Salary: Departments VS Company
Create table salary(Id int,employee_id int,amount int,pay_date date);
insert into salary values(1,1,9000,'2017-03-31');
insert into salary values(2,2,6000,'2017-03-31');
insert into salary values(3,3,10000,'2017-03-31');
insert into salary values(4,1,7000,'2017-02-28');
insert into salary values(5,2,6000,'2017-02-28');
insert into salary values(6,3,8000,'2017-02-28');
Create table employee_dept(Id int,department_id int);
insert into employee_dept values(1,1);
insert into employee_dept values(2,2);
insert into employee_dept values(3,2);
SELECT t1.pay_month, t1.department_id, (CASE WHEN dept_avg > com_avg THEN 'higher' WHEN dept_avg<com_avg THEN 'lower' ELSE 'same' END) AS comparison FROM (
(SELECT LEFT(pay_date,7) AS pay_month, department_id, AVG(s.amount) AS dept_avg FROM salary s
LEFT JOIN employee_dept d ON s.employee_id=d.Id GROUP BY LEFT(pay_date,7),department_id) t1 LEFT JOIN (
SELECT LEFT(pay_date,7) AS pay_month,AVG(amount) AS com_avg FROM salary GROUP BY LEFT(pay_date,7)) t2 on t1.pay_month=t2.pay_month);
20.Find Cumulative Salary of an Employee
select a.id, a.month, sum(b.salary) from employees2 a, employees2 b where a.id = b.id and a.month >= b.month and a.month < (select max(month) from employees2 c where a.id = c.id) group by a.id, a.month ORDER BY a.Id, a.Month DESC;
