인프런 커뮤니티 질문&답변

안녕하세요, 도움을 받고자 글 올립니다.

97년 이후 nw데이터의 '국가/ 기준월 / 대표제품명 / 구매고객수 / 동시구매 고객 수 / 구매 횟수 / 동시구매 횟수 / 동시구매율' 을 구해보았는데 결과가 나오긴 하는데 맞는지 알 수가 없어서 코드리뷰 부탁드립니다. 더불어 더 좋은 코드가 있을지 여쭙습니다.

새해 복 많이 받으세요

with uu as (select customer_id, product_id, order_id, product_name, order_date, ship_address

from

(select c.product_id, c.product_name, a.amount, b.order_date, b.ship_address , b.customer_id, b.order_id

from order_items a join orders b on a.order_id = b.order_id

join products c on a.product_id = c.product_id

where extract(year from b.order_date) >= 1997) tt

)

, xx as (select ww.customer_id, ww.product_id as prd_01, vv.product_id as prd_02, ww.order_date, ww.ship_address

from uu ww join uu vv on ww.customer_id = vv.customer_id)

, temp_01 as (select customer_id, prd_01, prd_02, extract(year from order_date) as year_ord, extract(month from order_date) as month_ord, ship_address as country

from xx

where prd_01 != prd_02

group by 1,2,3,4,5,6

order by 1,2,3,4,5,6)---------------구매자 id, 대표제품 id, 동시구매제품 id, 년, 월, 국가

, temp_03 as (select prd_01, prd_02, count(*) as cnt_prd_prd

from temp_01

group by 1,2

order by 1, 3 desc) -----------------대표제품 id, 동시구매제품 id, 동시구매 횟수

, temp_04 as (select distinct product_id, count(customer_id) as cnt_prd

from order_items oi join orders o on oi.order_id = o.order_id

group by 1

order by 1)-------------------- 대표제품 id, 구매고객수

, temp_05 as (select prd_01, prd_02, count(customer_id) as cnt_prd_customer

from temp_01

group by 1,2

order by 1,3)-------- 동시구매 고객 수

, temp_06 as (select customer_id, count(order_id) as cnt_customer

from orders

group by 1)------ 구매횟수

, temp_07 as (select distinct t5.prd_01, t5.prd_02, cnt_prd_customer as 동시구매고객수, sum(cnt_customer) as 구매횟수, cnt_prd_prd as 동시구매횟수

, cnt_prd_prd/sum(cnt_customer) as 동시구매율

from temp_01 t1 join temp_05 t5 on t5.prd_01 = t1.prd_01 and t5.prd_02 = t1.prd_02

join temp_06 t6 on t1.customer_id = t6.customer_id

join temp_03 t3 on t1.prd_01 = t3.prd_01 and t1.prd_02 = t3.prd_02

group by 1,2,3,5

order by 1,5 desc) ---/ 동시구매 고객 수 / 구매 횟수 / 동시구매 횟수 / 동시구매율

, temp_08 as (select distinct country, year_ord , month_ord , prd_01, cnt_prd as 구매고객수

from temp_01 t1 join temp_04 t4 on t1.prd_01 = t4.product_id

order by 1,2,3,4)---국가/ 기준월 / 대표제품명 / 구매고객수

select distinct t8.*, prd_02, 동시구매고객수, 구매횟수, 동시구매횟수, 동시구매율

from temp_08 t8 join temp_07 t7 on t8.prd_01 = t7.prd_01

;