[내일배움캠프] TIL 39일차 26.03.13(금)

Query Challenge Cycle 회고

 

2번

정답 코드:

select td.track_id
from track_details td
left join invoice_line il
on td.track_id = il.track_id
where il.track_id is null
	and td.media_type_id = 1
order by td.track_id

 

오류 상황:

select
  t.track_id
from track_details as t left join invoice_line as i
  on t.track_id = i.track_id
where t.media_type_id = 1
order by t.track_id asc;

=> 판매 내역에 존재하지 않는 트랙의 트랙 ID만 조회하는 조건 누락

 

해결 방법

=> 판매 내역에 존재하지 않는 트랙의 트랙 ID(track_id)가 없어야 하기 때문에 is null 사용

 

 

3번

정답 코드:

-- select *
-- from inoices
-- where date(invoice_date) between date '2025-01-01' and date '2025-12-31'
-- where date_format(invoice_date, '%Y-%m-%d') between '2025-01-31' and '2025-12-31'

with best_album as (
	select td.album_id
    from track_details td
    join inovice_line as il
    on td.track_id = il.track_id
    join invoices as i
    on i.invoice_id = il.invoice_id
    where date_format(invoice_date, '%Y-%m-%d') between '2025-01-01' and '2025-12-31'
    group by 1
    order by sum(il.unit_price * il.quantity) desc
    limit 1
)
select td.name
from tracK_details as td
join best_album as ba
on td.album_id = ba.album_id
order by 1

 

오류 상황:

select
  t.name
from track_details as t 
join invoice_line as il
  on t.track_id = il.track_id
join invoices as i
  on il.invoice_id = i.invoice_id
where 
  i.invoice_date like ('2025%')
having max(sum(t.unit_price * quantity) over partition by t.name)
order by t.name asc

=> 출력 불가

=> 추가 공부 필