[QCC 5회차]

[4기] QCC - 5회차

[4기] QCC - 5회차 | Notion

 

 

 

 

 

내 답

use qcc

 

select release_year,

revenue

from movies

where release_year >=2012 and vote_count >= 100 and json_length(genres)>=2

group by 1

order by release_year;

 

 

** 안에 있는 거 세주기  json_length(genres) >=2

 

 

< 해설>

select release_year,

sum(revenue) revenue

from movies

where release_year >=2012 and vote_count >= 100 and json_length(genres)>=2

group by 1

order by release_year;

 

** 합계니까 sum(revenue)으로 했어야 했다.... 답이 틀림

 

 

 

내 답

 

select customerid,

TotalOrderAmount,

rank() over(order by TotalOrderAmount desc) rn

from

(select customerid,

sum(totaldue) TotalOrderAmount

from SalesOrderHeader

group by customerid )a

limit 5

 

 

 

 

< 해석 >

어랏. 

 

 

 

 

 

내 답

select customer_id,

event_type status,

event_date start_date

from membership_history

order by 1, 3

 

 

end_date 이랑  current_flag 구하기 어려웠음

 

<해석>

 

1) 

 

** lead 썼을 때 (내 앞에 있는 거 여기로 가져와)

lead( event_date ) over (partition by customer_id order by event_date) as next_event_date

 

 ** leg 썼을 때 ( 그 전꺼 가져와) 

 

With구문 으로 뺌