[SQL] QCC - 4회차

Hyunjun Kim·6일 전

SQL

목록 보기

44/44

시험 : 2024. 12. 13. (금) 11:00 ~ 12:00 (1시간)
해설 : 2024. 12. 13. (금) 14:00 ~ 15:00

[4기] QCC - 4회차

👉 제출 안내

진행 시간 : 1시간
해설 : 2024. 12. 13. (금) 14:00 ~ 15:00

선행 커맨드: use qcc;

문제 1 `(下)`

테이블 설명 :

stores 테이블은 각 매장에 대한 정보를 담고 있습니다. 테이블 구조는 다음과 같으며, STORE_NAME, REGION_NAME, SALES, EMPLOYEES, OPEN_DATE, TYPE은
각각 매장 ID, 지역 이름, 매출, 직원 수, 개점일, 매장 유형을 나타냅니다.

컬럼명	타입	설명
STORE_ID	VARCHAR	매장 ID (PK)
REGION_NAME	VARCHAR	지역 이름
SALES	NUMERIC	매출
EMPLOYEES	INT	직원 수
OPEN_DATE	DATE	개점일
TYPE	VARCHAR	매장 유형

분석해야 할 내용은 다음과 같습니다 :

지역별로 매출이 가장 높은 매장을 조회하는 SQL 문을 작성해주세요.
단, 해당 지역에 매장이 두 개 이상인 경우만 결과에 포함해주세요.
결과는 지역 이름을 기준으로 오름차 정렬해주세요.

출력 값 예시

stores 테이블이 다음과 같다면 :

STORE_ID	REGION_NAME	SALES	EMPLOYEES	OPEN_DATE	TYPE
1	Seoul	1000.5	10	5/1/2020	Retail
2	Seoul	1500.75	15	3/15/2019	Retail
3	Seoul	100.00	5	3/12/2020	Retail
4	Busan	2000	20	6/10/2021	Wholesale
5	Daegu	1200.25	12	7/30/2018	Retail
6	Daegu	900	8	9/20/2020	Retail
7	Incheon	500	5	1/15/2022	Retail

Seoul 지역에는 매장이 3 개 있으며, 매출이 가장 높은 매장은 STORE_ID 2입니다.
Busan 지역에는 매장이 하나만 있습니다. 따라서 결과에 포함되지 않습니다.
Daegu 지역에는 매장이 2 개 있으며, 매출이 가장 높은 매장은 STORE_ID 5입니다.
Incheon 지역에는 매장이 하나만 있습니다. 따라서 결과에 포함되지 않습니다.

다음과 같이 결과 출력이 되어야 합니다.
(해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)

region_name	highest_sales
Daegu	1200.25
Seoul	1500.75

문제 2 `(中)`

테이블 설명 :

payments 테이블은 사용자의 결제 정보를 포함합니다. 테이블 구조는 다음과 같으며,
ID, USER_ID, AMOUNT, PAY_DATE, 그리고 PAYMENT_TYPE은
각각 결제 ID, 사용자 ID, 결제 금액, 결제 날짜, 결제 유형(카드, 현금 등)을 나타냅니다.

컬럼명	타입	설명
ID	INT	결제 ID (PK)
USER_ID	VARCHAR	사용자 ID
AMOUNT	INT	결제 금액
PAY_DATE	DATETIME	결제 날짜
PAYMENT_TYPE	INT	결제 유형 (0: 현금, 1:카드)

orders 테이블은 사용자의 상품 배송 정보를 포함합니다. 테이블 구조는 다음과 같으며,
ID, USER_ID, ORDER_DATE, 그리고 ITEM은
각각 주문 ID, 사용자 ID, 주문 날짜, 주문한 상품명을 나타냅니다.
해당 테이블의 USER_ID 는 payments 테이블의 USER_ID랑 동일합니다.

컬럼명	타입	설명
ID	INT	주문 ID (PK)
USER_ID	VARCHAR	사용자 ID
ORDER_DATE	DATETIME	주문 날짜
ITEM	VARCHAR	주문한 상품명

분석해야 할 내용은 다음과 같습니다 :

최근 특정 사용자들이 결제를 하지 않고 상품을 주문하거나,
결제를 하지 않은 시점에 이미 상품을 주문하는 버그가 발견되었습니다. 🐞
해당 버그를 악용한 사용자를 파악하기 위해 SQL 문을 작성해주세요.
다음 조건에 해당되는 사용자 수를 출력해주세요 :

버그 1: 결제를 하지 않고 상품을 주문한 사용자
버그 2: 첫 번째 결제일보다 이전에 상품을 주문한 사용자

출력 값 예시

payments 테이블이 다음과 같고 :

