hackerRank _ top earner_MAX
내가 쓴 답
SELECT months*salary AS earnings
, COUNT(employee_id) AS count
FROM EMPLOYEE
GROUP BY 1
ORDER BY months*salary DESC
LIMIT 1;
선생님 답
SELECT months*salary
, COUNT(*)
FROM EMPLOYEE
WHERE months*salary = (SELECT MAX(months*salary) FROM EMPLOYEE)
GROUP BY 1
HackerRank_Weather15 _ ROUND, 서브쿼리
SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345 );
#HackerRank_ Weather19_ROUND, SQRT, POWER
-- (MIN(LAT_N),MAX(LAT_N))
-- (MIN(LONG_W),MAX(LONG_W))
SELECT ROUND(SQRT(POWER(MIN(LAT_N)-MAX(LAT_N),2) + POWER((MIN(LONG_W)-MAX(LONG_W)),2)),4)
FROM STATION ;
HackerRank_Weather20_서브쿼리, WITH
내가쓴답
SELECT ROUND(LAT_N, 4)
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY LAT_N) AS RNK
, LAT_N
FROM STATION
) AS A
WHERE RNK =ROUND((SELECT COUNT(LAT_N) FROM STATION)/2,0);
선생님 답
WITH ordering AS(
SELECT ROW_NUMBER() OVER(ORDER BY LAT_N) AS RNK
, LAT_N
FROM STATION )
SELECT ROUND(AVG(LAT_N),4)
FROM ordering
WHERE RNK = (SELECT FLOOR(AVG(RNK)) FROM ordering)
OR RNK = (SELECT CEIL(AVG(RNK)) FROM ordering)
HackerRank_Triangle_CASE
SELECT CASE WHEN (A=B AND B=C) THEN 'Equilateral'
WHEN (A+B<=C OR B+C<=A OR A+C<=B) THEN 'Not A Triangle'
WHEN (A=B OR B=C OR A=C) THEN 'Isosceles'
ELSE 'Scalene' END
FROM TRIANGLES;
&&Hacker Rank _ CONCAT, SUBSTR, LOWER
SELECT CONCAT(name,'(',SUBSTR(occupation,1,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), occupation ;
==========================
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);
HackerRank_Occupation_RANK
SELECT MIN(CASE WHEN A.occupation='Doctor' THEN A.name ELSE NULL END ) AS doctor
, MIN(CASE WHEN A.occupation='Professor' THEN A.name ELSE NULL END) AS professor
, MIN(CASE WHEN A.occupation='Singer' THEN A.name ELSE NULL END ) AS singer
, MIN(CASE WHEN A.occupation='Actor' THEN A.name ELSE NULL END) AS actor
FROM (
SELECT * , ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS RNK
FROM OCCUPATIONS
) A
GROUP BY RNK
HackerRAnk _ Root, Case when
내답
SELECT N
, CASE WHEN P IS NULL THEN 'Root'
WHEN N IN (SELECT P FROM BST ) THEN 'Inner'
ELSE 'Leaf' END
FROM BST
ORDER BY 1
선생님답
SELECT CASE WHEN P IS NULL THEN CONCAT(N,' Root' )
WHEN N IN (SELECT P FROM BST ) THEN CONCAT(N,' Inner')
ELSE CONCAT(N,' Leaf') END
FROM BST
ORDER BY N
HackerRank _ new company
SELECT A.company_code
, A.founder
, COUNT(DISTINCT D.lead_manager_code)
, COUNT(DISTINCT D.senior_manager_code)
, COUNT(DISTINCT D.manager_code)
, COUNT(DISTINCT D.employee_code)
FROM company A
LEFT JOIN Employee D
ON A.company_code = D.company_code
GROUP BY 1,2
ORDER BY 1 ;
선생님답,
SELECT A.company_code
, A.founder
, ( SELECT COUNT(DISTINCT lead_manager_code)
FROM lead_manager
WHERE company_code = A.company_code) AS num_lead
, ( SELECT COUNT(DISTINCT senior_manager_code)
FROM senior_manager
WHERE company_code = A.company_code) AS num_senior
, ( SELECT COUNT(DISTINCT manager_code)
FROM manager
WHERE company_code = A.company_code) AS num_manager
, ( SELECT COUNT(DISTINCT employee_code)
FROM employee
WHERE company_code = A.company_code) AS num_employee
FROM company AS A
ORDER BY 1 ASC;
HackerRank INNER JOIN
SELECT A.CONTINENT
, FLOOR(AVG(B.Population)) AS avg_city
FROM COUNTRY A
INNER JOIN CITY B
ON A.CODE = B.COUNTRYCODE
GROUP BY 1
ORDER BY 1
hacker RANK 범위 JOIN
내 답
SELECT CASE WHEN B.GRADE<8 THEN NULL ELSE A.NAME END
, B.GRADE
, A.MARKS
FROM STUDENTS A
JOIN GRADES B
ON A.MARKS BETWEEN B.MIN_MARK AND B.MAX_MARK
ORDER BY B.GRADE DESC , A.NAME ASC, A.MARKS ASC
HACKER RANK - JOIN
내가 푼 답
SELECT hacker_id
, name
, SUM(s_score) AS total_score
FROM ( SELECT A.hacker_id
, A.name
, B.challenge_id
, MAX(B.score) AS s_score
FROM Hackers AS A
LEFT JOIN Submissions AS B
ON (A.hacker_id = B.hacker_id)
GROUP BY A.hacker_id, A.name, B.challenge_id
) C
GROUP BY 1, 2
HAVING SUM(s_score) <> '0'
ORDER BY 3 DESC, 1
--선생님 답
SELECT A.hacker_id
, A.name
, SUM(max_score) AS total_score
FROM HACKERS A
INNER JOIN (SELECT hacker_id
, challenge_id
, MAX(score) AS max_score
FROM Submissions
GROUP BY 1,2
) B
ON (A.hacker_id = B.hacker_id)
GROUP BY 1, 2
HAVING SUM(max_score) <> '0'
ORDER BY 3 DESC, 1
짱짱어려윰 Hacker Rank_challenge
SELECT main.hacker_id
, main.name
, main.num
FROM(
SELECT A.hacker_id
, A.name
, COUNT(B.challenge_id) AS num
FROM HACKERS AS A
INNER JOIN CHALLENGES AS B
ON (A.hacker_id = B.hacker_id)
GROUP BY A.hacker_id, A.name
) AS main
WHERE main.num = (SELECT max(cnt_challenge)
FROM (
SELECT COUNT(challenge_id) AS cnt_challenge
FROM CHALLENGES
GROUP BY hacker_id
) t1
)
OR main.num IN (SELECT cnt_challenge
FROM ( SELECT COUNT(challenge_id) AS cnt_challenge
FROM CHALLENGES
GROUP BY hacker_id
) t2
GROUP BY cnt_challenge
HAVING COUNT(cnt_challenge)<2 )
ORDER BY main.num DESC, main.hacker_id
좀어려움, 다시풀어보기HACKER RANK - inner join
SELECT A.name
FROM students A
INNER JOIN packages B
ON A.id = B.id
INNER JOIN (SELECT C.id
, C.friend_id AS friend_id
, D.salary AS friend_salary
FROM friends C
INNER JOIN packages D
ON C.friend_id = D.id) E
ON A.id = E.id
WHERE B.salary < E.friend_salary
ORDER BY E.friend_salary
어려움!! JOIN 심화!!
SELECT tb1.start_date
, MIN(tb2.end_date)
FROM (
SELECT start_date
FROM projects
WHERE start_date NOT IN (SELECT end_date FROM projects)
) tb1
INNER JOIN
(SELECT end_date
FROM projects
WHERE end_date NOT IN (SELECT start_date FROM projects)
) tb2
ON tb1.start_date < tb2.end_date
GROUP BY tb1.start_date
ORDER BY MIN(tb2.end_date)-tb1.start_date
, tb1.start_date
Hacker Rank _ Blunder
SELECT CEIL(AVG(salary) - AVG(REPLACE (salary, '0','')))
FROM employees
다시품
SELECT DATE_FORMAT(A.visited_at - interval 9 hour, '%Y-%m-%d') AS d_date
, customer_id
, max(A.visited_at) as last_visit
FROM tbl_visit AS A
WHERE A.visited_at >='2020-07-01'
AND A.visited_at <
GROUP BY 1, 2