TIL - 250210

리뷰 : 윈도우 함수 적응 중, 집계함수가 아닌 경우 HAVING에서도 적용되지 않는다.(2번 문제)

URL : https://datalemur.com/questions/sql-top-three-salaries

SELECT
  department_name, name, salary
FROM (
  SELECT 
    *
    ,DENSE_RANK() OVER(PARTITION BY department_name ORDER BY salary DESC) rk
  FROM employee
  LEFT JOIN department
  USING (department_id)
  )a
WHERE rk < 4
ORDER BY department_name, salary DESC, name

URL : https://datalemur.com/questions/top-fans-rank

WITH artist_ranking as(
SELECT
  artist_name
  ,DENSE_RANK() OVER(ORDER BY cnt DESC) artist_rank
FROM (
SELECT 
  artist_name
  ,SUM(CASE WHEN rank <= 10 then 1 else 0 end) cnt
FROM (
  SELECT * 
  FROM artists
  LEFT JOIN songs
  USING(artist_id)
  LEFT JOIN global_song_rank
  USING(song_id)
) a
GROUP BY artist_name
ORDER BY cnt DESC
) b
)
SELECT * FROM artist_ranking WHERE artist_rank <= 5
;