ID	USER_ID	AMOUNT	PAY_DATE	PAYMENT_TYPE
1	user1	3000	2023-01-23 10:00:00	0
2	user3	5000	2023-01-23 14:00:00	1
3	user5	7000	2023-02-23 12:00:00	0

orders 테이블이 다음과 같다면 :

ID	USER_ID	ORDER_DATE	ITEM
1	user1	2023-02-23 09:30:00	Laptop
2	user2	2023-01-23 15:45:00	Smartphone
3	user4	2023-01-23 17:20:00	Headphones
4	user5	2023-01-23 08:00:00	Monitor

user1은 결제 후 상품을 주문했으므로 버그와 무관합니다.
user2는 결제를 하지 않고 상품을 주문했습니다. 버그를 악용했습니다.
user4는 결제를 하지 않고 상품을 주문했습니다. 버그를 악용했습니다.
user5는 첫 번째 결제일 이전에 상품을 주문했습니다. 버그를 악용했습니다.

다음과 같이 결과 출력이 되어야 합니다.
(해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)

cnt
3

문제 3 `(上)`

테이블 설명

cart_products 테이블은 쇼핑 카트에서 주문된 아이템에 대한 정보를 담고 있습니다.
테이블 구조는 다음과 같으며, ID, CART_ID, NAME, PRICE는
각각 제품 ID, 주문 번호, 제품 이름, 개별 제품 가격을 나타냅니다.

컬럼명	타입	설명
ID	INT	제품 ID
CART_ID	INT	주문 번호
NAME	VARCHAR	제품 이름
PRICE	INT	제품 가격 (원 단위)

분석해야 할 내용은 다음과 같습니다 :

데이터 분석팀은 고객이 특정 상품 X를 구매했을 때 상품 Y도 함께 구매할 확률을 분석하고자 합니다.
이를 위해, 쇼핑 카트 데이터에서 서로 다른 두 제품 X와 Y가 같은 주문(CART_ID)에 포함된 주문 수를 확인하려고 합니다.

제품 X와 Y가 같은 주문에 포함된 경우를 계산합니다.
두 제품은 서로 다른 이름이어야 하며, 한 쌍의 경우(예: Coffee와 Sausages)는 다른 순서(예: Sausages와 Coffee)로도 포함됩니다.
결과는 각 제품 쌍과 해당 제품이 함께 포함된 주문 수를 반환해야 합니다.
제품 이름 X와 Y를 기준으로 알파벳 순으로 오름차순 정렬합니다.

출력 값 예시

cart_products 테이블이 다음과 같다면 :

ID	CART_ID	NAME	PRICE
1	1	Coffee	3000
2	1	Sausages	4000
3	1	Vegetable	2000
4	2	Coffee	3000
5	2	Bread	1500
6	2	Sausages	4000
7	3	Vegetable	2000
8	3	Bread	1500

다음과 같이 결과 출력이 되어야 합니다.
(해당 테이블은 예시이며, 실제 정답과 다를 수 있습니다.)

name_x	name_y	orders
Bread	Coffee	1
Bread	Sausages	1
Bread	Vegetable	1
Coffee	Bread	1
Coffee	Sausages	2
Coffee	Vegetable	1
Sausages	Bread	1
Sausages	Coffee	2
Sausages	Vegetable	1
Vegetable	Bread	1
Vegetable	Coffee	1
Vegetable	Sausages	1

내 풀이

1.

오답

SELECT REGION_NAME, MAX(SALES) 
FROM stores s
GROUP BY REGION_NAME
ORDER BY 1

난이도는 하 였지만 뒤에 있는 문제들 보다 어렵게 느껴졌다.
그 이유는 REGION_NAME 으로 GROUP BY 했을 경우 SALES의 최대 값을 구하기는 쉬웠지만
그 이후의 조건들을 만족하기 어려웠는데. GROUPBY 한 쿼리를 서브쿼리나 CTE로 만든다고 한들
이미 GROUP BY 되어버린 STORE_ID 컬럼들이 문제였다.

정답 풀이

SELECT REGION_NAME, MAX(SALES) highest_sales
FROM stores s
GROUP BY REGION_NAME
HAVING COUNT(DISTINCT STORE_ID) >= 2
ORDER BY 1

HAVING 을 사용해 간단하게 해결할 수 있었다.
HAVING SELECT 에서 고르지 않은 컬럼도 조회할 수 있다는 점

2.

오답

SELECT *
FROM (
SELECT o.USER_ID
FROM payments p RIGHT JOIN orders o
ON p.USER_ID = o.USER_ID
WHERE p.USER_ID IS NULL
UNION ALL
SELECT p.USER_ID
FROM  (SELECT USER_ID, MIN(PAY_DATE) min_pay_date
FROM payments p 
GROUP BY USER_ID ) p LEFT JOIN orders o
ON p.USER_ID = o.USER_ID
WHERE p.min_pay_date > o.ORDER_DATE
) a

