Open Brain

ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL 문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

# 문제에서 요구하는 것: 동일한 상품을 재구매한 USER_ID와 상품 ID 구하기, 중복되는 값 찾기
# 스텝: GROUP BY PRODUCT_ID -> HAVING >= 2, GROUP BY는 중복을 제거함

SELECT
    USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(PRODUCT_ID) >= 2
ORDER BY USER_ID ASC, PRODUCT_ID DESC

GROUP BY USER_ID, PRODUCT_ID를 하면 중복된 레코드끼리 모이게 된다. 출력 결과 상으로는 GROUP BY는 중복된 값을 표현하지 않기에 레코드가 보이지 않지만 같은 레코드로 묶여 있는 상태이다. HAVING COUNT(PRODUCT_ID) >= 2를 하면 각 그룹 별로 레코드를 센 후에 필터링한다.

REST_INFO 테이블에서 음식종류 별로 즐겨찾기 수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기 수를 조회하는 SQL문을 작성해주세요. 이 때, 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

오답코드

SELECT
    FOOD_TYPE,
    REST_ID,
    REST_NAME,
    MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC

위 코드를 실행하면 GROUP BY로 FOOD_TYPE 별 FAVORITES의 최대값은 가져올 수 있지만 REST_ID와 REST_NAME은 정확히 가져올 수 없다. GROUP BY가 임의로 아무 행이나 가져오게 된다.

GROUP BY가 그룹 기준 컬럼인 FOOD_TYPE과 집계함수 MAX에 사용된 FAVORITES는 정확히 인지하지만 나머지 컬럼은 인지하지 못하기 때문이다.

따라서 정확히 FOOD_TYPE과 MAX(FAVORITES)를 고른 후에 일치하는 행을 통째로 가져와야 한다.

정답 코드

SELECT
    FOOD_TYPE,
    REST_ID,
    REST_NAME,
    FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (
SELECT FOOD_TYPE, MAX(FAVORITES)
FROM REST_INFO
GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC

FOOD_TYPE 별로 좋아요 수가 최대인 행을 뽑으려면 가령 'WHERE FOOD_TYPE = '한식' AND MAX(FAVORITES)' 이런 식으로 짤 수 있다. 하지만 이런 코드는 굉장히 비효율적이다. FOOD_TYPE이 100가지면 코드를 100번 반복해야 하고, FOOD_TYPE이 변경되면 다시 찾아서 수정해야 한다. 따라서 아래와 같이 SUBQUERY로 FOOD_TYPE 별 최대 좋아요 수를 뽑고, 그 행을 기준으로 전체 테이블에서 필터링하는 것이 더 효과적이다.

문제: 머쓱이는 태어난지 6개월 된 조카를 돌보고 있습니다. 이 조카는 아직 "aya", "ye", "woo", "ma" 네 가지 발음을 최대 한 번씩 사용해 조합한(이어 붙인) 발음 밖에 하지 못합니다. 문자열 배열 babbling이 매개변수로 주어질 때, 머쓱이의 조카가 발음할 수 있는 단어의 개수를 return하도록 solution 함수를 완성해주세요.

아이디어

1. babbling 배열 안에 있는 단어들 안에 조카가 할 수 있는 단어는 공백으로 구분한다.
2. 공백을 모두 없애준다.
3. 문자열이 ""이라면 answer를 1 증가시킨다.
   • 만약 "ayaaya"라는 단어가 있다면 " aya"로 될 것이고, 공백을 없애면 " aya"가 된다. 최대 1번 사용 조건에 위배되기 때문에 문자열이 ""일 때만 answer를 증가시켜야 한다.

# babbling 각각 발음 안에 조카가 가능한 발음은 공백 처리
# 전부 공백이라면 가능한 발음이니 answer += 1

def solution(babbling):
    answer = 0
    nephew = ['aya', 'ye', 'woo', 'ma']
    for word in babbling:
        tmp_word = word
        for n in nephew:
            tmp_word = tmp_word.replace(n, " ")
        tmp_word = tmp_word.replace(" ","")
        if tmp_word == "":
            answer += 1
    return answer

문제: 문자열 my_str과 n이 매개변수로 주어질 때, my_str을 길이 n씩 잘라서 저장한 배열을 return 하도록 solution 함수를 완성해주세요.

나의 코드

def solution(my_str, n):
    answer = []
    length = 0
    if len(my_str) % n == 0:
        length = len(my_str) // n
    else:
        length = len(my_str) // n + 1
    tmp = 0 # 포인터
    for i in range(1, length):
        answer.append(my_str[(tmp):(n*i)])
        tmp += n
    answer.append(my_str[tmp:])
    return answer

포인터를 생성해서 해결했다. n 지점을 my_string에서 기억하여 문자열을 구분했고, 문자열의 길이가 n으로 나눠 떨어질 때와 아닐때의 반복문 회수가 달라져야 하기 때문에 length의 값도 구분했다.

간결한 코드

def solution(my_str, n):
    answer = []
    for i in range(0, len(my_str), n):
        answer.append(my_str[i:i+n])
    return answer

for 문에 3번째 인자로 n을 넣어서 알아서 필요한만큼 건너뛸 수 있게 했다. 슬라이싱의 경우 list out of range 에러가 발생하지 않아 더욱 간결하게 작성할 수 있다. 

문제: CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차 종류 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

CASE WHEN을 사용해서 DURATION_TYPE이 아닌 실제 빌린 일 수를 기준으로 할인율을 적용해야 하는게 핵심 포인트다. 

오답 코드

WITH CAR_RENTAL AS (
SELECT
    C.CAR_TYPE,
    C.DAILY_FEE,
    H.HISTORY_ID,
    DATEDIFF(H.END_DATE, H.START_DATE) +1 AS DATE_DIFF
FROM 
    CAR_RENTAL_COMPANY_CAR AS C
INNER JOIN 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
ON
    C.CAR_ID = H.CAR_ID
INNER JOIN
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P
ON 
    H.CAR_TYPE = P.CAR_TYPE)

SELECT
    HISTORY_ID,
    CASE
        WHEN DATE_DIFF BETWEEN 7 AND 29 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '7일 이상'))
        
        WHEN DATE_DIFF BETWEEN 30 AND 89 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '30일 이상'))
        
        WHEN DATE_DIFF >= 90 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상'))
        ELSE  ROUND(DAILY_FEE * DATE_DIFF)
    END AS FEE
