1.The PADS

image.png
image.png
image.png

select concat(name,'(',left(OCCUPATION,1),')')
from OCCUPATIONS
order by name;

select concat('There are a total of ',count(OCCUPATION),' ',lower(OCCUPATION),'s.')
from OCCUPATIONS
group by OCCUPATION
order by count(OCCUPATION) asc,OCCUPATION asc;

2.Occupations

image.png
image.png
image.png

select min(Doctor), min(Professor),min(Singer),  min(Actor)
from(
select ROW_NUMBER() OVER(PARTITION By Doctor,Actor,Singer,Professor order by name asc) AS Rownum, 
case when Doctor=1 then name else Null end as Doctor,
case when Actor=1 then name else Null end as Actor,
case when Singer=1 then name else Null end as Singer,
case when Professor=1 then name else Null end as Professor
from occupations
pivot
( count(occupation)
for occupation in(Doctor, Actor, Singer, Professor)) as p

) temp

group by Rownum  ;

mysql字符串大小比较：使用MAX()查询一个字符串类型的字段时，字符串类型大小比较是先比较首字符的ASCII码的大小，然后依次往后进行比较的。
对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。
当然，对与日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小.

讲解：
可以使用用户定义的变量辅助创建新表。RowLine 表示这个名字应该被放在结果的第几行。因为结果希望将姓名按字母表顺序排列，所以可以先按名字排序。暂时把这张表叫做t。当得到t这样的表，就可以把查询语句先写成"SELECT MIN(Doctor), MIN(Professor), MIN(Singer), MIN(Actor) FROM t GROUP BY RowLine"。

为了生成表格t，可以定义变量和使用CASE语句。创建四个变量来记录对应行数（RowLine），一个职业一个变量。使用CASE来针对不同的职业进行不同的操作。

变量的设置
参考

3.Binary Tree Nodes

image.png
image.png
image.png

select N, 
case when P is NULL then 'Root' 
when N in (select P from BST) then 'Inner' 
else 'Leaf' 
end as Node 
from BST 
order by N;

SELECT N, 
IF(P IS NULL,'Root',IF((SELECT COUNT(*) FROM BST WHERE P=B.N)>0,'Inner','Leaf')) 
FROM BST AS B 
ORDER BY N;

if的这个答案有点蒙圈，不太懂为什么要count。。。

4.New Companies

image.png
image.png
image.png

SELECT c.company_code, c.founder, 
       COUNT(DISTINCT l.lead_manager_code), COUNT(DISTINCT s.senior_manager_code),
       COUNT(DISTINCT m.manager_code), COUNT(DISTINCT e.employee_code)
FROM Company c, Lead_Manager l, Senior_Manager s, Manager m, Employee e
WHERE c.company_code = l.company_code AND 
      l.lead_manager_code = s.lead_manager_code AND
      s.senior_manager_code = m.senior_manager_code AND
      m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder ORDER BY c.company_code;

5.Contest Leaderboard

image.png
image.png
image.png
image.png

SELECT h.hacker_id, h.name, SUM(score) FROM (
    SELECT hacker_id, challenge_id, MAX(score) AS score FROM SUBMISSIONS
    GROUP BY hacker_id, challenge_id
)t 
JOIN Hackers h on t.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING SUM(score) > 0
ORDER BY SUM(score) desc, h.hacker_id

6.Weather Observation Station 19

image.png

select ROUND(sqrt(pow(abs(Max(Lat_n)-min(Lat_n)),2)+pow(abs(Max(long_w)-min(long_w)),2)),4) 
from station;

7.Weather Observation Station 20

image.png

求中位数

SET @r = -1; 
SELECT ROUND(AVG(Temp.L), 4) 
FROM 
(SELECT @r := @r + 1 AS r, Lat_N as L 
 FROM Station 
 ORDER BY Lat_N) Temp 
 WHERE Temp.r = ceil(@r/2) and temp.r = floor(@r/2);

参考

8.The Report

image.png
image.png
image.png

select (case when grade <8 THEN NULL ELSE name END) name, grade, marks 
from students,grades 
where marks between min_Mark and Max_Mark 
order by grade desc, coalesce(name,marks);

9.Top Competitors

image.png
image.png

我的错误答案

select Hackers.hacker_id,Hackers.name
from Hackers
inner join Submissions on Hackers.hacker_id=Submissions.hacker_id
inner join Challenges on Challenges.hacker_id= Hackers.hacker_id
inner join Difficulty on Challenges.difficulty_level = Difficulty.difficulty_level
where Submissions.score in(select max(Submissions.score) from Submissions)
having count(Challenges.challenge_id) as cc >1
order by cc desc,Hackers.hacker_id;

正确答案

select h.hacker_id, h.name
from submissions s
inner join challenges c
on s.challenge_id = c.challenge_id
inner join difficulty d
on c.difficulty_level = d.difficulty_level 
inner join hackers h
on s.hacker_id = h.hacker_id
where s.score = d.score and c.difficulty_level = d.difficulty_level
group by h.hacker_id, h.name
having count(s.hacker_id) > 1
order by count(s.hacker_id) desc, s.hacker_id asc

