5월 식품들의 총매출 조회하기
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/131117
- 생산일자가 2022년 5월에 해당하는 식품의 ID, NAME, 총매출을 조회하는 SQL문을 작성
- 정렬: 총매출 내림차순 정렬, 식품 ID 오름차순 정렬
MySol)
<hide/>
SELECT PRODUCT_ID, PRODUCT_NAME, SUM(PRICE * AMOUNT) TOTAL_SALES
FROM FOOD_PRODUCT JOIN FOOD_ORDER USING (PRODUCT_ID)
WHERE YEAR(PRODUCE_DATE) = 2022 AND MONTH(PRODUCE_DATE) = 5
GROUP BY PRODUCT_ID
ORDER BY 3 DESC, 1
주문량이 많은 아이스크림 조회하기 - level 4
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/133027
- TEMP 테이블 안에서 정렬을 먼저하고 그 다음에
- 상위 3개만 골라서 FLAVOR만 출력한다.
Sol)
<hide/>
WITH TEMP AS
(
SELECT FLAVOR, SUM(J.TOTAL_ORDER + FH.TOTAL_ORDER)
FROM FIRST_HALF FH JOIN JULY J USING (FLAVOR)
GROUP BY FLAVOR
ORDER BY 2 DESC
)
SELECT FLAVOR
FROM TEMP
LIMIT 3
그룹별 조건에 맞는 식당 목록 출력하기 - level 4
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/131124
- 먼저 from 내부 쿼리를 이용해서 max_tmp 테이블에 최대 개수와 member_id를 추출한다.
- 그리고 해당 member_id와 문제의 테이블에서 id를 비교해서 회원 정보를 반환하도록 한다.
<hide/>
WITH MAX_TMP AS
(
SELECT COUNT(REVIEW_ID) MAX_CNT, MP.MEMBER_ID MAX_MEMBER_ID, MEMBER_NAME
FROM MEMBER_PROFILE MP JOIN REST_REVIEW USING (MEMBER_ID)
GROUP BY MP.MEMBER_ID
ORDER BY 1 DESC
LIMIT 1
)
SELECT MEMBER_NAME MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM MAX_TMP JOIN REST_REVIEW R ON MAX_MEMBER_ID = R.MEMBER_ID
ORDER BY 3, 2
없어진 기록 찾기 - level 3
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59042
- out 테이블에는 존재하지만 in 테이블에는 존재하지 않는 데이터만 출력하면 된다.
- (ANIMAL_ID)를 가지고 사이에 두고 RIGHT JOIN을 한다.
- 그러면 OUT 테이블의 모든 튜플이 출력된다.
- OUT 테이블에는 존재하지만 IN 테이블에는 존재하지 않는 튜플의 ANIMAL_ID에는 null이 들어간다.
- 따라서 WHERE 절에 I.ANIMAL_ID 가 NULL인 경우만 출력해야한다.
- 그러면 (OUT 집합 원소) - (IN 집합과 OUT집합의 교집합 원소) 에 해당하는 값을 출력할 수 있다.
Sol)
<hide/>
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS I RIGHT JOIN ANIMAL_OUTS O USING (ANIMAL_ID)
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID
있었는데요 없었습니다 - level 3
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59043
- 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.
- 날짜도 숫자와 같이 부등호로 비교할 수 있다.
- where절에 조건을 넣어서 비교한다.
Sol)
<hide/>
SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O USING (ANIMAL_ID)
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME
오랜 기간 보호한 동물(1) - level 3
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59044
- TIMESTAMPDIFF(DAY, 시작 날, 끝나는 날): 기간을 입력한 단위인 DAY 로 반환할 수 있다.
- CURTIME(): 현재 시간 (날짜, 시간 포함), (CURDATE()는 날짜까지만 반환)
- COALESCE(DATETIME, CURTIME()): DATETIME가 NULL 인 경우, CURTIME을 반환한다.
Sol)
<hide/>
WITH TMP AS
(
SELECT TIMESTAMPDIFF(DAY, I.DATETIME, COALESCE(O.DATETIME, CURDATE())) DIFF,
I.NAME, I.DATETIME
FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID NOT IN (
SELECT ANIMAL_ID
FROM ANIMAL_OUTS
)
ORDER BY DIFF DESC
LIMIT 3
)
SELECT NAME, DATETIME
FROM TMP
보호소에서 중성화한 동물 - level 4
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59045
- WHERE 절 조건에 IN, LIKE 키워드를 넣을 수도 있지만 간단하게 중성화 여부가 바뀌었는지 아닌지의 여부만 비교했다.
Sol)
<hide/>
SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS USING (ANIMAL_ID)
WHERE SEX_UPON_INTAKE != SEX_UPON_OUTCOME
상품 별 오프라인 매출 구하기 - level 2
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/59042
- 총 매출을 구해야하기 때문에 SUM() 을 이용한다.
Sol)
<hide/>
SELECT PRODUCT_CODE, SUM(PRICE * SALES_AMOUNT) "SALES"
FROM PRODUCT JOIN OFFLINE_SALE USING (PRODUCT_ID)
GROUP BY PRODUCT_CODE
ORDER BY 2 DESC, 1
조건에 맞는 도서과 저자 리스트 출력하기 - level 2
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/144854
- date_format(date, '%y-%m-%d'): date를 원하는 형식으로 바꿀 수 있다.
Sol)
<hide/>
SELECT BOOK_ID, AUTHOR_NAME, date_format(PUBLISHED_DATE, "%Y-%m-%d") PUBLISHED_DATE
FROM BOOK JOIN AUTHOR USING (AUTHOR_ID)
WHERE category = '경제'
ORDER BY 3
상품을 구매한 회원 비율 구하기 - level 5
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/131534
Sol)
<hide/>
WITH
TB_회원가입 AS
(
SELECT YEAR(JOINED) YEAR, MONTH(JOINED) MONTH, USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
),
TB_회원수 AS
(
SELECT COUNT(USER_ID) 모든회원수
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
)
SELECT
YEAR(SALES_DATE) YEAR, MONTH(SALES_DATE) MONTH, COUNT(DISTINCT USER_ID) PUCHASED_USERS,
ROUND(COUNT(DISTINCT USER_ID) / TB_회원수.모든회원수, 1) PUCHASED_RATIO
FROM TB_회원가입
JOIN ONLINE_SALE USING (USER_ID)
JOIN TB_회원수
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE)
ORDER BY YEAR(SALES_DATE), MONTH(SALES_DATE)- 푸는데 오래 걸려서 결국 GPT의 도움을 받았다.
- 거의 정답에 근접했지만 DISTINCT 키워드를 생각하지 못해서 풀지 못했다.
- 2021년에 한해서 회원 정보와 가입연월 정보를 가져오고나서 마지막에 GTOUP BY, ORDER BY를 할 때에 구매 연월 정보가 필요하다.
- 문제 : (2021년 가입한 회원 중에서 상품을 구매한 회원 수) / (2021년 가입 회원 수)
- cf) 즉 ratio가 1이 넘으면 잘못된 로직이다.
- 회원 수를 카운트 하기 위해 반드시 "DISTINCT"를 넣는다.
- 넣지 않으면 같은 USER_ID를 카운트해서 잘못된 값이 나온다.
- 1) TB_회원가입: 회원 가입연월 테이블: YEAR MONTH USER_ID
- 2) TB_회원수: 2021년 가입한 회원 수 구하기, (이 테이블은 그냥 회원 수만 구하는 용도라서 JOIN할 때 따로 조건이 필요없음)
- 3) TB_회원가입, TB_회원수, ONLINE_SALE 세 테이블을 조인한다.
특정 기간동안 대여 가능한 자동차들의 대여 비용 구하기 - level 4
출처 - https://school.programmers.co.kr/learn/courses/30/lessons/157339
Sol)
<hide/>
SELECT C.CAR_ID, C.CAR_TYPE, ROUND(DAILY_FEE * 30 * (1 - (DISCOUNT_RATE /100)) , 0) FEE
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY USING (CAR_ID)
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P USING (CAR_TYPE)
WHERE
P.DURATION_TYPE = "30일 이상"
AND CAR_TYPE IN("세단", "SUV")
AND C.CAR_ID NOT IN (
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE
YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 11
OR YEAR(end_date) = 2022 AND MONTH(end_date) = 11
OR START_DATE < "2022-11-01" AND "2022-12-01" <= END_DATE )
GROUP BY 1 HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY 3 DESC, 2, 1 DESC- 포인트 1) 할인이 적용된 30일 간의 대여 비용만 구하면 된다.
- DATEDIFF를 구할 필요가 없다.
- 포인트 2) 각 차량에 기록에 대한 대여 비용의 합을 구하는게 아니라 단순히 30일 * 하루 비용 * (1 - 할인 비율) 만을 구하고 대여 비용이 50만원 이상 200만 원 이하인 경우를 구하면된다.
- 포인트 3) 11월에 대여 가능한 조건을 구하기 위해 WHERE 절에
WHERE 절에 END_DATE < "2022-11-01" OR "2022-12-01" <= START_DATE- 라는 조건을 준다면 잘못된 쿼리가 나온다.
- 이 WHERE 절은 시작일, 마지막날이 모두 11월에 포함되는 경우만을 포함한다. 따라서, "시작일과 마지막 날 중에 어느 한 쪽만 11월인 경우"가 누락되어있어서 오답이다.
- WHERE절 안에 조건으로
- 1. 시작일이 11월이거나
- 2. 마지막날이 11월이거나
- 3. 시작일과 마지막날이 11월 전체를 포함하거나
- 이렇게 세 가지 경우를 전체로 묶어서 반대의 경우를 WHERE 절 조건으로 줬다.
- 그런데 이렇게 하는 것보다 아래의 서브쿼리가 훨씬 짧고 간단하다.
- 다른 블로그에서 참고하고 조건을 이해하기 힘들었지만 수직선 상에 그려보니까 이해갔다.
- 11월의 하루라도 포함하도록 조건을 주려면 다음과 같이 WHERE 절 조건을 줄 수 있다.
- 이는 START_DATE가 항상 END_DATE보다 이전이라는 성질을 이용했기 때문에 식이 간단해진 것이다.
SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE <= "2022-11-30" AND END_DATE >= "2022-11-01"
회고록
- 드디어 프로그래머스의 SQL문제를 다 풀었다 🙌🙌
- 아무래도 JOIN, GROUP BY 파트가 가장 어려웠던 것 같다.
- 될 것 같은데... 내 머릿속에서는 맞는 로직인데 왜 안될까? 하는 생각을 100번도 넘게 했다 ㅎㅎ
- 그래도 몇 시간씩 매달리다보면 내 답안에서 하나 둘 허점을 찾아서 해결할 수 있었다.
- 가장 기억에 남는 건 "SET" 을 이용해서 새로운 변수를 생성해야했던 문제(행 번호 생성 문제 - 입양 시각 구하기 (2)) 이다. 내 배경지식으로는 도저히 풀 수 없는 문제였다. 이렇게 배경 지식 없이는 못 푸는 경우도 간혹 있기 때문에 정해진 시간을 넘는 경우는 좋은 답안을 보고 이해하는 게 훨씬 효율적인 방법이라고 몸소 느꼈다.
- 앞으로는 Leet code 나 다른 사이트에서 문제를 풀면서 감을 유지하려고 한다!
'컴퓨터 과학 > [프로그래머스 & Leet Code] MySQL 문제 풀이' 카테고리의 다른 글
| [programmers] String, Date (0) | 2023.05.27 |
|---|---|
| [programmers] SUM, MAX, MIN (0) | 2023.05.25 |
| [programmers] IS NULL (0) | 2023.05.22 |
| [programmers] GROUP BY (0) | 2022.11.25 |
| [leetcode] Day 2. SELECT & Order (0) | 2022.11.24 |