[240603] 코딩테스트 문제풀이

1. 한 해에 잡은 물고기 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/298516

프로그래머스

SELECT COUNT(*) as 'FISH_COUNT'
FROM FISH_INFO
WHERE YEAR(TIME) = '2021 '

---

2. 잡은 물고기의 평균 길이 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/293259

프로그래머스

SELECT ROUND(AVG(IFNULL(LENGTH,10)),2) as 'AVERAGE_LENGTH'
FROM FISH_INFO

---

3. 가장 큰 물고기 10마리 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/298517

프로그래머스

SELECT ID, LENGTH
FROM FISH_INFO
ORDER BY LENGTH DESC, ID ASC 
LIMIT 10

---

4. 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284528

프로그래머스

SELECT a.EMP_NO, a.EMP_NAME, 
    CASE WHEN a.BO_PERCENT = 0.2 then 'S'
    WHEN a.BO_PERCENT = 0.15 then 'A'
    WHEN a.BO_PERCENT = 0.10 then 'B'
    ELSE 'C' END as 'GRADE',  (a.BO_PERCENT) * a.SAL as 'BONUS'
FROM (SELECT a.EMP_NO,b.EMP_NAME, a.AVG_SCORE, b.SAL,
    CASE WHEN a.AVG_SCORE >= 96 then 0.2
    WHEN a.AVG_SCORE < 96 and a.AVG_SCORE >= 90 then 0.15
    WHEN a.AVG_SCORE < 90 and a.AVG_SCORE >= 80 then 0.1
    ELSE 0 END as 'BO_PERCENT'
FROM (SELECT EMP_NO, AVG(SCORE) as 'AVG_SCORE'
FROM HR_GRADE
GROUP BY EMP_NO) as a LEFT JOIN HR_EMPLOYEES as b
ON a.EMP_NO = b.EMP_NO) as a
ORDER BY a.EMP_NO

---

5. 입양 시각 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

WITH RECURSIVE q AS
        (
        SELECT  0 AS num
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num <= 22
        )
SELECT q.num, IFNULL(count_number, 0) as 'COUNT'
FROM q LEFT JOIN (SELECT HOUR(DATETIME) as 'num', COUNT(*) as 'count_number'
                    FROM ANIMAL_OUTS
                    GROUP BY HOUR(DATETIME)) as b ON q.num = b.num     
ORDER BY q.num

 

---

6. 그룹별 조건에 맞는 식당 목록 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

WITH REVIEW_INFO AS (SELECT MEMBER_ID, REVIEW_TEXT, 
    DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") as 'REVIEW_DATE'
FROM REST_REVIEW
WHERE (MEMBER_ID) IN ((SELECT  MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
HAVING COUNT(*) = (SELECT COUNT(*)
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(*) desc
LIMIT 1))))
SELECT MEMBER_PROFILE.MEMBER_NAME, REVIEW_INFO.REVIEW_TEXT, 
    REVIEW_INFO.REVIEW_DATE
FROM REVIEW_INFO LEFT JOIN MEMBER_PROFILE
ON REVIEW_INFO.MEMBER_ID = MEMBER_PROFILE.MEMBER_ID
ORDER BY REVIEW_INFO.REVIEW_DATE, REVIEW_INFO.REVIEW_TEXT

 

---

7.  오프라인 / 온라인 판매 데이터 통합하기 (⭐️ 복습하기 )

https://school.programmers.co.kr/learn/courses/30/lessons/131537

프로그래머스

WITH ONLI AS (SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as 'SALES_DATE', 
                    PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE
    WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE <= '2022-03-31')
    ,
OFFLI AS (SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") as 'SALES_DATE',
               PRODUCT_ID, NULL as 'USER_ID', SALES_AMOUNT
              FROM OFFLINE_SALE
              WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE <= '2022-03-31')
              
SELECT * FROM ONLI
UNION ALL
SELECT * FROM OFFLI
ORDER BY SALES_DATE , PRODUCT_ID , USER_ID

1. \오프라인/온라인 판매 데이터 통합하

---

8. 주문량이 많은 아이스크림들 조회하기

• 문제를 잘 읽자 !! 

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

SELECT a.FLAVOR
FROM (SELECT FLAVOR, SUM(TOTAL_ORDER) as 'total'
FROM FIRST_HALF
GROUP BY FLAVOR) as a JOIN
(SELECT FLAVOR, SUM(TOTAL_ORDER) as 'total'
FROM JULY
GROUP BY FLAVOR) as b
ON a.FLAVOR = b.FLAVOR
ORDER BY  a.total + b.total desc
limit 3

