SQL QCC 오답노트

문제1. 

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

select REGION_NAME as region_name, 
	   MAX(SALES) as highest_sales 
from stores
group by region_name
HAVING count(distinct STORE_ID) >= 2 
order by region_name;

---

 

문제2. 

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

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

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_payments fp
ON o.USER_ID = fp.USER_ID
WHERE fp.USER_ID IS NULL 
   OR o.ORDER_DATE < fp.FIRST_PAY_DATE

 

첫 번째 결제일보다 이전에 상품을 주문한 사용자라는 말을 제대로 이해하지 못해서 식을 구현하지 못했다. 

결제를 먼저 하고 상품을 주문하는 게 정상적인데, 이 사용자는 결제를 안하고 주문을 해버렸다. 

1. WITH절로 첫 번째 결제일을 구해준다. 

2. 첫번째 결제일을 구한 테이블을 기준으로 left join을 한다. 

3. first_payment테이블에는 없는데, order테이블에만 있는 사용자를 찾아주기 - 1번 조건 

4. order_date < first_pay_date = 2번 조건

 

---

 

문제3. 

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

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

내가 쓴 답

 

select x.name as name_x,
	   y.name as name_y,
	   count(distinct x.CART_ID) as orders
from cart_products as x join cart_products as y on x.CART_ID = y.CART_ID 
group by 1, 2
order by 1, 2

ON조건에서 x.name <> y.name 이 같지 않다는 것을 명시하지 않았다. 

 

 

select x.name as name_x,
	   y.name as name_y,
	   count(distinct x.CART_ID) as orders
from cart_products as x join cart_products as y on x.CART_ID = y.CART_ID and x.name <> y.name
group by 1, 2
order by 1, 2