FROM
    CAR_RENTAL
WHERE
    CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

WITH로 만든 CTE에 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 데이터 셋을 결합하게 되면 HISTORY_ID 별로 여러 할인율의 행이 생성된다.

예를 들어 HISTORY_ID가 111인 차량이 여러 번 대여되어 5%, 8%, 10%의 할인율 기록이 있을 때 CTE에 하나의 HISTORY_ID 행이 총 3개의 행으로 복제 된다.

이렇게 되면 CASE WHEN에서 SUBQUERY를 사용해서 할인율 정보를 가져올 때 하나의 HISTORY_ID에 있는 할인율 정보가 아닌 여러 HISTORY_ID에 있는 할인율 정보(예: 7%)를 가져오게 된다.

이 경우, Subquery returns more than 1 row 에러가 발생한다. Subquery를 사용해서 행별 값을 하나씩 계산해야 되는데 여러 HISTORY_ID가 반환되면 동시에 연산을 할 수 없는 Subquery는 에러가 발생한다.

에러를 방지하기 위해 CTE에서 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 제외하고, Subquery에서 직접 참조할 수 있게 변경만 하면 된다.

정답 코드

WITH CAR_RENTAL AS (
SELECT
    C.CAR_TYPE,
    C.DAILY_FEE,
    H.HISTORY_ID,
    DATEDIFF(H.END_DATE, H.START_DATE) +1 AS DATE_DIFF
FROM 
    CAR_RENTAL_COMPANY_CAR AS C
INNER JOIN 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
ON
    C.CAR_ID = H.CAR_ID)

SELECT
    HISTORY_ID,
    CASE
        WHEN DATE_DIFF BETWEEN 7 AND 29 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '7일 이상'))
        
        WHEN DATE_DIFF BETWEEN 30 AND 89 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '30일 이상'))
        
        WHEN DATE_DIFF >= 90 THEN ROUND(DAILY_FEE * DATE_DIFF * (
        SELECT (100 - DISCOUNT_RATE) / 100
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE CAR_TYPE = '트럭' AND DURATION_TYPE = '90일 이상'))
        ELSE  ROUND(DAILY_FEE * DATE_DIFF)
    END AS FEE
FROM
    CAR_RENTAL
WHERE
    CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC

WITH CTE에서 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 없애고 Subquery에 직접 참조하게 추가하면 끝!

데이터를 결합할 때 어떤 형식으로 결합되는지, 문제에 적합하게 결합되는지를 확인해야 한다.

문제: CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV'인 자동차 중 2022년 11월 1일과 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL 문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

-- CAR_RENTAL_COMPANY_CAR: 현재 대여 중
-- CAR_RENTAL_COMPANY_RENTAL_HISTORY: 대여 기록 정보
-- CAR_RENTAL_COMPANY_DISCOUNT_PLAN: 할인 정보

-- WHERE로 세단 또는 SUV, 2022년 11월 1일부터 11월 30일
-- 30일 할인율 적용한 50만원 ~ 200만원 미만
-- WITH 구문으로 필요한 INNER JOIN 테이블 만들기
-- 테이블에서 할인률 적용한 대여 금액 구하기

WITH CAR_JOIN_T AS (
    SELECT C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE, H.START_DATE, H.END_DATE, P.DURATION_TYPE, P.DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_CAR AS C
    LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
    ON C.CAR_ID = H.CAR_ID
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P
    ON C.CAR_TYPE = P.CAR_TYPE)
    
SELECT
    DISTINCT CAR_ID,
    CAR_TYPE,
    ROUND(DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30) AS FEE
FROM CAR_JOIN_T
WHERE CAR_TYPE IN ('세단', 'SUV')
AND DURATION_TYPE = '30일 이상'
AND CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')
AND DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 >= 500000
AND DAILY_FEE * (1 - DISCOUNT_RATE / 100) * 30 < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