10.Ollivander's Inventory

image.png
image.png
image.png
image.png

SELECT temp2.I, temp2.A, temp2.WNN, temp2.P FROM (SELECT MIN(W1.COINS_NEEDED) AS WN, WP1.AGE as AG, W1.POWER AS PW FROM WANDS W1 INNER JOIN WANDS_PROPERTY WP1 ON W1.CODE=WP1.CODE 
 GROUP BY W1.POWER, WP1.AGE ORDER BY W1.POWER DESC, WP1.AGE DESC) temp1
INNER JOIN
(SELECT W.ID AS I, MIN(W.COINS_NEEDED) AS WNN, WP.AGE as A, W.POWER AS P  FROM WANDS W INNER JOIN WANDS_PROPERTY WP ON W.CODE=WP.CODE 
WHERE WP.IS_EVIL=0
GROUP BY W.POWER, WP.AGE, W.ID ORDER BY W.POWER DESC, WP.AGE DESC) temp2
ON temp1.WN=temp2.WNN AND temp1.PW=temp2.P AND temp1.AG=temp2.A;

11.Challenges

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

image.png
image.png
image.png
image.png

SELECT c.hacker_id, h.name, COUNT(c.challenge_id) AS cnt 
FROM Hackers AS h JOIN Challenges AS c ON h.hacker_id = c.hacker_id
GROUP BY c.hacker_id, h.name HAVING
cnt = (SELECT COUNT(c1.challenge_id) FROM Challenges AS c1 GROUP BY c1.hacker_id ORDER BY COUNT(*) DESC LIMIT 1) OR
cnt NOT IN (SELECT COUNT(c2.challenge_id) FROM Challenges AS c2 GROUP BY c2.hacker_id HAVING c2.hacker_id <> c.hacker_id)
ORDER BY cnt DESC, c.hacker_id;

参考答案

12.Projects

You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

image.png
image.png
image.png

SET sql_mode = '';
SELECT Start_Date, End_Date
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date 
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date

13. Placements

image.png
image.png
image.png
image.png

select s.name 
from students s 
inner join friends f 
on s.id=f.id 
inner join packages p 
on p.id=s.id 
inner join packages p1 
on p1.id=f.friend_id 
where (p1.salary-p.salary)>0 
order by p1.salary;

14.Symmetric Pairs

image.png

SELECT f1.X, f1.Y FROM Functions f1
INNER JOIN Functions f2 ON f1.X=f2.Y AND f1.Y=f2.X
GROUP BY f1.X, f1.Y
HAVING COUNT(f1.X)>1 or f1.X<f1.Y
ORDER BY f1.X 

15.Print Prime Numbers

image.png

SET @potential_prime = 1;
SET @divisor = 1;

SELECT GROUP_CONCAT(POTENTIAL_PRIME SEPARATOR '&') FROM
    (SELECT @potential_prime := @potential_prime + 1 AS POTENTIAL_PRIME FROM
    information_schema.tables t1,
    information_schema.tables t2
    LIMIT 1000) list_of_potential_primes
WHERE NOT EXISTS(
    SELECT * FROM
        (SELECT @divisor := @divisor + 1 AS DIVISOR FROM
        information_schema.tables t4,
        information_schema.tables t5
        LIMIT 1000) list_of_divisors
    WHERE MOD(POTENTIAL_PRIME, DIVISOR) = 0 AND POTENTIAL_PRIME <> DIVISOR);

16. Interviews

Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds screening contest.

image.png
image.png
image.png
image.png

select con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
join colleges col on con.contest_id = col.contest_id 
join challenges cha on  col.college_id = cha.college_id 
left join
(select challenge_id, sum(total_views) as total_views, sum(total_unique_views) as total_unique_views
from view_stats group by challenge_id) vs on cha.challenge_id = vs.challenge_id 
left join
(select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions from submission_stats group by challenge_id) ss on cha.challenge_id = ss.challenge_id
    group by con.contest_id, con.hacker_id, con.name
        having sum(total_submissions)!=0 or 
                sum(total_accepted_submissions)!=0 or
                sum(total_views)!=0 or
                sum(total_unique_views)!=0
            order by contest_id;

17.15 Days of Learning SQL

image.png
image.png
image.png

select 
submission_date ,

( SELECT COUNT(distinct hacker_id)  
 FROM Submissions s2  
 WHERE s2.submission_date = s1.submission_date AND    (SELECT COUNT(distinct s3.submission_date) FROM      Submissions s3 WHERE s3.hacker_id = s2.hacker_id AND s3.submission_date < s1.submission_date) = dateDIFF(s1.submission_date , '2016-03-01')) ,

(select hacker_id  from submissions s2 where s2.submission_date = s1.submission_date 
group by hacker_id order by count(submission_id) desc , hacker_id limit 1) as shit,
(select name from hackers where hacker_id = shit)
from 
(select distinct submission_date from submissions) s1
group by submission_date

reference:
https://nifannn.github.io/2018/06/01/SQL-%E7%AC%94%E8%AE%B0-Hackerrank-Occupations/
https://blog.csdn.net/qqxyy99/article/details/79980005