---

9. TEST DOM - Enrollment

https://app.testdome.com/library?page=1&skillArea=65&generatorId=12

TestDome

UPDATE enrollments
SET year = 2015
WHERE id BETWEEN 20 AND 100

---

10. Test Dom - Pets

https://app.testdome.com/library?page=1&skillArea=65&generatorId=12

TestDome

WITH SUM_PETS AS (SELECT DISTINCT(name)
FROM dogs 
UNION ALL
SELECT DISTINCT(name)
FROM cats)

SELECT distinct(name)
FROM SUM_PETS

---

11. 물고기 종류 별 대어 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/293261

프로그래머스

SELECT a.ID, b.FISH_NAME, a.LENGTH
FROM FISH_INFO as a JOIN FISH_NAME_INFO as b
ON a.FISH_TYPE = b.FISH_TYPE
WHERE (a.FISH_TYPE, a.LENGTH) IN
    (SELECT FISH_TYPE, MAX(LENGTH)
    FROM FISH_INFO
    GROUP BY FISH_TYPE)
ORDER BY a.ID

---

12. 노선별 평균 역 사이 거리 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284531

프로그래머스

SELECT ROUTE, CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),"km") as 'TOTAL_DISTANCE',
    CONCAT(ROUND(AVG(D_BETWEEN_DIST),2), "km") as 'AVERAGE_DISTANCE'
FROM SUBWAY_DISTANCE
GROUP BY ROUTE 
ORDER BY TOTAL_DISTANCE desc

 

---

13. 대장균들의 자식 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299305

프로그래머스

SELECT b.ID, IFNULL(a.CHILD_COUNT, 0) as 'CHILD_COUNT'
FROM (SELECT PARENT_ID, COUNT(*) as 'CHILD_COUNT'
FROM ECOLI_DATA
WHERE PARENT_ID IS NOT NUlL
GROUP BY PARENT_ID) as a RIGHT JOIN ECOLI_DATA as b
ON a.PARENT_ID = b.ID
ORDER BY b.ID

 

---

14. 년, 월, 성별 별 상품 구매 회원 수 구하기

