구름 아카이브

https://leetcode.com/problems/department-top-three-salaries/

-- RANK(), DENSE_RANK()


SELECT t.department
     , t.employee
     , t.salary
     
FROM(
    SELECT department.name AS dapartment
        , employee.name AS employee
        , employee.salary
        , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr

    FROM Employee
        INNER JOIN dapartment ON employee.departmentId = dapartment.id
    ) t
WHERE t.dr <=3

https://leetcode.com/problems/department-highest-salary/

*** SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음

SELECT department.name AS Department
     , sub.name AS Employee
     , sub.max_salary AS Salary
FROM( 
    SELECT id 
         , name
         , salary
         , departmentid
         , MAX(salary) OVER (PARTITION BY departmentId) AS max_salary
    FROM employee
    -- WHERE salary = max_salary >>> SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음
    ) as sub
    INNER JOIN Department ON sub.departmentid = department.id
WHERE sub.salary = sub.max_salary

https://leetcode.com/problems/consecutive-numbers/

-- Use Window function

-- *LEAD,  *LAG



SELECT DISTINCT l.NUM AS ConsecutiveNums
     SELECT NUM
          , LEAD(NUM,1) OVER (ORDER BY id) AS next
          , LEAD(NUM,2) OVER (ORDER BY id ) AS afternext

     FROM logs
 )l
 WHERE l.Num=next AND l.next=l.afternext

https://www.hackerrank.com/challenges/what-type-of-triangle/problem

SELECT
      CASE 
           WHEN A=B AND B=C THEN 'Equilateral' 
      
           WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'

           WHEN A=B OR A=C OR B=C THEN 'Isosceles'
           
           ELSE 'Scalene'
           
      END
      
FROM TRIANGLES

Symmetric Pairs - Medium 
https://www.hackerrank.com/challenges/symmetric-pairs/problem

SELECT X, YG
FROM Functions
WHERE X=Y
GROUP BY X,Y
HAVING COUNT(*)>=2

UNION

SELECT s1.X, s1.Y
FROM functions AS s1
     INNER JOIN functions AS s2 ON s1.X = s2.Y AND s1.Y = s2.X
WHERE s1.X < s1.Y
ORDER BY X

 African Cities 
https://www.hackerrank.com/challenges/african-cities/problem

SELECT city.name FROM city
      INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.CODE
WHERE continent = 'Africa'

Population Census
https://www.hackerrank.com/challenges/asian-population/problem

SELECT SUM(city.population) FROM city
       INNER JOIN country ON  city.CountryCode = Country.Code
WHERE CONTINENT = 'Asia'

New Companies - Medium 
https://www.hackerrank.com/challenges/the-company/problem

SELECT C.company_code 
       ,C.founder
       ,COUNT(DISTINCT LM.lead_Manager_code)
       ,COUNT(DISTINCT SM.senior_Manager_code)
       ,COUNT(DISTINCT M.manager_code)
       ,COUNT(DISTINCT E.employee_code)
       
FROM Company C
     LEFT JOIN Lead_Manager LM ON C.company_code = LM.company_code
     LEFT JOIN Senior_Manager SM ON LM.lead_manager_code = SM.lead_manager_code
     LEFT JOIN Manager M ON SM.senior_manager_code = M.senior_manager_code
     LEFT JOIN Employee E ON M.manager_code = E.manager_code

GROUP BY C.company_code, C.founder
ORDER BY C.Company_code

Top Competitors - Medium
https://www.hackerrank.com/challenges/full-score/problem

SELECT H.hacker_id
      ,H.name
FROM Submissions S
    INNER JOIN Challenges C ON C.Challenge_id = S.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
GROUP BY H.hacker_id, H.name
HAVING COUNT(DISTINCT S.submission_id)>1     
ORDER BY COUNT(DISTINCT S.submission_id) DESC, H.hacker_id

Placements - Medium
https://www.hackerrank.com/challenges/placements/problem

SELECT NAME
FROM Friends F
     INNER JOIN Students S ON S.ID=F.ID
     INNER JOIN Packages P1 ON F.ID = P1.ID
     INNER JOIN Packages P2 ON F.Friend_ID = P2.ID
