sql 문제풀이 5

문제 1 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

with fish_stats as -- 필터링 기준 만들기
    (select fish_type, avg(case when length is null or length <= 10 then 10 else length end) as avg_length
     from fish_info
     group by fish_type
     having avg(case when length is null or length <=10 then 10 else length end) >=33),
     fish_ranked as -- 계산
     (select id, fish_type, length,
      count(*) over(partition by fish_type) as fish_count, 
      row_number() over(partition by fish_type order by length desc) as fr
      from fish_info)
select t.fish_count, t.length -- 집계
as max_length, t.fish_type
from fish_ranked t
join fish_stats fs on t.fish_type = fs.fish_type
where t.fr = 1
order by t.fish_type;

풀이 2
with fish_data as 
	(select id, fish_type, length,
     		row_number() over(partition by fish_type order by length desc) as rm,
            count(*) over(partiton by fish_type) as fish_count
            avg(case when length is null or length <= 10 then 10 else length end) over(partition by fish_type) as avg_length
            from fish_info)
     select fish_count, length as max_length, fish_type
     	from fish_data
     	where rn = 1
     	and avg_length >= 33
     	order by fish_type