많은 조건으로 필터링해야 하는 문제다. 이 문제의 경우 아래와 같이 코드를 작성했을 때 문제가 발생했다.

AND CAR_ID NOT IN (START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01')

오류: CAR_ID 목록이 아닌 TRUE/FALSE의 논리 연산 결과가 반환되서 정확한 CAR_ID를 필터링할 수 없다.

SQL에서 TRUE면 1, FALSE면 0이 반환되기 때문에 코드 결과는 CAR_ID가 1이거나 0이 아닌 결과가 반환되어 사실상 모든 CAR_ID를 선택한다. 따라서 CAR_ID 목록 괄호 안에 전달하기 위해 SUB QUERY를 사용해서 일차적으로 CAR_ID를 뽑아야 한다.

문제: 보호서에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL 문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

논리구조

• 데이터셋에는 없어도 결과에는 0부터 23이 잇어야 됨 -> WITH 구문으로 임시 테이블 생성
• 임시테이블과 ANIMAL_OUTS의 HOUR를 KEY로 해서 결합
• 시간대를 만들기 위해 HOUR 사용
• HOUR 별로 ANIMAL_ID를 GROUP BY

오답 코드

SELECT
    HOUR(DATETIME) AS HOUR,
    COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
ORDER BY HOUR

위 코드를 실행하면 모든 시간대를 나타낼 수 없다는 문제가 있다. 기존 데이터셋에는 0 ~ 23시까지 모든 시간대가 있는 것이 아니고, 중간에 비어있는 시간대도 있기 때문에 문제에서 요구하는 24시간을 표현할 수 없다.

정답 코드

WITH RECURSIVE OUT_HOUR AS (
    SELECT 0 AS HOUR_VALUE
    UNION ALL
    SELECT HOUR_VALUE +1
    FROM OUT_HOUR
    WHERE HOUR_VALUE < 23)
    
SELECT
    H.HOUR_VALUE AS HOUR,
    COUNT(A.ANIMAL_ID) AS COUNT
FROM OUT_HOUR AS H
LEFT JOIN ANIMAL_OUTS AS A
ON H.HOUR_VALUE = HOUR(A.DATETIME)
GROUP BY HOUR
ORDER BY HOUR

문제에서 요구하는 0 ~ 23시 열을 만들기 위해 WITH 구문으로 하나의 series를 만들어야 한다. 0에서 23까지 값이 하나씩 증가시키기 위해 RECURSIVE를 사용했다. 이 때 WHERE 조건을 걸어 23 이하에서 멈추게 해야 한다. 22에서 멈춰야 마지막으로 1을 더했을 때 정확히 23에서 값이 끝나게 된다.

WITH 구문으로 필요한 CTE를 만든 후에는 ANIMAL_OUTS와 LEFT_JOIN을 사용해서 결합하면 된다. ANIMAL_OUTS에는 없는 시간대는 0으로 채우기 위해 LEFT_JOIN을 사용해야 한다.

중첩 서브쿼리로 조건에 맞는 행을 필터링하는 문제다.

필요 정보

-- KEY 값은 MEMEBER_ID
-- 어떤 JOIN을 사용해야 할까?: 리뷰를 작성한 회원만 확인할 수 있도록 INNER JOIN
-- 서브쿼리로 가장 많은 리뷰를 적은 MEMBER_ID 파악
-- WHERE를 사용해서 MEMBER_ID가 일치하는 행의 NAME, TEXT, DATE 도출

첫번째 서브쿼리

리뷰 데이터 테이블에 있는 멤버 아이디 별 리뷰 개수 구하기

SELECT
    MEMBER_ID, COUNT(*) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID

두번째 서브쿼리

최대 리뷰 개수 구하기

SELECT
    MAX(CNT)
FROM
(SELECT
    MEMBER_ID, COUNT(*) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID) AS T

세번째 서브쿼리

최대 리뷰 개수와 동일한 리뷰를 작성한 MEMBER_ID 찾기

SELECT
    MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
HAVING COUNT(MEMBER_ID) =  # MEMBER_ID가 MAX(CNT)와 동일한 것만 추출한다.
(SELECT
    MAX(CNT)
FROM
(SELECT
    MEMBER_ID, COUNT(*) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID) AS T)

네번째 서브쿼리

위에서 구한 MEMBER_ID와 동일한 MEMBER_ID들의 고객명, 리뷰 텍스트, 날짜 구하기

SELECT
    MP.MEMBER_NAME,
    RR.REVIEW_TEXT,
    DATE_FORMAT(RR.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS MP
JOIN REST_REVIEW AS RR
ON MP.MEMBER_ID = RR.MEMBER_ID
WHERE RR.MEMBER_ID IN
(SELECT
    MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
HAVING COUNT(MEMBER_ID) =
(SELECT
    MAX(CNT)
FROM
(SELECT
    MEMBER_ID, COUNT(*) AS CNT
FROM REST_REVIEW
GROUP BY MEMBER_ID) AS T))
ORDER BY REVIEW_DATE, REVIEW_TEXT