세이렌과 인어들의 섬 습격 기록

Lv.4 SQL

문제 설명

세이렌은 두 마리의 인어를 데리고 섬을 습격해 주민들을 잡아먹습니다. 섬 주민과 습격 사건, 사건 참여자, 주민의 포획 기록은 다음 테이블에 저장되어 있습니다.

ISLANDS — 섬 정보
-`island_id: 섬 ID
- island_name: 섬 이름

RESIDENTS — 섬 주민 정보
- resident_id: 주민 ID
- island_id: 주민이 사는 섬 ID
- resident_name: 주민 이름

CREATURES — 생물 정보
- creature_id: 생물 ID
- creature_name: 생물 이름
- creature_role: 역할 (`'SIREN'` 또는 `'MERMAID'`)
- leader_id: 인어의 우두머리 생물 ID. 세이렌은 `NULL`

INCIDENTS — 습격 사건 정보
- incident_id: 사건 ID
- island_id: 사건이 발생한 섬 ID
- occurred_at: 사건 발생 일시

INCIDENT_PARTICIPANTS — 사건별 참여 생물
- incident_id: 사건 ID
- creature_id: 참여 생물 ID

CAPTURES — 주민 포획 기록
- capture_id: 포획 기록 ID
- incident_id: 포획이 발생한 사건 ID
- resident_id: 포획된 주민 ID
- captured_at: 포획 일시
- released_at: 주민이 풀려난 일시. 아직 풀려나지 않았다면 `NULL`

2026년 10월 1일 기준으로, 2026년 7월 1일부터 2026년 10월 1일 직전까지 발생한 습격 사건 중 세이렌과 그를 따르는 인어 두 마리가 모두 참여한 사건을 대상으로 분석하려고 합니다. 해당 사건에서 포획된 주민 가운데, 2026년 10월 1일 기준 아직 풀려나지 않은 주민 수가 모든 섬의 평균보다 많은 섬을 조회하세요.
(단, 날짜·시간은 YYYY-MM-DD HH:MM:SS 형식으로 저장된 문자열로 비교합니다. 사건 기간은 2026-07-01 00:00:00부터 2026-10-01 00:00:00 직전까지이며, 포획 상태는 2026-10-02 00:00:00 직전 시점을 기준으로 판단합니다. released_at이 NULL이거나 해당 기준 시점 이후인 주민을 아직 풀려나지 않은 것으로 봅니다.)

다음 조건을 적용합니다.
- 세이렌과 인어 두 마리가 같은 사건에 모두 참여한 경우만 대상 사건으로 봅니다. 인어는 `leader_id`로 자신이 따르는 세이렌을 식별합니다.
- 포획된 주민은 기준 시점까지 풀려나지 않은 경우에만 집계합니다. 즉 `released_at`이 `NULL`이거나 `2026-10-01` 이후인 기록을 대상으로 합니다.
- 한 주민이 대상 사건에서 여러 번 포획 기록에 나타나더라도 섬별 주민 수에는 한 번만 반영합니다.
- 평균은 포획 주민이 0명인 섬도 포함해 모든 섬을 기준으로 계산합니다.
- 기준 시점은 `2026-10-01`의 종료 시점으로 봅니다.

결과에는 다음 컬럼을 출력하세요.
- `island_id`: 섬 ID
- `island_name`: 섬 이름
- `active_captured_residents`: 기준 시점에 풀려나지 않은 고유 주민 수
- `average_captured_residents`: 전체 섬의 평균 고유 주민 수
`active_captured_residents`가 큰 순서로 정렬하고, 값이 같으면 `island_id`가 작은 순서로 정렬하세요.

솔루션 SQL

WITH qualified_incidents AS (
    SELECT
        -- TODO: 세이렌과 그를 따르는 인어 두 마리가 모두 참여한 사건 ID
    FROM INCIDENTS AS i
    -- TODO: 사건 참여 생물과 CREATURES 테이블 조인
    WHERE
        -- TODO: 2026년 7월 1일부터 2026년 10월 1일 직전까지의 사건
    GROUP BY
        -- TODO: 사건 단위로 그룹화
    HAVING
        -- TODO: 참여한 인어 수가 2마리인지 확인
),
active_captures AS (
    SELECT
        -- TODO: 섬 ID와 고유 포획 주민 수
    FROM qualified_incidents AS qi
    -- TODO: 사건, 섬, 포획 기록 조인
    WHERE
        -- TODO: 기준 시점까지 포획 상태인 기록만 선택
    GROUP BY
        -- TODO: 섬 단위로 그룹화
),
island_counts AS (
    SELECT
        i.island_id,
        i.island_name,
        -- TODO: 포획 주민이 없는 섬은 0명으로 처리
    FROM ISLANDS AS i
    -- TODO: active_captures를 LEFT JOIN
),
island_counts_with_average AS (
    SELECT
        island_id,
        island_name,
        active_captured_residents,
        -- TODO: 모든 섬의 평균을 계산
    FROM island_counts
)
SELECT
    island_id,
    island_name,
    active_captured_residents,
    average_captured_residents
FROM island_counts_with_average
WHERE
    -- TODO: 섬별 포획 주민 수가 전체 평균보다 많은 경우
ORDER BY
    -- TODO: 포획 주민 수 내림차순, island_id 오름차순
;

실행 결과

실행 또는 제출하면 결과가 여기에 표시됩니다.