• COUNT(DISTINCT(a.USER_ID) -> DISTINCT을 해줘야 함 !! 

https://school.programmers.co.kr/learn/courses/30/lessons/131532

프로그래머스

SELECT YEAR(a.SALES_DATE) as 'YEAR', 
    MONTH(a.SALES_DATE) as 'MONTH', b.GENDER, 
    COUNT(distinct(a.USER_ID)) as 'USERS'
FROM ONLINE_SALE as a LEFT JOIN USER_INFO as b
ON a.USER_ID  = b.USER_ID
WHERE b.GENDER IS NOT NULL
GROUP BY YEAR(a.SALES_DATE), MONTH(a.SALES_DATE), b.GENDER
ORDER BY YEAR, MONTH, b.GENDER

 

---

15. 우유와 요거트가 담긴 장바구니

https://school.programmers.co.kr/learn/courses/30/lessons/62284

프로그래머스

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Milk','Yogurt')
GROUP BY CART_ID
HAVING COUNT(DISTINCT(NAME)) >= 2 
ORDER BY CART_ID

 

---

16. 저자 별 카테고리 별 매출액 집계하기 (⭐️복습하기)

https://school.programmers.co.kr/learn/courses/30/lessons/144856

프로그래머스

SELECT c.AUTHOR_ID, c.AUTHOR_NAME, b.CATEGORY, 
    SUM(SALES*PRICE) as 'TOTAL_SALES'
FROM (SELECT *
FROM BOOK_SALES
WHERE DATE_FORMAT(SALES_DATE, "%Y-%m") = '2022-01') as a
    LEFT JOIN BOOK as b ON a.BOOK_ID = b.BOOK_ID
    LEFT JOIN AUTHOR as c ON b.AUTHOR_ID = c.AUTHOR_ID
GROUP BY c.AUTHOR_ID, c.AUTHOR_NAME, b.CATEGORY
ORDER BY c.AUTHOR_ID, b.CATEGORY desc

 

---

17. 취소되지 않은 진로 예약 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/132204

프로그래머스

SELECT a.APNT_NO, p.PT_NAME, a.PT_NO, b.MCDP_CD, b.DR_NAME, a.APNT_YMD
FROM (SELECT APNT_YMD, PT_NO, MDDR_ID,APNT_NO	
FROM APPOINTMENT
WHERE DATE_FORMAT(APNT_YMD, "%Y-%m-%d") = '2022-04-13'
    AND MCDP_CD = 'CS' AND APNT_CNCL_YN = 'N') as a
    LEFT JOIN DOCTOR as b ON a.MDDR_ID = b.DR_ID
    LEFT JOIN PATIENT as p ON a.PT_NO = p.PT_NO
ORDER BY APNT_YMD

 

---

18. 부서별 평균 연봉 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284529

프로그래머스

SELECT b.DEPT_ID, b.DEPT_NAME_EN, a.AVG_SAL
FROM (SELECT DEPT_ID, ROUND(AVG(SAL),0) as 'AVG_SAL'
FROM HR_EMPLOYEES
GROUP BY DEPT_ID) as a JOIN HR_DEPARTMENT as b
ON a.DEPT_ID = b.DEPT_ID
ORDER BY AVG_SAL desc

 

---

19. 서울에 위치한 식당 목록 출력하기

• Address 조건 주의
  • LIKE %서울특별시%  or LIKE %서울%  -> 틀린 조건
  • LIKE '서울%' 맞는 조건임  -> 데이터 칼럼 값을 볼 수 있어야 함

SELECT a.REST_ID, a.REST_NAME, a.FOOD_TYPE, a.FAVORITES, a.ADDRESS, b.SCORE
FROM (SELECT *
FROM REST_INFO
WHERE ADDRESS LIKE "서울%") as a JOIN (
    SELECT REST_ID, ROUND(AVG(REVIEW_SCORE),2) as 'SCORE'
    FROM REST_REVIEW
    GROUP BY REST_ID) as b
ON a.REST_ID = b.REST_ID
ORDER BY SCORE desc, a.FAVORITES desc

 

---

20.  식품분류별 가장 비싼 식품의 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/131116

프로그래머스

SELECT CATEGORY, PRICE as 'MAX_PRICE', PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN 
    (SELECT CATEGORY, MAX(PRICE) as 'MAX_PRICE'
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY)
ORDER BY MAX_PRICE desc

 

---

21. 5월 식품들의 총매출 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/131117

프로그래머스

SELECT b.PRODUCT_ID,b.PRODUCT_NAME, a.sales * b.PRICE as 'TOTAL_SALES'
FROM (SELECT PRODUCT_ID, SUM(AMOUNT) as 'sales'
FROM FOOD_ORDER
WHERE DATE_FORMAT(PRODUCE_DATE, "%Y-%m") = '2022-05'
GROUP BY PRODUCT_ID) as a JOIN FOOD_PRODUCT as b
ON a.PRODUCT_ID = b.PRODUCT_ID
ORDER BY TOTAL_SALES desc, a.PRODUCT_ID

 

---

22. 조건에 맞는 사용자 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164670

프로그래머스

SELECT USER_ID, NICKNAME, 
    CONCAT(CITY, " ",STREET_ADDRESS1," ",STREET_ADDRESS2) as '전체주소', 
    CONCAT(LEFT(TLNO,3),"-", SUBSTR(TLNO,4,4),"-", RIGHT(TLNO,4)) as '전화번호'
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(*) >= 3)
ORDER BY USER_ID DESC

 

---

23. 잡은 물고기 중 가장 큰 물고기의 길이 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/298515

프로그래머스

SELECT CONCAT(MAX(LENGTH), "cm") as 'MAX_LENGTH'
FROM FISH_INFO

 

---

24. 잔챙이 잡은 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/293258

프로그래머스

SELECT COUNT(*) as 'FISH_COUNT'
FROM FISH_INFO
WHERE LENGTH IS NULL

 

---

25. 상품을 구매한 회원 비율 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

WITH TOTLA_NUMBER AS (
    SELECT COUNT(DISTINCT(USER_ID)) as 'total_num'
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021)
    
SELECT YEAR(SALES_DATE) as 'YEAR', MONTH(SALES_DATE) as 'MONTH', 
    COUNT(DISTINCT USER_ID) as 'PUCHASED_USERS',
    ROUND(COUNT(DISTINCT USER_ID) / b.total_num,1) as 'PUCHASED_RATIO'
FROM ONLINE_SALE JOIN TOTLA_NUMBER as b
WHERE USER_ID IN (SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = 2021)
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR, MONTH

 

---

26. 자동차 대여 기록 별 대여 금액 구하기

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

프로그래머스

SELECT c.HISTORY_ID, 
    ROUND(c.DAILY_FEE * c.time_diff * (1- IFNULL(d.DISCOUNT_RATE,0) * 0.01),0) as 'FEE'
