내배캠 qcc 2회차 문풀

간만에 만지니 너무 어려워서 버벅이다가 못풀었다... 파이썬 그만 만지고 sql도 다시 공부 해야할듯

 

문제 공개해도 되는지 몰라서 쿼리만

select c.customer_id,
       p.last_name,
       sum(sod.order_qty) as total_order,
       p.first_name
from sales_customer as c
join person as p on c.person_id = p.business_entity_id
join sales_order_header as soh on c.customer_id = soh.customer_id
join sales_order_detail as sod on soh.sales_order_id = sod.sales_order_id
where
    soh.order_date like '2011-10%' -- ->soh.order_date >= '2011-10-01' and soh.order_date < '2011-11-01'
    and soh.status <> 6
    and sod.order_qty is not null
    and p.first_name is not null
    and p.last_name is not null
group by c.customer_id,
		 p.first_name,
         p.last_name
having sum(sod.order_qty) >= 70
order by c.customer_id asc;

 

차근 차근 풀어본 최종 쿼리

조인 3번하고 조건걸면 되긴하는데 갑자기 컬럼명이 달라도 조인이 되던가 하는 의문에 시간이 많이 잡아먹힌듯

기본적인걸 많이 까먹었다. 그래도 한번 기억하니까 나머지도 좀 기억이 나서 바꿔본 쿼리

편의상 %를 많이 썼는데 자원 누수가 심하다고 그래서 바꿔봤다. 

 

with totalorders as (
    select
    	soh.customer_id,
    	sum(sod.order_qty) as total_quantity
    from sales_order_header as soh
    join sales_order_detail as sod on soh.sales_order_id = sod.sales_order_id
    where
        soh.order_date >= '2011-10-01' and soh.order_date < '2011-11-01'
        and soh.status <> 6
        and sod.order_qty is not null
    group by soh.customer_id
    having sum(sod.order_qty) >= 70)
select 
    p.first_name,
    p.last_name,
    cto.total_quantity,
    c.customer_id
from sales_customer as c
join person as p on c.person_id = p.business_entity_id
join totalorders as cto on c.customer_id = cto.customer_id
where p.first_name is not null and p.last_name is not null
order by c.customer_id asc;

 

아으 중간에 문제 풀던것만 안날아갔어도