효율적인 쿼리

안녕하세요 선생님,

nw 데이터로 자체적으로 문제를 만들어 풀어보았는데

해당 쿼리가 맞는지, 더 효율적인 쿼리가 있는지 여쭤봅니다. 항상 감사합니다.

---- 개인별로 가장 많이 산 상품과 그 상품을 샀을 떄 동시구매한 상품 top 1

with temp_01 as (select a.order_id, a.line_prod_seq, a.product_id, a.amount, b.customer_id, c.product_name

from order_items a

join orders b on a.order_id = b.order_id

join products c on a.product_id = c.product_id )

, kk as (select d.customer_id, d.order_id, d.product_id, d.product_name, d.amount, d.line_prod_seq

from temp_01 d)

, aa as (select d.customer_id, d.product_id as prod_01, kk.product_id as prod_02

from temp_01 d join kk on d.customer_id = kk.customer_id

where d.product_id != kk.product_id)

, bb as (select aa.customer_id, aa.prod_01, aa.prod_02, count(*) prd_num

from aa

group by 1,2,3

order by 1,2,4 desc)

, cc as (select customer_id, prod_01, prod_02, max(prd_num) max_num

from bb

group by 1,2,3)

select *

from cc

where prod_01 != prod_02 and max_num != 1