WHERE P2.salary > P1.salary
ORDER BY P2.salary

* CEIL - 소수점 첫째 자리에서 올림

* FLOOR - 소수점 첫째 자리에서 내림

* ROUND - 숫자를 각 자리에서 반올림

* TRUNCATE(숫자, 버릴 자릿수) - 숫자를 버릴 자릿수 아래로 버림

* ABS - 절대값

* POWER - 제곱

* SQRT - 양수 x 에 대한 제곱근을 반환

Average Population
https://www.hackerrank.com/challenges/average-population/problem

SELECT Floor(AVG(population))
FROM CITY

Weather Observation Station 2
https://www.hackerrank.com/challenges/weather-observation-station-2/submissions/database/293205884

SELECT  ROUND(SUM(LAT_N),2) AS lat  
       ,ROUND(SUM(LONG_W),2) AS lon
FROM STATION

Weather Observation Station 18 - Medium 
https://www.hackerrank.com/challenges/weather-observation-station-18/problem

SELECT ROUND(ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W)),4)
FROM STATION

Weather Observation Station 19 - Medium 
https://www.hackerrank.com/challenges/weather-observation-station-19/problem

SELECT TRUNCATE(SQRT( POWER(MAX(LAT_N)-MIN(LAT_N),2) + POWER(MAX(LONG_W)-MIN(LONG_W),2) ),4)
FROM STATION

Average Population of Each Continent
https://www.hackerrank.com/challenges/average-population-of-each-continent/problem

SELECT COUNTRY.CONTINENT, FLOOR(AVG(city.population)) FROM CITY
       INNER JOIN COUNTRY ON COUNTRY.Code = CITY.CountryCode
       
GROUP BY COUNTRY.CONTINENT

Revising Aggregations - Averages 
https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem

SELECT AVG(population)
FROM CITY
WHERE District = "California"

Revising Aggregations - The Sum Function
https://www.hackerrank.com/challenges/revising-aggregations-sum/problem

SELECT SUM(population)
FROM CITY
WHERE District = "California"

Revising Aggregations - The Count Function
https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem

SELECT COUNT(COUNTRYCODE)
FROM CITY
WHERE POPULATION > 100000

Weather Observation Station 4
https://www.hackerrank.com/challenges/weather-observation-station-4/problem

SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION

Top Earners 

https://www.hackerrank.com/challenges/earnings-of-employees/problem

SELECT salary * months AS earnings , COUNT(*)
FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

Japan Population
https://www.hackerrank.com/challenges/japan-population/problem

SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE='JPN'

Weather Observation Station 13 
https://www.hackerrank.com/challenges/weather-observation-station-13/problem

SELECT TRUNCATE(SUM(LAT_N),4)
FROM STATION
WHERE LAT_N>=38.7880 AND LAT_N<=137.2345

Weather Observation Station 3
https://www.hackerrank.com/challenges/weather-observation-station-3/problem

SELECT CITY
FROM STATION
WHERE ID%2=0
GROUP BY CITY

https://www.hackerrank.com/challenges/the-report/problem?h_r=internal-search 

- SELECT 구문에서 CASE 활용해서 구분점 만들기
- 정렬기준 여러개
- INNER 조인 조건에 BETWEEN 사용할 수 있음.hacker_id

SELECT CASE WHEN g.grade <8 THEN NULL ELSE s.name END AS name
     , g.grade
     , s.marks
FROM students AS s
     INNER JOIN grades AS g ON s.marks BETWEEN g.min_mark AND g.max_mark
     
ORDER BY g.grade DESC, name ASC, s.marks

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;

Consecutive Numbers
https://leetcode.com/problems/consecutive-numbers/

# 최소 3번이상 연속적으로 나타나는 숫자 찾기
# 순서는 상관없음
 

 SELECT DISTINCT l.num AS ConsecutiveNums   -- 중복제거
 FROM logs AS l
      INNER JOIN logs AS l_next ON l.id + 1 = l_next.id
      INNER JOIN logs AS l_next2 ON l.id + 2 =  l_next2.id
 
 # three times consecutively
 WHERE l.num = l_next.num AND l_next.num = l_next2.num