FROM (SELECT HISTORY_ID, a.DAILY_FEE,b.time_diff,
    CASE WHEN b.time_diff >= 7 and b.time_diff < 30 then '7일 이상'
    WHEN b.time_diff >= 30 and b.time_diff < 90 then '30일 이상'
    WHEN b.time_diff >= 90 then '90일 이상'
    ELSE '할인율없음' END as 'DURATION_TYPE'
FROM (SELECT CAR_ID, DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = '트럭') as a JOIN (
    SELECT HISTORY_ID, CAR_ID, DATEDIFF(END_DATE,START_DATE) + 1 as 'time_diff'
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) as b
ON a.CAR_ID = b.CAR_ID) as C LEFT JOIN 
    (SELECT *
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭') as d
ON c.DURATION_TYPE = d.DURATION_TYPE
ORDER BY FEE desc, HISTORY_ID desc

 

---

27.  보호소에서 중성화한 동물

https://school.programmers.co.kr/learn/courses/30/lessons/59045#fn1

프로그래머스

SELECT a.ANIMAL_ID, a.ANIMAL_TYPE, b.NAME
FROM (SELECT ANIMAL_ID,ANIMAL_TYPE,DATETIME
FROM ANIMAL_INS
WHERE SEX_UPON_INTAKE LIKE "Intact%") as a JOIN 
(SELECT ANIMAL_ID,ANIMAL_TYPE,DATETIME, NAME
FROM ANIMAL_OUTS
WHERE SEX_UPON_OUTCOME LIKE "Spayed%" or SEX_UPON_OUTCOME LIKE "Neutered%") as b
ON a.ANIMAL_ID = b.ANIMAL_ID
ORDER BY a.ANIMAL_ID

 

---

28. 특정 세대의 대장균 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/301650

프로그래머스

WITH SECOND AS (SELECT DISTINCT(ID)
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL))

SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID FROM SECOND)
ORDER BY ID

 

---

29.  New Companies (⭐️ 복습하기)

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

New Companies | HackerRank

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 Employee as e
    LEFT JOIN Manager as m ON e.manager_code = m.manager_code
    LEFT JOIN Senior_Manager as s On s.senior_manager_code = e.senior_manager_code
    LEFT JOIN Lead_Manager as l ON l.lead_manager_code = e.lead_manager_code
    LEFT JOIN Company as c ON c.company_code = e.company_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code

---

30. Weather Observation station 18, 19

(1) 18

https://www.hackerrank.com/challenges/weather-observation-station-18/problem?isFullScreen=true

Weather Observation Station 18 | HackerRank

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

 

 

(2) 19 

• 제곱수 함수 -> POW
• 제곱근 함수 -> SQRT

https://www.hackerrank.com/challenges/weather-observation-station-19/problem?isFullScreen=true

Weather Observation Station 19 | HackerRank

SELECT ROUND(SQRT(POW(MAX(LAT_N) - MIN(LAT_N),2) +  POW(MAX(LONG_W) - MIN(LONG_W),2)),4)
FROM STATION

---

31. 3월에 태어난 여성 회원 목록 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131120

프로그래머스

SELECT MEMBER_ID, MEMBER_NAME, GENDER, 
    DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d") as 'DATE_OF_BIRTH'
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 03
AND TLNO IS NOT NULL and GENDER = 'W'
ORDER BY MEMBER_ID asc

 

---

32. 루시와 엘라 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59046

프로그래머스

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

 

---

33. 대여 기록이 존재하는 자동차 리스트 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157341

프로그래머스

SELECT DISTINCT(CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = '세단')
AND MONTH(START_DATE) = 10
ORDER BY CAR_ID desc

 

---

34. 조건에 부합하는 중고거래 상태 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164672

프로그래머스

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE,
    CASE WHEN STATUS = 'SALE' THEN '판매중'
    WHEN STATUS = 'RESERVED' THEN '예약중'
    ELSE '거래완료' END as 'STATUS'
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, "%Y-%m-%d") = '2022-10-05'
ORDER BY BOARD_ID desc

 

---

35. 조건에 맞는 사원 정보 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284527

프로그래머스

WITH HR_GRADE_SUM AS (SELECT EMP_NO, SUM(SCORE) as 'total_score'
FROM HR_GRADE
GROUP BY EMP_NO)

SELECT a.SCORE, a.EMP_NO, b.EMP_NAME, b.POSITION, b.EMAIL
FROM (SELECT total_score as 'SCORE', EMP_NO
FROM HR_GRADE_SUM
WHERE total_score in (SELECT MAX(total_score) FROM HR_GRADE_SUM)) as a 
LEFT JOIN HR_EMPLOYEES as b ON a.EMP_NO = b.EMP_NO

 

