구름 아카이브

https://www.hackerrank.com/challenges/challenges/submissions/database/331359810

SELECT h.hacker_id
     , h.name
     , count(*) Challenges_created
FROM Challenges c 
     INNER JOIN Hackers h ON h.hacker_id = c.hacker_id

GROUP BY h.hacker_id, h.name
HAVING Challenges_created = (SELECT MAX(Challenges_created)             --Challeges_created MAX 값이 50인 것         
                                FROM(    
                                    SELECT hacker_id
                                         , count(*) Challenges_created
                                    FROM Challenges
                                    GROUP BY hacker_id
                                    ) sub)
                    
OR Challenges_created IN (SELECT Challenges_created             --만들어진 챌린지수의 묶음이 각각 몇개인지 ex) 몇명의 사람들이 12개의 챌린지를 만들었는지?       
                            FROM(
                                SELECT hacker_id
                                     , COUNT(*) AS Challenges_created               --해커 한명 당 몇개의 챌린지를 만들었는지?
                                FROM Challenges
                                GROUP BY hacker_id
                                )sub
                            GROUP BY Challenges_created
                            HAVING COUNT(*)=1               --12개를 만든 사람이 딱 한명일 때만 목록에 보존
                         )                     


ORDER BY challenges_created DESC, hacker_id



-----------------------------------------------------------------
-----------------------------------------------------------------



Challenges - WITH 문 활용  - Medium
https://www.hackerrank.com/challenges/challenges/submissions/database/331359810
-----------------------------------------------------------------

WITH counter AS (
    SELECT hackers.hacker_id
         , hackers.name
         , count(*) AS Challenges_created
    FROM Challenges
        INNER JOIN Hackers ON Challenges.hacker_id = Hackers.hacker_id
    GROUP BY hackers.hacker_id, hackers.name
) 

SELECT counter.hacker_id
     , counter.name
     , counter.Challenges_created
FROM counter
WHERE Challenges_created = (SELECT MAX(Challenges_created) FROM counter)
OR Challenges_created IN (SELECT Challenges_created        -- if count(*) is in HAVING, then exclude "count(*)" in SELECT clause
                          FROM counter
                          GROUP BY Challenges_created
                          HAVING count(*)=1 )
ORDER BY counter.Challenges_created DESC, counter.hacker_id

Top earners - 서브쿼리
https://www.hackerrank.com/challenges/earnings-of-employees/problem

SELECT months*salary as earnings
     , count(*)
FROM employee

WHERE months*salary = (SELECT MAX(months*salary) FROM employee)
GROUP BY earnings


-- Having >>> GROUP BY 결과물을 다시 한 번 필터링 해줄 때


SELECT months*salary AS earnings
     , count(*)
FROM employee
GROUP BY earnings
HAVING earnings = (SELECT MAX(months*salary) FROM employee)  -- 전체 임직원 중에서 최대 값을 가진 사람만



-- FROM >>> FROM 절을 테이블로 활용
 
SELECT *
FROM (

        SELECT months*salary AS earnings
             , count(*)
        FROM employee
        GROUP BY earnings
    ) sub
ORDER BY earnings DESC
limit 1;

*** 다시 풀어볼 것

184. Department Highest Salary
https://leetcode.com/problems/Department-Highest-Salary/

# 각각의 부서에서 누가 가장 고소득자인지
# sel 부서, 직원, 급여 
# 순서 상관 x

SELECT
       d.name AS Department
     , e.name AS Employee
     , e.salary AS Salary
FROM employee as e
     INNER JOIN (
          SELECT departmentID, MAX(salary) AS max_salary
          FROM Employee
          GROUP BY departmentID
              ) AS dh ON e.departmentID = dh.departmentID
                    AND e.salary = dh.max_salary
     INNER JOIN department AS d ON d.id = e.departmentId