‘좋아, 상위 고객의 순위가 나왔어. 그렇다면 상위 10위 고객의 거주지는 어디고 직업은 무엇일까?’

 

SELECT A.주소, COUNT(A.주소) 카운팅

FROM

(

  SELECT A.customer_id          고객아이디,

         A.customer_name        고객이름,

         NVL(A.job, '정보없음') 직업,

         D.address_detail       주소,

         SUM(C.sales)           전용상품_매출,

         RANK() OVER(PARTITION BY C.item_id ORDER BY SUM(C.sales) DESC) 순위

  FROM customer A, reservation B, order_info C, address D

  WHERE A.customer_id = B.customer_id

  AND   B.reserv_no   = C.reserv_no

  AND   A.zip_code    = D.zip_code

  AND   B.cancel      = 'N'

  AND   C.item_id     = 'M0001'

  GROUP BY A.customer_id, C.item_id, A.customer_name, NVL(A.job, '정보없음'), D.address_detail

) A

WHERE A.순위 <= 10

GROUP BY A.주소

ORDER BY COUNT(A.주소) DESC;