---

36. 조건에 부합하는 중고거래 댓글 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164673

프로그래머스

SELECT a.TITLE, a.BOARD_ID, b.REPLY_ID, b.WRITER_ID, b.CONTENTS,
    DATE_FORMAT(CREATED_DATE, "%Y-%m-%d") as 'CREATED_DATE'
FROM (SELECT BOARD_ID, TITLE
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, "%Y-%m") = '2022-10') as a
RIGHT JOIN USED_GOODS_REPLY as b
ON a.BOARD_ID = b.BOARD_ID
WHERE a.BOARD_ID is not null
ORDER BY CREATED_DATE, a.TITLE

 

---

37. 연도별 대장균 크기의 편차 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299310

프로그래머스

SELECT a.YEAR, b.MAX_COL - a.SIZE_OF_COLONY  as 'YEAR_DEV', a.ID 
FROM (SELECT ID, SIZE_OF_COLONY, YEAR(DIFFERENTIATION_DATE) as 'YEAR'
FROM ECOLI_DATA) as a LEFT JOIN (SELECT YEAR(DIFFERENTIATION_DATE) as 'YEAR',
                                 MAX(SIZE_OF_COLONY) as 'MAX_COL'
FROM ECOLI_DATA
GROUP BY YEAR(DIFFERENTIATION_DATE)) as b
ON a.YEAR = b.YEAR
ORDER BY YEAR asc, YEAR_DEV asc

 

---

38. Challenges

https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true

Challenges | HackerRank

WITH MEMBER_COUNT AS (SELECT a.hacker_id, b.name, a.cou_ch
FROM (SELECT hacker_id, COUNT(distinct challenge_id) as 'cou_ch'
FROM Challenges
GROUP BY hacker_id) as a JOIN Hackers as b
ON a.hacker_id = b.hacker_id),
MAX_NUMBER AS  (
    SELECT MAX(cou_ch) as 'max_number'
    FROM MEMBER_COUNT )

SELECT *
FROM MEMBER_COUNT
WHERE cou_ch IN (SELECT cou_ch
FROM MEMBER_COUNT
GROUP BY cou_ch
HAVING COUNT(*) = 1) or cou_ch IN (
    SELECT MAX(cou_ch)
    FROM MEMBER_COUNT ) or cou_ch IN (SELECT max_number FROM MAX_NUMBER )
ORDER BY cou_ch desc, hacker_id

 

---

39. Contest Leaderboard

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true

Contest Leaderboard | HackerRank

SELECT  c.hacker_id, c.name, b.sum_score
FROM (SELECT a.hacker_id, SUM(a.score) as 'sum_score'
FROM (SELECT hacker_id, challenge_id, MAX(score) as 'score'
FROM Submissions
GROUP BY hacker_id, challenge_id ) as a
GROUP BY a.hacker_id) as b LEFT JOIN Hackers as c
ON b.hacker_id = c.hacker_id
WHERE  b.sum_score != 0
ORDER BY b.sum_score desc, c.hacker_id

 

---

40. Top Competitors

https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true

Top Competitors | HackerRank

SELECT b.hacker_id, b.name
FROM (SELECT s.hacker_id, COUNT(distinct s.challenge_id ) as 'count_ch'
FROM Submissions as s
    LEFT JOIN Challenges as c ON s.challenge_id = c.challenge_id
    LEFT JOIN Difficulty as d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score 
GROUP BY s.hacker_id
HAVING COUNT(distinct s.challenge_id ) >= 2) as a JOIN Hackers as b
ON a.hacker_id = b.hacker_id
ORDER BY a.count_ch desc, b.hacker_id

 

---

41. SQL Project Planning

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true

SQL Project Planning | HackerRank

WITH start as (SELECT row_number() over (order by Start_Date) as 'rank_number', Start_Date 
FROM Projects
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects )) ,
end as (SELECT row_number() over (order by End_Date) as 'rank_number', End_Date 
FROM Projects
WHERE End_Date NOT IN (SELECT Start_Date FROM Projects ))
SELECT a.Start_Date, b.End_Date
FROM start  as a JOIN end as b
ON a.rank_number = b.rank_number
ORDER BY DATEDIFF(b.End_Date,a.Start_Date) asc, a.Start_Date

 

 

 

 

제발 상반기 마지막 카드 ... 제발..... 제발 ... . . . .  . . . .