4차 QCC review

≣ 목차

 

✏️ 1번 

문제

`store` 테이블 이용

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

해당 지역에 매장이 2개 이상인 경우만 결과에 포함

결과는 지역이름 기준으로 오름차순

 

풀이

SELECT region_name, max(sales) as highest_sales
from stores s 
group by 1
having count(distinct store_id) > 1
order by 1;

 

 

---

✏️ 2번 

문제

`payments` `orders` 테이블 이용

다음 조건에 해당되는 사용자 수를 출력

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

 

풀이

WITH first_payment AS ( 
    SELECT 
        USER_ID, 
        MIN(PAY_DATE) AS FIRST_PAY_DATE
    FROM payments
    GROUP BY USER_ID
) -- 모든 user의 첫번째 결제일 
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_DATE -- 첫번째 결제일보다 이전에 주문한 사용자

 

• with 절 -> 모든 user의 첫번째 결제일 테이블 `first_payment` 생성
• 메인 쿼리 
  •  `orders left join first_payment` -> orders의 모든 user_id 불러와서 first_payment 와 조인
  • `fp.user_id is null` -> 결제하지 않은 사용자 필터링
  • `o.order_date < fp.first_pay_date` -> 첫번째 결제일보다 이전에 주문한 사용자 필터링
  • `count(distinct o.user_id) cnt` -> 중복 제거된 user_id 개수 count

 

풀이2

SELECT count(distinct user_id) as cnt
FROM (
select o.user_id
from orders o left join payments p on o.user_id = p.user_id
where p.user_id is null -- 결제하지 않은 사용자

union

select o.user_id
from orders o left join (
select user_id, min(pay_date) as first_pay
from payments
group by user_id) f on o.user_id = f.user_id
where o.order_date < f.first_pay) as combined; -- 첫번째 결제일보다 이전에 주문한 사용자

 

• 결제하지 않은 사용자 테이블 & 첫번째 결제일 이전에 주문한 사용자 테이블 union

 

---

✏️ 3번

문제

`cart_products` 테이블 이용

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

• 두 제품은 서로 다른 이름이어야 하며, 한 쌍의 경우(예: Coffee와 Sausages)는 다른 순서(예: Sausages와 Coffee)로도 포함됨
• 결과는 각 제품 쌍 + 해당 제품이 함께 포함된 주문 수를 반환
• 제품 이름 X와 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;

 

• 카트id는 같고 제품name은 다른 경우의 수를 출력해야 함
  • `cart_products A` `cart_products B` 조인 -> 카트id가 같고, A제품이름과 B제품이름이 다른 경우 필터링
• `count(distinct a.cart_id) as orders` -> 각 제품 쌍이 포함된 주문 수 계산 (중복제거된 cart_id)
  • ex) Bread, Butter 한쌍이 포함된 주문, 즉 cart_id가 몇개인지 count