[SQL문제풀기] 스테디셀러 작가 찾기

silver

Oct 05, 2025

Contents

문제 SQLite

문제

solvesql.com

https://solvesql.com/problems/find-steadyseller-writers/

SQLite

내가 작성한 오답

: 가장 긴 연속 구간 1개만 뽑아야 하는데, 전체 묶음으로 계산되고 있다


select author, max(year) year, max(n) depth
from (select author, year, row_number() over(partition by author order by year) n
      from books 
      where genre = 'Fiction')
group by author
having max(year)-min(year) = max(n)-1
      and count(year) >= 5

: row_number() over(partition by author order by year) 는 같은 연도에 여러 레코드가 있으면 각 레코드에 서로 다른 순번을 줌. → 중복제거 필요


select author, max(year) year, count(*) depth
from (select author, year, year - row_number() over(partition by author order by year) n
      from books 
      where genre = 'Fiction')
group by author, n
having count(n) >= 5

내가 작성한 정답


select author, max(year) year, count(*) depth
from (select author, year, year - row_number() over(partition by author order by year) n
      from (select distinct author, year from books where genre ='Fiction') )
group by author, n
having count(n) >= 5;

내가 이전에 작성한 정답

: 작가 별 연도를 정렬한 후 연속되지 않은 해에서 증가하는 번호로 그룹을 나눴다


with a as (select author, year,
                    lag(year) over(partition by author order by year) py
            from (select distinct author,year from books where genre='Fiction')),
b as (select author, year,
            sum(d) over (partition by author order by year rows unbounded preceding) gr
      from (select author, year,
                    case when year - py = 1 or py is null then 0
                    else 1 end d
              from a))
select author, max(year) year, count(year) depth
from b 
group by author, gr
having count(year) >= 5

💡

LAG() - 현재 행 기준으로 이전 행의 값을 가져옴.

LEAD() - 현재 행 기준으로 다음 행의 값을 가져옴.

• 이전/다음 시각과 비교해서 시간 차 계산할 때

• 세션 분리 기준 찾을 때 (현재 시간 - 이전 시간 >= 30분)

• 앞뒤 값을 기반으로 변화 감지할 때 (예: 주가 변화, 상태 변화 등)


LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)
	•	column_name: 이전 값을 가져올 컬럼
	•	offset: 몇 번째 이전 값? (생략 시 기본은 1)
	•	default_value: 이전 값이 없을 경우 대체할 값 (예: 첫 번째 행)

LEAD(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)	
  •	column_name: 다음 값을 가져올 컬럼
	•	offset: 몇 번째 다음 값? (생략 시 기본은 1)
	•	default_value: 다음 값이 없을 경우 대체할 값 (예: 첫 번째 행)

select author, max(year) year, max(n) depth from (select author, year, row_number() over(partition by author order by year) n from books where genre = 'Fiction') group by author having max(year)-min(year) = max(n)-1 and count(year) >= 5

select author, max(year) year, count(*) depth from (select author, year, year - row_number() over(partition by author order by year) n from books where genre = 'Fiction') group by author, n having count(n) >= 5

select author, max(year) year, count(*) depth from (select author, year, year - row_number() over(partition by author order by year) n from (select distinct author, year from books where genre ='Fiction') ) group by author, n having count(n) >= 5;

with a as (select author, year, lag(year) over(partition by author order by year) py from (select distinct author,year from books where genre='Fiction')), b as (select author, year, sum(d) over (partition by author order by year rows unbounded preceding) gr from (select author, year, case when year - py = 1 or py is null then 0 else 1 end d from a)) select author, max(year) year, count(year) depth from b group by author, gr having count(year) >= 5

LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...) • column_name: 이전 값을 가져올 컬럼 • offset: 몇 번째 이전 값? (생략 시 기본은 1) • default_value: 이전 값이 없을 경우 대체할 값 (예: 첫 번째 행) LEAD(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...) • column_name: 다음 값을 가져올 컬럼 • offset: 몇 번째 다음 값? (생략 시 기본은 1) • default_value: 다음 값이 없을 경우 대체할 값 (예: 첫 번째 행)