구름 아카이브

링크:
https://school.programmers.co.kr/learn/courses/30/lessons/151141

습득한 점:

테이블 끼리의 조인을 할 때도,

두 테이블에 단순히 같은 값이 있다고 자주쓰던 INNER JOIN으로 그냥 매칭 시킬 것이 아니라

묶어야 할 테이블의 데이터 형식에 맞추어 조인 할 수 있도록

CASE 문을 통해, 특정 구간의 숫자 레인지를 값으로 바꾸는 데이터 조작 방식과,

이를 NULL 값이 드러 날 수 있도록 LEFT JOIN 으로 묶는  쿼리 작성 방식을 알게되었음.

주어진 테이블의 전체 형태를 보고 데이터 조작과, 테이블 조인을 시도해야 겠다는 생각이 들었음.

테이블을 연계구조를 한눈에 파악하기가 아직은 힘들었는데, 한눈에 파악 안되면 직접 그림을 그려보는 것도 하나의 방법이 될 것 같음.

문제:

- 자동차 종류: 트럭 의 대여기록 별 대여금액, 대여기록 ID + 대여금액 리스트 출력
- 컬럼명은 FEE: 대여기록 별 대여 금액
- 대여금액을 기준으로 내림차순 desc, 기록 id 내림차순

문제파악:

날짜의 특정 레인지를 특정 값으로 바꾸는 것이 포인트.

- 히스토리 테이블의 기간범위 값들을 특정 4구간으로 변환 >>> why? 기간별 할인율이 다름
- DURATION_TYPE이 원래 존재하는 디스카운트 테이블과 조인 3개의 구간 존재
- none값은 할인을 받지 않는 값으로써 활용해야함. (0~6일 구간)
- 조인했을때, 조인된 테이블에 값이 null값이라면 0으로 치환하기
- 100-ifnull(discount_rate,0)/100 > 할인율이 null값이면 0을 반환, null값이 아니면 할인율 반환
- daily_fee*period*discount_rate FEE 컬럼 형성
- 0으로 치환된 것들은 자동으로 1로 변환 (100-0)/100 =값에 변화가 없음.

# 한눈에 파악 안되면, 테이블 그리기

# 문제 제대로 읽기

풀이:

WITH def AS (
    SELECT CAR.DAILY_FEE
         , CAR.car_type
         , HISTORY.history_id
         , DATEDIFF(END_DATE,START_DATE) + 1 AS lending_p
         , CASE 
               WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 90 THEN '90일 이상'
               WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 30 THEN '30일 이상'
               WHEN DATEDIFF(END_DATE,START_DATE) + 1 >= 7 THEN '7일 이상'
               ELSE 'NONE'
           END AS duration_type
    
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
      INNER JOIN CAR_RENTAL_COMPANY_CAR AS CAR ON CAR.CAR_ID = HISTORY.CAR_ID AND CAR.CAR_TYPE ='트럭'
    
               )


SELECT def.HISTORY_ID
     , ROUND(def.daily_fee * def.lending_p * 
            (1- (IFNULL(plan.DISCOUNT_RATE,0)/100))) AS FEE
     

FROM def
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN ON def.DURATION_TYPE = PLAN.DURATION_TYPE
AND PLAN.CAR_TYPE = '트럭'
ORDER BY FEE DESC, def.HISTORY_ID DESC

 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

*** 다시 풀어볼 것

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

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 값