SQL Query 결과에 중복값을 생각하지 못했다.
UNION ALL -> UNION 으로 변경!

정답 풀이

SELECT COUNT(*)
FROM (
SELECT o.USER_ID
FROM payments p RIGHT JOIN orders o
ON p.USER_ID = o.USER_ID
WHERE p.USER_ID IS NULL
UNION 
SELECT p.USER_ID
FROM  (SELECT USER_ID, MIN(PAY_DATE) min_pay_date
FROM payments p 
GROUP BY USER_ID ) p LEFT JOIN orders o
ON p.USER_ID = o.USER_ID
WHERE p.min_pay_date > o.ORDER_DATE
) a

튜터님 정답

WITH first_payment AS (
    SELECT 
        USER_ID, 
        MIN(PAY_DATE) AS FIRST_PAY_DATE
    FROM qcc.payments
    GROUP BY USER_ID
)
SELECT 
   COUNT(DISTINCT o.USER_ID) cnt 
FROM qcc.orders o
LEFT JOIN first_payment fp
ON o.USER_ID = fp.USER_ID
WHERE fp.USER_ID IS NULL 
   OR o.ORDER_DATE < fp.FIRST_PAY_DATE

3.

정답

SELECT c1.name as name_x, c2.NAME  as name_y , COUNT(*) orders
FROM cart_products c1 JOIN cart_products c2
ON c1.CART_ID = c2.CART_ID AND c1.NAME != c2.NAME
GROUP BY 1,2
ORDER BY 1,2

만약중복값을 없앤 결과를 얻고자 할 때에는 조건문을 추가하면 되는데
조건문 사용에 있어서 주의해야 할 점이 있다.

잘못된 예시.

SELECT c1.name as name_x, c2.NAME  as name_y , COUNT(*) orders
FROM cart_products c1 JOIN cart_products c2
ON c1.CART_ID = c2.CART_ID AND c1.NAME != c2.NAME
GROUP BY 1,2
ORDER BY 1,2

결과

name_x	name_y	orders
Bread	Butter	3
Bread	Coffee	3
Bread	Milk	5
Bread	Sausages	4
Bread	Vegetable	3
Butter	Bread	3
Butter	Coffee	2
Butter	Milk	3
Butter	Sausages	2
Butter	Vegetable	3
Coffee	Bread	3
Coffee	Butter	2
Coffee	Milk	4
Coffee	Sausages	2
Coffee	Vegetable	1
Milk	Bread	5
Milk	Butter	3
Milk	Coffee	4
Milk	Sausages	5
Milk	Vegetable	4
Sausages	Bread	4
Sausages	Butter	2
Sausages	Coffee	2
Sausages	Milk	5
Sausages	Vegetable	4
Vegetable	Bread	3
Vegetable	Butter	3
Vegetable	Coffee	1
Vegetable	Milk	4
Vegetable	Sausages	4

의도와는 다르게 중복된 결과가 나오게 된다.
c1.NAME != c2.NAME 조건이 아닌
c1.name > c2.name 조건을 넣어주게 되면 중복이 없는 결과를 얻을 수 있다.

SELECT c1.name as name_x, c2.NAME  as name_y , COUNT(*) orders
FROM cart_products c1 JOIN cart_products c2
ON c1.CART_ID = c2.CART_ID AND c1.NAME != c2.NAME
AND c1.name > c2.name # >> 이 부분 추가~!
GROUP BY 1,2
ORDER BY 1,2

위 조건들의 차이점이 잘 이해가 되지 않는다면
아래 조건들을 예시로 들어볼 수 있다.

1,2,3 을 대입해 보았을떄,
(다르다) 조건은
12, 13, 21, 23, 31, 33 이 나올 수 있지만,
(< ) 크기비교 조건시
12, 13, 23 만 나올 수 있다.

Hyunjun Kim

이전 포스트

[SQL] QCC - 4회차

SQL

[4기] QCC - 4회차

문제 1 `(下)`

출력 값 예시

문제 2 `(中)`

출력 값 예시

문제 3 `(上)`

출력 값 예시

내 풀이

1.

2.

3.

[SQL] QCC - 3회차

0개의 댓글

[SQL] QCC - 4회차

SQL

[4기] QCC - 4회차

문제 1 (下)

출력 값 예시

문제 2 (中)

출력 값 예시

문제 3 (上)

출력 값 예시

내 풀이

1.

2.

3.

[SQL] QCC - 3회차

0개의 댓글

문제 1 `(下)`

문제 2 `(中)`

문제 3 `(上)`