[QCC] 4회차

문제 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 지역에는 매장이 하나만 있습니다. 따라서 결과에 포함되지 않습니다.

---

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

📚나의 답

SELECT REGION_NAME, MAX(SALES) AS highest_sales 
FROM stores
WHERE REGION_NAME IN (SELECT REGION_NAME FROM stores GROUP BY REGION_NAME HAVING COUNT(*)>=2)
GROUP BY REGION_NAME 
ORDER BY REGION_NAME; 

📚튜터님 답

select REGION_NAME, max(SALES)
from stores
group by 1 
HAVING count(distinct STORE_ID) > 1
order by 1

문제 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랑 동일합니다.

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

---

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

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

---

출력 값 예시

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 테이블이 다음과 같다면 :

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

---

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

cnt
3

---

📚나의 답

x

📚튜터님 답

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 (上)

---

• 테이블 설명

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

---

📚나의 답

SELECT cp1.NAME AS NAME_X, cp2.NAME AS NAME_Y, COUNT(*) AS ORDERS
FROM cart_products cp1
JOIN cart_products cp2
ON cp1.CART_ID = cp2.CART_ID 
GROUP BY NAME_X, NAME_Y
ORDER BY NAME_X, NAME_Y;

📚튜터님 답

SELECT 
    A.NAME AS name_x, 
    B.NAME AS name_y, 
    COUNT(DISTINCT A.CART_ID) as orders 
FROM cart_products A
JOIN cart_products B
ON A.CART_ID = B.CART_ID AND A.NAME <> B.NAME
GROUP BY A.NAME, B.NAME
ORDER BY A.NAME, B.NAME;

A.NAME <> B.NAME 조건을 빼먹음😥

---

다 맞추는게 항상 목표인데 쉽지않다... 다음 시험에서는 기필코... 제발...