Reformat Department Table
https://leetcode.com/problems/reformat-department-table/

SELECT id,
      SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS JAN_Revenue,
      SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
      SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
      SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
      SUM(CASE WHEN month='May' THEN revenue ELSE NULL END) AS May_Revenue,
      SUM(CASE WHEN month='Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
      SUM(CASE WHEN month='Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
      SUM(CASE WHEN month='Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
      SUM(CASE WHEN month='Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
      SUM(CASE WHEN month='Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
      SUM(CASE WHEN month='Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
      SUM(CASE WHEN month='Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
     
        
FROM Department
GROUP BY id

Customers Who Never Order
https://leetcode.com/problems/Customers-Who-Never-Order/

SELECT name AS Customers FROM Customers AS c
       LEFT JOIN Orders AS o ON o.customerId  = c.id
WHERE o.id is null

Employees Earning More Than Their Managers
https://leetcode.com/problems/Employees-Earning-More-Than-Their-Managers/

SELECT employee.name AS Employee
FROM employee
     INNER JOIN employee as Mannager ON employee.managerID = Mannager.id

WHERE employee.salary > Mannager.salary

Rising Temperature
https://leetcode.com/problems/Rising-Temperature/

SELECT today.id
FROM weather AS today
     INNER JOIN weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate
WHERE today.temperature > yesterday.temperature


# # {"headers": ["id", "recordDate", "temperature", "id", "recordDate", "temperature"], 

# # "values": 

# # [[2, "2015-01-02", 25, 1, "2015-01-01", 10], 
# #  [4, "2015-01-04", 30, 3, "2015-01-03", 20]]}

# {"headers": ["id", "recordDate", "temperature", "id", "recordDate", "temperature"], "values": 

# [[2, "2015-01-02", 25, 1, "2015-01-01", 10], 
#  [3, "2015-01-03", 20, 2, "2015-01-02", 25], 
#  [4, "2015-01-04", 30, 3, "2015-01-03", 20]]}
 
#  # SELECT today.id AS today_id
#  #        ,today.recordDate AS today_RecordDate
#  #        ,today.Temperature AS today_Temperature
#  #        ,yesterday.id AS yesterday_id
#  #        ,yesterday.recordDate AS yesterday_RecordDate
#  #        ,yesterday.Temperature AS yesterdayy_Temperature
 
#  yesterday에 1을 더했을때 매칭되는 today 값

Big Countries
https://leetcode.com/problems/Big-Countries/

SELECT name, population, area
FROM WORLD
WHERE area >=3000000 OR population >=25000000

Not Boring Movies
https://leetcode.com/problems/Not-Boring-Movies/

SELECT id, movie, description, rating
FROM Cinema
WHERE MOD(id,2)= 1 AND description != 'boring'
ORDER BY rating DESC

Duplicate Emails
https://leetcode.com/problems/Duplicate-Emails/

SELECT email 
FROM Person
GROUP BY Person.email
HAVING COUNT(email) > 1

Combine Two Tables
https://leetcode.com/problems/Combine-Two-Tables/

SELECT firstName, lastName, city, state             
FROM Person
     LEFT JOIN Address ON Person.personId =Address.personId
     
    
    
# {"headers": 

# ["firstName", "lastName", "city", "state"], 

# "values": 

# [["Allen", "Wang", null, null], 
# ["Bob", "Alice", "New York City", "New York"]]}

https://leetcode.com/problems/Delete-Duplicate-Emails/

# email 가지고 group by를 한 뒤, 이 이메일이 가지고 있는 id값이 무엇이냐
# WHERE 절에 들어간 min ID들을 제외하고 삭제
# 서브쿼리가 동작하지 않음

DELETE >>> 지울 테이블 지정(조인을 활용 할 경우)
FROM Person >>> 삭제를 해주고 싶은 테이블 명
WHERE Id NOT IN
(

SELECT sub.min_id
FROM(

SELECT Email, MIN(id) AS min_id
FROM Person
GROUP BY Email
) sub 

)