[SQL문제풀기 - Advent of SQL 2025 🎅] 두 대회 연속으로 출전한 기록이 있는 배구 선수

문제

solvesql.com

https://solvesql.com/problems/volleyball-players-in-two-consecutive-olympics/

내가 작성한 오답

with a as (select r.athlete_id, g.year, dense_rank() over(order by g.year) dr
          from records r
          join games g on r.game_id = g.id
          where event_id in (select id
                            from events
                            where event = 'Volleyball Women''s Volleyball')
            and team_id in (select id from teams where team = 'KOR')
            and r.sex = 'F')
          
select distinct a.athlete_id id, c.name
from a a
join a b on a.dr+1 = b.dr and a.athlete_id = b.athlete_id
join athletes c on a.athlete_id = c.id

내가 작성한 정답

MySQL, SQLite, PostgreSQL

with a as (select id,dense_rank() over(order by year) dr from games where season = 'Summer'),
     b as (select r.athlete_id, a.dr
          from records r
          join a a on r.game_id = a.id
          where event_id in (select id
                            from events
                            where event = 'Volleyball Women''s Volleyball')
            and team_id in (select id from teams where team = 'KOR'))
select distinct a.athlete_id id, c.name
from b a
join b b on a.dr+1 = b.dr and a.athlete_id = b.athlete_id
join athletes c on a.athlete_id = c.id