QCC(Query Challenge Cycle)

이번주는 금요일이 아닌 목요일에 진행되었다. 그래서 왠지 오늘이 금욜 같잖아!!

결과는... 문제 풀이 마지막에서...

• 기본 데이터 실행코드
  

SHOW DATEABASE;
USE products;

---

문제 1 (하)

📝 문제

---

👩🏻‍💻풀이과정

• stores 테이블 조회
  

SELECT *
FROM stores s;
# STORE_ID, REGION_NAME, SALES, EMPLOYEES, OPEN_DATE, TYPE

• 해당 지역에 매장이 두개 이상인 경우만
  

SELECT *
FROM stores s 
GROUP BY REGION_NAME 
HAVING COUNT(DISTINCT STORE_ID) >= 2 ;

• 지역 이름을 기준 오름차순 정렬
  

SELECT *
FROM stores s 
GROUP BY REGION_NAME 
HAVING COUNT(DISTINCT STORE_ID) >= 2 
ORDER BY REGION_NAME;

• 지역별로 매출이 가장 높은 매장의 매출을 조회
  

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

---

✅ 정답

SELECT 
    region_name, 
    MAX(SALES) AS highest_sales
FROM stores
GROUP BY REGION_NAME
HAVING COUNT(DISTINCT STORE_ID) > 1
ORDER BY REGION_NAME;

---

문제 2 (중)

📝 문제

---

👩🏻‍💻풀이과정

• payments 테이블 조회
  

SELECT *
FROM payments p ;
	# ID, USER_ID, AMOUNT, PAY_DATE, PAYMENT_TYPE(0: CASH, 1: CARD)

• orders 테이블 조회
  

SELECT *
FROM orders o ;
	# ID, USER_ID, ORDER_DATE, ITEM

• 조건 1) 결제 하지 않고 상품 주문한 사용자
  

SELECT count(DISTINCT o.USER_ID) AS cnt_nopay
FROM orders o LEFT JOIN payments p ON o.USER_ID = p.USER_ID 
WHERE p.PAY_DATE IS NULL;

• 조건 2) 첫 번째 결제일 이전에 상품 주문한 사용자
  

SELECT COUNT(DISTINCT o.USER_ID) AS cnt_preord
FROM orders o LEFT JOIN 
	(SELECT	USER_ID, 
		MIN(p.PAY_DATE) AS first_pay_date
	FROM payments p
	GROUP BY USER_ID) fp
	ON o.USER_ID = fp.USER_ID
WHERE (o.ORDER_DATE < fp.first_pay_date); 

• 조건 1, 2 합치기
  

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

---

✅ 정답

WITH first_payment AS (
    SELECT 
        USER_ID, 
        MIN(PAY_DATE) AS FIRST_PAY_DATE
    FROM payments
    GROUP BY USER_ID
)
SELECT 
   COUNT(DISTINCT o.USER_ID) cnt 
FROM 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_DAT;

---

문제 3 (상)

📝 문제

---

👩🏻‍💻풀이과정

• cart_products 테이블 조회
  

SELECT *
FROM cart_products cp ;
	# ID, CART_ID, NAME, PRICE

• 제품 X와 Y가 같은 주문에 포함된 경우
  

SELECT A.CART_ID,
    A.NAME AS name_x, 
    B.NAME AS name_y
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;

• 각 제품 쌍과 해당 제품이 함께 포함된 주문 수 반환
  

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;

---

✅ 정답

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;

---

완전 다 틀렸당!
오늘 좀 정신이 없어서 넋놓고 풀었더니 저모양..ㅎ