Lv.4로 넘어가니 예전에 있던 문제들은 그래도 간단히 풀리는 문제들이 많았지만
새로 생긴 문제들을 상당히 어렵거나 아니면 문제가 답안과 약간 일치하지 않는 문제들이 있어 문제풀이하는데 애를 좀 먹었다.
하지만 결국 다 풀었으니 내가 어렵다고 생각한 문제들을 위주로 업로드 해보도록 하겠다!
Let's Start!
1. 오프라인/온라인 판매 데이터 통합하기
문제 설명
다음은 어느 의류 쇼핑몰의 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블과 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 입니다.
ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며
ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는
각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ONLINE_SALE_ID | INTEGER | FALSE |
| USER_ID | INTEGER | FALSE |
| PRODUCT_ID | INTEGER | FALSE |
| SALES_AMOUNT | INTEGER | FALSE |
| SALES_DATE | DATE | FALSE |
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
OFFLINE_SALE 테이블은 아래와 같은 구조로 되어있으며
OFFLINE_SALE_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는
각각 오프라인 상품 판매 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| OFFLINE_SALE_ID | INTEGER | FALSE |
| PRODUCT_ID | INTEGER | FALSE |
| SALES_AMOUNT | INTEGER | FALSE |
| SALES_DATE | DATE | FALSE |
동일한 날짜, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
문제
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요.
OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요.
결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.
예시
예를 들어 ONLINE_SALE 테이블이 다음과 같고
| ONLINE_SALE_ID | USER_ID | PRODUCT_ID | SALES_AMOUNT | SALES_DATE |
|---|---|---|---|---|
| 1 | 1 | 3 | 2 | 2022-02-25 |
| 2 | 4 | 4 | 1 | 2022-03-01 |
| 4 | 2 | 2 | 2 | 2022-03-02 |
| 3 | 6 | 3 | 3 | 2022-03-02 |
| 5 | 5 | 5 | 1 | 2022-03-03 |
| 6 | 5 | 7 | 1 | 2022-04-06 |
OFFLINE_SALE 테이블이 다음과 같다면
| OFFLINE_SALE_ID | PRODUCT_ID | SALES_AMOUNT | SALES_DATE |
|---|---|---|---|
| 1 | 1 | 2 | 2022-02-21 |
| 4 | 1 | 2 | 2022-03-01 |
| 3 | 3 | 3 | 2022-03-01 |
| 2 | 4 | 1 | 2022-03-01 |
| 5 | 2 | 1 | 2022-03-03 |
| 6 | 2 | 1 | 2022-04-01 |
각 테이블의 2022년 3월의 판매 데이터를 합쳐서, 정렬한 결과는 다음과 같아야 합니다.
| SALES_DATE | PRODUCT_ID | USER_ID | SALES_AMOUNT |
|---|---|---|---|
| 2022-03-01 | 1 | NULL | 2 |
| 2022-03-01 | 3 | NULL | 3 |
| 2022-03-01 | 4 | NULL | 1 |
| 2022-03-01 | 4 | 4 | 1 |
| 2022-03-02 | 2 | 2 | 2 |
| 2022-03-02 | 3 | 6 | 3 |
| 2022-03-03 | 2 | NULL | 1 |
| 2022-03-03 | 5 | 5 | 1 |
Answer
-- 초기 풀이
SELECT DATE_FORMAT(n.SALES_DATE, "%Y-%m-%d") AS SALES_DATE,
n.PRODUCT_ID,
IFNULL(n.USER_ID, 'NULL') AS USER_ID, n.SALES_AMOUNT
FROM ONLINE_SALE n
LEFT JOIN OFFLINE_SALE f
ON n.PRODUCT_ID = f.PRODUCT_ID
WHERE n.SALES_DATE > '2022-02-28 23:59:59'
ORDER BY n.SALES_DATE, n.PRODUCT_ID, n.USER_ID; 일단 두 개의 테이블에서 2022년 3월의 데이터를 가져와야하기 때문에 JOIN을 하여 값을 반환하면 될거라고 생각했다.
하지만 이는 테이블의 구성을 잘 보지 않아 당연히 JOIN을 하여도 값이 이상하게 나왔다.
해당하는 조건을 각 테이블마다 걸은다음 모든 테이블을 결합한 다음 UNION을 사용하여 결합하고 문제에서 요구하는 정렬 방식을 적용해주면 금방 풀 수 있는 문제였다.
식이 다소 복잡해보이지만 하나하나씩 뜯어보면 금방 이해할 수 있을 것이다.
-- 정답
SELECT * FROM
(SELECT
DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') LIKE '2022-03%'
UNION ALL
SELECT
DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID,
CASE WHEN OFFLINE_SALE_ID IS NOT NULL THEN NULL
END AS USER_ID,
SALES_AMOUNT
FROM OFFLINE_SALE
WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') LIKE '2022-03%'
) z
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;2. 식품분류별 가장 비싼 식품의 정보 조회하기
문제 설명
다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블입니다.
FOOD_PRODUCT 테이블은 다음과 같으며
PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.
| Column name | Type | Nullable |
|---|---|---|
| PRODUCT_ID | VARCHAR(10) | FALSE |
| PRODUCT_NAME | VARCHAR(50) | FALSE |
| PRODUCT_CD | VARCHAR(10) | TRUE |
| CATEGORY | VARCHAR(10) | TRUE |
| PRICE | NUMBER | TRUE |
문제
FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요.
이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.
예시
FOOD_PRODUCT 테이블이 다음과 같을 때
| PRODUCT_ID | PRODUCT_NAME | PRODUCT_CD | CATEGORY | PRICE |
|---|---|---|---|---|
| P0018 | 맛있는고추기름 | CD_OL00008 | 식용유 | 6100 |
| P0019 | 맛있는카놀라유 | CD_OL00009 | 식용유 | 5100 |
| P0020 | 맛있는산초유 | CD_OL00010 | 식용유 | 6500 |
| P0021 | 맛있는케첩 | CD_SC00001 | 소스 | 4500 |
| P0022 | 맛있는마요네즈 | CD_SC00002 | 소스 | 4700 |
| P0039 | 맛있는황도 | CD_CN00008 | 캔 | 4100 |
| P0040 | 맛있는명이나물 | CD_CN00009 | 캔 | 3500 |
| P0041 | 맛있는보리차 | CD_TE00010 | 차 | 3400 |
| P0042 | 맛있는메밀차 | CD_TE00001 | 차 | 3500 |
| P0099 | 맛있는맛동산 | CD_CK00002 | 과자 | 1800 |
SQL을 실행하면 다음과 같이 출력되어야 합니다.
| CATEGORY | MAX_PRICE | PRODUCT_NAME |
|---|---|---|
| 식용유 | 6500 | 맛있는산초유 |
| 과자 | 1800 | 맛있는맛동산 |
Answer
SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
AND PRICE IN (SELECT MAX(PRICE)
FROM FOOD_PRODUCT
GROUP BY CATEGORY)
GROUP BY CATEGORY
ORDER BY MAX_PRICE DESC; 이 문제는 식품분류 별로 가격이 제일 비싼 식품의 정보들을 가져오는 것인데 식품분류도 특정한 것을 가져와야한다.
이것은 WHERE절의 IN을 사용하여 가져오면 되지만 이제 가장 비싼 식품 분류들을 가져와야하는데
이것은 WHERE PRICE IN 서브쿼리를 카테고리로 그룹핑하여 가장 비싼 가격을 반환하여 가져온 값을 가져오는 식으로 구현하였다.
3. 년, 월, 성별 별 상품 구매 회원 수 구하기
문제 설명
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다.
USER_INFO 테이블은 아래와 같은 구조로 되어있으며
USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| USER_ID | INTEGER | FALSE |
| GENDER | TINYINT(1) | TRUE |
| AGE | INTEGER | TRUE |
| JOINED | DATE | FALSE |
GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.
ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며, ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
| Column name | Type | Nullable |
|---|---|---|
| ONLINE_SALE_ID | INTEGER | FALSE |
| USER_ID | INTEGER | FALSE |
| PRODUCT_ID | INTEGER | FALSE |
| SALES_AMOUNT | INTEGER | FALSE |
| SALES_DATE | DATE | FALSE |
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
문제
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요.
결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
예시
예를 들어 USER_INFO 테이블이 다음과 같고
| USER_ID | GENDER | AGE | JOINED |
|---|---|---|---|
| 1 | 1 | 26 | 2021-06-01 |
| 2 | NULL | NULL | 2021-06-25 |
| 3 | 0 | NULL | 2021-06-30 |
| 4 | 0 | 31 | 2021-07-03 |
| 5 | 1 | 25 | 2021-07-09 |
| 6 | 1 | 33 | 2021-07-14 |
ONLINE_SALE 테이블이 다음과 같다면
| ONLINE_SALE_ID | USER_ID | PRODUCT_ID | SALES_AMOUNT | SALES_DATE |
|---|---|---|---|---|
| 1 | 1 | 54 | 1 | 2022-01-01 |
| 2 | 1 | 3 | 2 | 2022-01-25 |
| 3 | 4 | 34 | 1 | 2022-01-30 |
| 4 | 6 | 253 | 3 | 2022-02-03 |
| 5 | 2 | 31 | 2 | 2022-02-09 |
| 6 | 5 | 35 | 1 | 2022-02-14 |
| 7 | 5 | 57 | 1 | 2022-02-18 |
2022년 1월에 상품을 구매한 회원은 USER_ID 가 1(GENDER=1), 4(GENDER=0)인 회원들이고,2022년 2월에 상품을 구매한 회원은 USER_ID 가 2(GENDER=NULL), 5(GENDER=1), 6(GENDER=1)인 회원들 이므로,
년, 월, 성별 별로 상품을 구매한 회원수를 집계하고, 년, 월, 성별을 기준으로 오름차순 정렬하면 다음과 같은 결과가 나와야 합니다.
| YEAR | MONTH | GENDER | USERS |
|---|---|---|---|
| 2022 | 1 | 0 | 1 |
| 2022 | 1 | 1 | 1 |
| 2022 | 2 | 1 | 2 |
Answer
SELECT YEAR(o.SALES_DATE) AS 'YEAR', MONTH(o.SALES_DATE) AS 'MONTH',
u.GENDER, COUNT(DISTINCT o.USER_ID) AS USERS
FROM USER_INFO u
INNER JOIN ONLINE_SALE o
ON u.USER_ID = o.USER_ID
WHERE u.GENDER IN (0,1)
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3; 이 문제는 년, 월, 성별 별로 상품을 구매한 회원수를 집계해야하는데
년과 월은 YEAR, MONTH 함수를 사용하여 불러오고 USER_ID를 중복값을 제거하여 가져오고
성별을 가져오는데 NULL 값을 제거해야하니 IS NOT NULL을 사용해도 되겠지만 쿼리가 좀 길어질 것 같아 WHERE절에 IN으로 해결하였다.
4. 주문량이 많은 아이스크림들 조회하기
문제 설명
다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과 7월의 아이스크림 주문 정보를 담은 JULY 테이블입니다.
FIRST_HALF 테이블 구조는 다음과 같으며,
SHIPMENT_ID, FLAVOR, TOTAL_ORDER는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냅니다.
FIRST_HALF 테이블의 기본 키는 FLAVOR입니다.
FIRST_HALF테이블의 SHIPMENT_ID는 JULY테이블의 SHIPMENT_ID의 외래 키입니다.
| NAME | TYPE | NULLABLE |
|---|---|---|
| SHIPMENT_ID | INT(N) | FALSE |
| FLAVOR | VARCHAR(N) | FALSE |
| TOTAL_ORDER | INT(N) | FALSE |
JULY 테이블 구조는 다음과 같으며, SHIPMENT_ID, FLAVOR, TOTAL_ORDER 은 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 7월 아이스크림 총주문량을 나타냅니다. JULY 테이블의 기본 키는 SHIPMENT_ID입니다.
JULY테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키입니다.
7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다.
| NAME | TYPE | NULLABLE |
|---|---|---|
| SHIPMENT_ID | INT(N) | FALSE |
| FLAVOR | VARCHAR(N) | FALSE |
| TOTAL_ORDER | INT(N) | FALSE |
문제
7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.
예시
예를 들어 FIRST_HALF 테이블이 다음과 같고
| SHIPMENT_ID | FLAVOR | TOTAL_ORDER |
|---|---|---|
| 101 | chocolate | 3200 |
| 102 | vanilla | 2800 |
| 103 | mint_chocolate | 1700 |
| 104 | caramel | 2600 |
| 105 | white_chocolate | 3100 |
| 106 | peach | 2450 |
| 107 | watermelon | 2150 |
| 108 | mango | 2900 |
| 109 | strawberry | 3100 |
| 110 | melon | 3150 |
| 111 | orange | 2900 |
| 112 | pineapple | 2900 |
JULY테이블이 다음과 같다면
| SHIPMENT_ID | FLAVOR | TOTAL_ORDER |
|---|---|---|
| 101 | chocolate | 520 |
| 102 | vanilla | 560 |
| 103 | mint_chocolate | 400 |
| 104 | caramel | 460 |
| 105 | white_chocolate | 350 |
| 106 | peach | 500 |
| 107 | watermelon | 780 |
| 108 | mango | 790 |
| 109 | strawberry | 520 |
| 110 | melon | 400 |
| 111 | orange | 250 |
| 112 | pineapple | 200 |
| 208 | mango | 110 |
| 209 | strawberry | 220 |
7월 아이스크림 총주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하면 strawberry(520 + 220 + 3,100 = 3,840), mango(790 + 110 + 2,900 = 3,800), chocolate(520 + 3,200 = 3,720) 순입니다. 따라서 SQL 문을 실행하면 다음과 같이 나와야 합니다.
| FLAVOR |
|---|
| strawberry |
| mango |
| chocolate |
Answer
SELECT j.FLAVOR FROM FIRST_HALF f
LEFT JOIN
(SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY
GROUP BY FLAVOR
) j
ON j.FLAVOR = f.FLAVOR
ORDER BY (f.TOTAL_ORDER + j.TOTAL_ORDER) DESC
LIMIT 3; 간단한 문제만큼 Lv.4에서 보기 힘든 간단한 문제였다.
여기에서의 관건은 JOIN절에 Sub-Query를 작성하는 것과
ORDER BY절에 각 column을 더한 값을 정렬로 사용할 수 있다는 것이다.
To be continued...
