[코드잇 스프린트] 스프린트 미션 06 - SQL로 2024년 음악 결산 데이터 조회하기

이 글은 코드잇 스프린트 데이터 분석가 과정 학습 기록입니다.

🎶SQL로 2024년 음악 결산 데이터 조회하기

이번 여섯 번째 스프린트 미션에서는 앞서 배운 SQL 문법을 활용해 가상의 음악 서비스에서 '2024년 내가 들은 음악 결산' 페이지에 들어갈 데이터를 직접 조회해보는 과제를 진행했다. 단순히 한두 개의 테이블에서 데이터를 가져오는 것이 아니라, 아티스트, 앨범, 곡, 사용자, 재생 기록 테이블을 서로 연결해서 필요한 정보를 뽑아내야 했기 때문에 지금까지 배운 조회, 조건, 정렬, 집계, 그룹화, 조인, 그리고 서브쿼리까지 한 번에 복습하는 느낌이 강한 미션이었다.

 

이번 미션도 쉽지는 않았다. 잘 풀리는 문제도 분명 있었지만 문제 하나를 100이라고 했을 때 90 정도까지는 가는데 마지막 10 때문에 자꾸 에러가 나는 경우가 있었다... 머릿속으로는 이렇게 조회하면 되겠다, 이 순서로 집계하면 되겠다가 어느 정도 그려지는데 막상 그걸 SQL 문장으로 옮기려니 특히 서브쿼리 부분에서 구조를 어떻게 짜야 할지 많이 헷갈렸다. 그래도 이번 미션을 하면서 느낀 건, SQL은 단순히 문법을 아는 것보다 문제를 작은 단계로 쪼개서 생각하는 힘이 훨씬 중요하다는 점이었다.

🧬이번 미션에서 다룬 데이터베이스 구조

이번 실습 데이터베이스는 음악 서비스의 구조를 그대로 가져온 형태였다. 기본적으로는 다음과 같은 테이블로 구성되어 있었다.

• artists: 아티스트 정보
• albums: 앨범 정보
• songs: 곡 정보
• users: 사용자 정보
• history: 사용자의 곡 재생 기록

음악 자체에 대한 정보는 아티스트-앨범-곡으로 이어지고 실제 사용자의 행동 데이터는 history 테이블에 쌓이는 구조였다. 그래서 간단한 목록 조회 정도는 한 테이블만 봐도 가능했지만 사용자와 곡 제목, 아티스트 이름, 재생 수 같은 정보를 한 번에 보려면 결국 여러 테이블을 연결해야 했다. 이번 미션에서 조인을 계속 사용하게 된 이유도 바로 이 구조 때문이었다.

🔍파트 1. 데이터 파악하기

미션의 시작은 데이터베이스 구조를 확인하는 것이었다. 어떤 테이블이 있는지 보고, 각 테이블의 컬럼과 타입을 확인하고, 실제로 어떤 데이터가 들어 있는지도 간단히 조회해보는 단계였다. 겉으로 보기엔 제일 쉬운 파트였지만, 오히려 이 단계에서 구조를 제대로 이해해야 이후 문제를 더 수월하게 풀 수 있었다.

 

📍1-1) 테이블 목록과 컬럼 구조 확인

/*
1-1. 존재하는 테이블의 목록과, 각 테이블의 컬럼 정보를 각각 확인해 보세요.
테이블의 목록은 SHOW TABLES; 구문으로, 테이블의 컬럼 정보는 DESCRIBE 테이블_이름; 구문으로 확인할 수 있어요.
*/

SHOW TABLES;

DESCRIBE albums;
DESCRIBE artists;
DESCRIBE history;
DESCRIBE playlists;
DESCRIBE songs;
DESCRIBE users;

 

가장 먼저 SHOW TABLES;로 테이블 목록을 확인하고, DESCRIBE 테이블명;으로 컬럼 구조를 확인했다. 사실 SELECT나 WHERE는 익숙했지만 SHOW TABLES;나 DESCRIBE는 이번에 처음 써봤다. 문제에서 아예 이 구문을 알려준 이유를 바로 알 수 있었다. 실제로 DESCRIBE를 해보니 컬럼의 타입, NULL 허용 여부, 키 정보 등을 볼 수 있었다.

 

📍1-2) 사용자 목록과 아티스트 목록 확인

/*
1-2. 사용자 목록, 아티스트 목록을 각각 확인해 보세요.
*/

SELECT *
FROM users;

SELECT *
FROM artists;

 

그 다음에는 users, artists 테이블을 각각 조회해서 어떤 데이터가 들어 있는지 확인했다. 이 단계는 문법적으로는 정말 기본적인 SELECT * FROM 테이블명; 문제였지만, 이후 특정 사용자나 특정 아티스트를 직접 골라야 하는 문제들이 있었기 때문에 그냥 넘어가는 단계는 아니었다. 목록을 보다 보니 실제 이름처럼 보이는 계정명이나 아티스트명이 꽤 많아서 뒤 문제에서 누구를 골라볼까?를 미리 생각하면서 보게 됐다.

 

📍1-3) 특정 아티스트의 앨범 목록 확인

/*
1-3. 궁금한 아티스트를 한 명 골라 모든 앨범 목록을 확인해 보세요.
*/

SELECT title
FROM albums
WHERE artist_id = 35;

 

이후에는 궁금한 아티스트 한 명을 골라 해당 아티스트의 앨범 목록을 조회했다. 여기서는 아티스트 목록을 먼저 본 뒤 Deanna Murphy라는 이름이 눈에 띄어서 그 사람의 id를 기준으로 albums 테이블에서 조회했다. 이 문제는 사실상 기본 조회 + 조건 걸기의 형태였기 때문에 어렵지는 않았다.

 

📍1-4) 특정 사용자의 최근 재생 곡 조회

/*
1-4. 사용자를 한 명 골라 가장 최근에 재생한 20곡을 재생 시점 순으로 확인해 보세요.
(재생 시점, 사용자 ID, 사용자 계정명, 곡 ID, 곡 제목, 곡 재생 시간(초)를 조회해보세요.)
*/

SELECT h.played_at, h.user_id, u.username, h.song_id, s.title, s.duration_seconds
FROM users u
LEFT JOIN history h
ON u.id = h.user_id
LEFT JOIN songs s
ON h.song_id = s.id
WHERE u.username = 'danielblackwell'
ORDER BY h.played_at DESC
LIMIT 20;

 

파트 1의 마지막 문제는 특정 사용자가 가장 최근에 재생한 20곡을 재생 시점 순으로 확인하는 문제였다. 여기서는 재생 시점은 history, 사용자 계정명은 users, 곡 제목과 재생 시간은 songs에 있기 때문에 세 테이블을 조인해야 했다. 즉 이 문제의 핵심은 단순한 조회가 아니라 '필요한 정보가 각각 어느 테이블에 있는지 파악하고 그것들을 연결하는 것'이었다. 처음 SQL을 배울 때는 조인이 가장 복잡하게 느껴졌는데 이쯤 되니까 적어도 이 정보들을 한 번에 보려면 어떤 테이블들을 연결해야 하는지 정도는 바로 떠오르기 시작했다. 그리고 특정 사용자, 최신순 정렬, 20개 제한이라는 조건이 함께 들어가 있었기 때문에 WHERE, ORDER BY, LIMIT도 같이 사용해야 했다. 이 문제는 여러 요소가 한 번에 들어가 있었지만 이미 앞에서 배운 내용들이 자연스럽게 이어지는 구조라 비교적 수월하게 풀 수 있었다.

🎵파트 2. 2024년의 음악

파트 2부터는 본격적으로 2024년이라는 시점에 초점을 맞춰 데이터를 조회하는 문제가 이어졌다. 즉, 단순히 음악 데이터 전체를 보는 것이 아니라 2024년에 발매된 앨범, 2024년에 가장 많이 재생된 곡, 2024년에 많이 재생된 아티스트처럼 연도 조건이 포함된 조회를 수행해야 했다. 이 파트의 문제들을 풀면서 가장 반복적으로 사용한 것은 다음 세 가지였다.

• 날짜 조건 걸기
• 조인을 통한 정보 연결
• 그룹화와 집계

📍2-1) 2024년에 발매된 앨범 조회

/*
2-1. 2024년에 발매된 모든 앨범을 확인해 보세요.
(앨범의 ID, 앨범 제목, 앨범 발매일, 아티스트 이름을 조회해 보세요. 발매순으로 정렬하고 발매일이 같을 경우 앨범의 ID 순으로 정렬하세요.)
*/

SELECT a.id, a.title, a.release_date, ar.name
FROM albums a
LEFT JOIN artists ar
ON a.artist_id = ar.id
WHERE a.release_date LIKE '2024%'
ORDER BY a.release_date, a.id;

 

첫 문제는 2024년에 발매된 모든 앨범을 발매일 순으로 조회하는 것이었다. 여기서는 albums와 artists를 조인해서 앨범 정보와 아티스트 이름을 함께 가져왔다. 그리고 2024년 발매 앨범만 보기 위해 release_date LIKE '2024%' 형태로 조건을 걸었다.

날짜형 컬럼에서 YEAR()를 써도 되지만 이 문제에서는 문자열 패턴으로 걸어보는 방식으로 접근했다. 그리고 정렬 조건도 발매일이 같으면 앨범 id 순까지 들어가 있었기 때문에 단순히 조회만 하는 게 아니라 문제에서 요구한 출력 순서를 정확하게 맞추는 것도 중요했다.

 

📍2-2) 2024년에 앨범을 많이 발매한 아티스트 조회

/*
2-2. 2024년에 앨범을 발매한 아티스트의 목록을 앨범을 많이 발매한 순서대로 확인해 보세요.
(아티스트 ID, 아티스트 이름, 발매한 앨범의 수를 조회하고, 앨범의 수가 많은 것부터 정렬하되 앨범의 수가 같을 경우 아티스트의 이름 순으로 정렬하세요.)
*/

SELECT ar.id, ar.name, COUNT(a.id) AS albumcount
FROM albums a
LEFT JOIN artists ar
ON a.artist_id = ar.id
WHERE release_date LIKE '2024%'
GROUP BY ar.id, ar.name
ORDER BY albumcount DESC, ar.name;

 

이 문제부터는 집계가 들어갔다. 2024년에 앨범을 발매한 아티스트 목록을 보고, 각 아티스트가 몇 개의 앨범을 냈는지 계산해야 했기 때문이다. 그래서 COUNT()를 사용하고, 아티스트별로 집계해야 하므로 GROUP BY를 걸어야 했다. 이 문제를 풀면서 다시 느낀 건, 집계 문제에서는 결국 무엇을 기준으로 묶을지가 가장 중요하다는 점이었다. 여기서는 아티스트별로 묶어야 하니 GROUP BY ar.id, ar.name이 필요했고, 그 결과 위에서 COUNT(a.id)를 통해 앨범 수를 계산할 수 있었다. 이전 문제와 구조가 유사해서 비교적 빠르게 풀 수 있었고, 점점 조건 → 그룹화 → 집계 → 정렬이라는 흐름이 익숙해지기 시작했다.

 

📍2-3) 2024년에 가장 많이 재생된 곡 조회

/*
2-3. 2024년에 가장 많이 재생된 20곡을 많이 재생된 순서대로 확인해 보세요.
(곡의 ID, 곡 제목, 재생 수를 조회하고, 재생 수가 같을 경우 곡 제목 순으로 정렬하세요.)
*/

SELECT s.id, s.title, COUNT(h.id) AS playcount
FROM history h
LEFT JOIN songs s
ON h.song_id = s.id
WHERE played_at LIKE '2024%'
GROUP BY s.id, s.title
ORDER BY playcount DESC, s.title
LIMIT 20;

 

이 문제에서는 기준이 앨범에서 곡으로 바뀌었다. 즉 이제 발매 데이터가 아니라 재생 기록인 history를 중심으로 봐야 했다. 2024년에 재생된 데이터만 필터링하고, 곡별 재생 수를 세기 위해 songs와 조인한 뒤 COUNT(h.id)를 사용했다. 여기서도 핵심은 곡별 재생 수이기 때문에 그룹화 기준을 곡 id와 곡 제목으로 잡아야 한다는 점이었다. 문제들이 반복될수록 패턴이 보였다. 누구를 기준으로 집계할 것인지, 어떤 날짜 조건이 들어가는지, 결과를 어떤 순서로 정렬할 것인지가 거의 문제 해석의 핵심이었다.

 

📍2-4) 2024년에 가장 많이 재생된 아티스트 조회

/*
2-4. 2024년에 가장 많이 재생된 20명의 아티스트를 많이 재생된 순서대로 확인해 보세요.
(아티스트의 ID, 아티스트 이름, 재생 수를 조회하고, 재생 수가 같은 경우 아티스트의 이름 순으로 정렬하세요.)
*/

SELECT ar.id, ar.name, COUNT(h.id) AS playcount
FROM artists ar
LEFT JOIN albums a
ON ar.id = a.artist_id
LEFT JOIN songs s
ON a.id = s.album_id
LEFT JOIN history h
ON s.id = h.song_id
WHERE played_at LIKE '2024%'
GROUP BY ar.id, ar.name
ORDER BY playcount DESC, ar.name
LIMIT 20;

 

이 문제는 개인적으로 파트 2에서 가장 번거로웠다. 곡별 조회는 history와 songs만 연결하면 되지만 아티스트별 조회는 history → songs → albums → artists까지 이어져야 했기 때문이다. 즉, 재생 기록에서 곡을 타고, 곡에서 앨범을 타고, 앨범에서 아티스트로 가는 구조를 정확히 이해해야 했다..ㅋㅋ 문법 자체는 앞 문제들과 비슷했지만, 연결해야 하는 테이블 수가 늘어나니까 처음엔 좀 귀찮게 느껴졌다. 그래도 이런 구조를 한 번 직접 짜보니까 정규화된 데이터베이스에서 왜 조인이 중요한지 훨씬 실감이 났다. 이 문제도 날짜 조건, 그룹화, 정렬, 제한 수를 차례대로 붙여 해결할 수 있었다.

🎧파트 3. 내가 2024년에 들은 음악

파트 3은 파트 2보다 더 흥미로웠다. 이번에는 전체 사용자나 전체 음악 데이터가 아니라 특정 사용자 한 명을 중심으로 2024년 감상 기록을 분석하는 단계였기 때문이다. 여기서는 내가 사용자 id가 16인 사람을 기준으로 문제를 풀었다. 특정 사용자 기준으로 최근 재생 곡, 많이 들은 곡, 많이 들은 아티스트, 월별 감상 횟수, 총 재생 시간까지 확인해보는 구조였다.

 

📍3-1) 가장 최근에 재생한 100곡 조회

/*
3-1. 특정 사용자가 2024년에 재생한 곡 중에서 가장 최근에 재생한 100곡을 재생 시점 순으로 정렬하여 확인해 보세요.
(사용자 ID, 사용자 계정명, 재생 시점, 곡 제목, 앨범 제목, 아티스트 이름을 조회하세요.)
*/

SELECT u.id, u.username, h.played_at, s.title, a.title, ar.name
FROM history h
LEFT JOIN users u
ON h.user_id = u.id
LEFT JOIN songs s
ON h.song_id = s.id
LEFT JOIN albums a
ON a.id = s.album_id
LEFT JOIN artists ar
ON ar.id = a.artist_id
WHERE (u.id = 16) AND (h.played_at LIKE '2024%')
ORDER BY h.played_at DESC
LIMIT 100;

 

이 문제는 요구하는 정보가 꽤 많았다. 사용자 ID, 사용자 계정명, 재생 시점, 곡 제목, 앨범 제목, 아티스트 이름까지 모두 필요했기 때문에 사실상 history, users, songs, albums, artists 다섯 개 테이블을 전부 조인해야 했다. 재생 기록은 history에 있고, 곡 제목은 songs, 앨범 제목은 albums, 아티스트 이름은 artists에 있으니까 결국 다 연결할 수밖에 없었던 것이다. 이 문제는 집계 없이 조건과 정렬만으로 해결하는 문제였고 복잡해 보이는 것과 달리 논리는 비교적 단순했다!

 

📍3-2) 특정 사용자가 많이 들은 20곡

/*
3-2. 특정 사용자가 2024년에 많이 들은 20곡을 많이 들은 순서대로 확인해 보세요.
(곡의 ID, 곡 제목, 재생 수를 조회하시오. 재생 수가 같은 경우 곡 제목 순으로 정렬하세요.)
*/

SELECT s.id, s.title, COUNT(h.id) AS playcount
FROM history h
LEFT JOIN songs s
ON h.song_id = s.id
WHERE (h.user_id = 16) AND (h.played_at LIKE '2024%')
GROUP BY s.id, s.title
ORDER BY playcount DESC, s.title
LIMIT 20;

 

여기서는 다시 집계가 등장했다. 특정 사용자가 2024년에 들은 곡 중 많이 들은 순으로 20곡을 보여줘야 했기 때문에 user_id 조건과 연도 조건을 걸고 곡별로 그룹화해서 재생 수를 계산했다. 이 파트부터는 조인과 그룹화가 꽤 자연스럽게 묶이기 시작했다. 어떤 문제는 '여러 테이블을 합치는 것'이 핵심이고, 어떤 문제는 '합친 다음 어떤 기준으로 묶을 것인가'가 핵심인데, 이 문제는 그 두 가지가 잘 합쳐진 유형이었다.

 

📍3-3) 특정 사용자가 많이 들은 20명의 아티스트

/*
3-3. 특정 사용자가 2024년에 많이 들은 20명의 아티스트를 많이 들은 순서대로 확인해 보세요.
(아티스트의 ID, 아티스트 이름, 재생 수를 조회하고, 재생 수가 같은 경우 아티스트의 이름 순으로 정렬하세요.)
*/

SELECT ar.id, ar.name, COUNT(h.id) AS playcount
FROM history h
LEFT JOIN songs s
ON h.song_id = s.id
LEFT JOIN albums a
ON s.album_id = a.id
LEFT JOIN artists ar
ON a.artist_id = ar.id
WHERE (h.user_id = 16) AND (h.played_at LIKE '2024%')
GROUP BY ar.id, ar.name
ORDER BY playcount DESC, ar.name
LIMIT 20;

 

곡 단위에서 아티스트 단위로 바뀌었을 뿐, 구조는 앞 문제와 비슷했다. history에서 시작해 songs, albums, artists로 넘어가면서 아티스트 정보를 연결하고, 아티스트별로 재생 수를 집계했다. 이쯤 되니까 같은 구조의 문제는 확실히 빨라졌다. 처음엔 조인 경로를 찾는 것도 번거로웠는데 계속 반복하다 보니 아티스트까지 가려면 songs, albums를 거쳐야 한다는 식의 연결이 머릿속에 자리 잡기 시작했다.ㅋㅋ

 

📍3-4) 월별 음악 감상 횟수

/*
3-4. 특정 사용자의 2024년 월별 음악 감상 횟수를 확인해 보세요.
*/

SELECT MONTH(played_at) AS month, COUNT(id) AS playcount
FROM history
WHERE (user_id = 16) AND (played_at LIKE '2024%')
GROUP BY month
ORDER BY month;

 

이 문제는 비교적 단순했다. MONTH(played_at)으로 월만 추출하고, 월별로 그룹화하여 재생 횟수를 계산했다. 여기서는 조인 없이 history 테이블 하나만으로 해결할 수 있었기 때문에 오히려 더 깔끔하게 느껴졌다. 이 문제를 풀면서 느낀 건 꼭 복잡한 조인만이 중요한 게 아니라는 점이었다!

 

📍3-5) 총 재생 시간 계산

/*
3-5. 특정 사용자가 2024년 재생한 곡들의 총 재생 시간을 확인해 보세요.
*/

SELECT SUM(s.duration_seconds) AS totalsec
FROM history h
LEFT JOIN songs s
ON h.song_id = s.id
WHERE (h.user_id = 16) AND (h.played_at LIKE '2024%');

 

이 문제는 보기엔 쉬워 보였는데, 나는 처음에 조금 돌아갔다. 사용자가 2024년에 재생한 곡들의 총 재생 시간을 구해야 하니 결국 곡의 길이 정보가 있는 songs와 재생 기록인 history를 조인한 뒤 SUM(duration_seconds)를 하면 되는 문제였다. 그런데 나는 처음에 곡 하나하나의 시간까지 신경 쓰면서 괜히 복잡하게 생각했다. 사실은 전체 합만 구하면 되는 문제였는데, 스스로 어렵게 꼬아서 생각한 셈이었다. 이런 경험을 하면서 오히려 문제를 있는 그대로 읽는 것도 꽤 중요하다는 걸 느꼈다.

 

📍3-6) 새롭게 발견한 아티스트 찾기

/*
3-6. 특정 사용자가 2024년에 새롭게 발견한 아티스트 목록을 확인해 보세요.
*/

SELECT DISTINCT ar.name
FROM history h
LEFT JOIN songs s
ON h.song_id = s.id
LEFT JOIN albums a
ON s.album_id = a.id
LEFT JOIN artists ar
ON a.artist_id = ar.id
WHERE h.user_id = 21
  AND YEAR(h.played_at) = 2024
  AND ar.id NOT IN (
      SELECT DISTINCT ar2.id
      FROM history h2
      LEFT JOIN songs s2
      ON h2.song_id = s2.id
      LEFT JOIN albums a2
      ON s2.album_id = a2.id
      LEFT JOIN artists ar2
      ON a2.artist_id = ar2.id
      WHERE h2.user_id = 21
        AND YEAR(h2.played_at) <= 2023
  );

 

이 문제는 특정 사용자가 2024년에 새롭게 발견한 아티스트 목록을 확인하는 문제였다. 여기서 새롭게 발견한 아티스트라는 것은 2024년에는 들었지만 2023년까지는 들은 적이 없는 아티스트를 의미한다. 그래서 이 문제는 2024년에 들은 아티스트 목록에서 2023년까지 들었던 아티스트를 제외하는 방식으로 풀었다. 우선 바깥 쿼리에서는 특정 사용자가 2024년에 들은 아티스트를 조회해야 하므로 history, songs, albums, artists 테이블을 차례대로 LEFT JOIN 시켜주었다. 그리고 WHERE절에서 사용자 id가 21인 조건과 2024년 조건을 걸어 2024년에 들은 기록만 남겨주었다. 조회할 것은 아티스트 이름이므로 ar.name을 가져왔고, 같은 아티스트가 여러 번 나올 수 있으므로 DISTINCT로 중복을 제거해주었다. 그리고 NOT IN 안의 서브쿼리에서는 이 사용자가 2023년까지 이미 들었던 아티스트 id 목록을 구해주었다. 구조는 바깥 쿼리와 거의 같고, 조건만 YEAR(h2.played_at) <= 2023으로 바꿔주었다. 마지막으로 바깥 쿼리에서 ar.id NOT IN (...) 조건을 걸어주면, 2024년에 들은 아티스트 중에서 이전에 들은 적 없는 아티스트만 남게 된다. 이 문제는 처음에는 좀 헷갈렸지만 2024년에 들은 아티스트 - 2023년까지 들은 아티스트라고 생각하니까 구조가 보여서 풀 수 있었다.

SELECT ar.id
	, ar.name
FROM history h
INNER JOIN songs s ON h.song_id = s.id
INNER JOIN albums al ON s.album_id = al.id
INNER JOIN artists ar ON al.artist_id = ar.id
WHERE h.user_id = 1
GROUP BY ar.id, ar.name
HAVING MIN(h.played_at) >= '2024-01-01'
	AND MIN(h.played_at) < '2025-01-01'
ORDER BY ar.name
;

 

강사님께서는 이 문제를 조금 다르게 푸셨다. 나는 2024년에 들은 아티스트에서 2023년까지 들었던 아티스트를 제외하는 방식으로 풀었는데, 강사님은 아티스트별 최초 재생일이 2024년인지 확인하는 방식으로 푸셨다. 즉, 해당 사용자가 그 아티스트를 처음 들은 시점이 2024년 안에 있으면 새롭게 발견한 아티스트로 보는 것이다. 이 방식은 NOT IN 서브쿼리를 쓰지 않아도 되어 더 간단하고 깔끔하게 느껴졌다. 그리고 날짜 조건도 YEAR()로 비교하기보다 날짜 범위로 직접 비교하는 방식이 더 정석적인 방법이라는 점도 같이 알게 되었다.

🎤파트 4. 나의 음악 감상 패턴 발견하기

파트 4는 강사님께서도 미리 어렵다고 하셨던 구간이었다. 실제로 이 네 문제를 푸는 데만 1시간 반 정도 걸렸고, 내가 가장 헷갈려하던 서브쿼리가 본격적으로 들어가면서 난이도가 확 올라갔다. 여기서는 사용자 id가 21인 사람을 기준으로 문제를 풀었다. 이 파트의 핵심은 단순 조회가 아니라 비교와 비율이었다. 상위 몇 퍼센트인지, 다른 사용자 평균보다 더 많이 들은 곡은 무엇인지, 요일별/시간대별 재생 비율은 어떤지 같은 문제들은 한 번에 바로 뽑기 어렵고, 먼저 중간 결과를 만든 뒤 그것을 다시 바깥 쿼리에서 활용하는 구조가 많았다.

 

📍4-1) 특정 아티스트 감상 기준 상위 몇 퍼센트인지 구하기

/*
4-1. 특정 사용자가 2024년에 특정 아티스트의 곡을 들은 사용자들 중에서 감상 횟수 기준으로 상위 몇 퍼센트(%)에 속하는지 확인해 보세요.
*/

SELECT userlist, top
FROM (SELECT userlist, (ROW_NUMBER() OVER (ORDER BY playcount DESC) / COUNT(playcount) OVER ()) * 100 AS top
    FROM (SELECT h.user_id AS userlist, COUNT(h.id) AS playcount
          FROM history h
          LEFT JOIN songs s
          ON h.song_id = s.id
          LEFT JOIN albums a
          ON s.album_id = a.id
          LEFT JOIN artists ar
          ON a.artist_id = ar.id
          WHERE (h.played_at LIKE '2024%') AND (ar.id = 195)
          GROUP BY h.user_id) AS t) AS t2
WHERE userlist = 21;

 

이 문제는 이번 미션에서 가장 오래 걸린 문제였다. 문제 자체를 처음 보면 막막한데 하나씩 쪼개보니 구조가 보였다.

• 먼저 2024년 기준으로 특정 아티스트를 들은 사용자별 재생 수를 구한다.
• 그 다음 그 사용자들 사이에서 순위를 매긴다.
• 마지막으로 내가 고른 특정 사용자가 그 중 몇 퍼센트 위치에 있는지 확인한다.

즉 한 번에 해결하려고 하면 어렵지만 중간 집계 결과를 먼저 만든다 → 그 결과로 순위를 계산한다 → 마지막에 특정 사용자만 뽑는다 이 순서으로 보니까 조금 정리가 됐다. 여기서 ROW_NUMBER()와 COUNT() OVER()를 함께 사용하였다. 지금까지는 GROUP BY 중심의 집계에 익숙했는데, 윈도우 함수까지 들어가니까 SQL이 확실히 더 넓게 느껴졌다. 다만 서브쿼리 안에 또 서브쿼리가 들어가는 구조를 직접 짜는 건 아직 쉽지 않았다. 머릿속으로는 흐름이 보이는데 그걸 SQL 문장 구조로 옮기는 데 시간이 오래 걸렸던 것 같다.

WITH artist_listens AS (
	SELECT h.user_id
		, COUNT(h.id) AS played_count
		, PERCENT_RANK() OVER(ORDER BY COUNT(h.id) DESC) AS pct_rnk
	FROM history h
	INNER JOIN songs s ON h.song_id = s.id
	INNER JOIN albums al ON s.album_id = al.id
	WHERE h.played_at >= '2024-01-01'
		AND h.played_at < '2025-01-01'
		AND al.artist_id = 20
	GROUP BY h.user_id)

SELECT *
FROM artist_listens
WHERE user_id = 1
;

 

강사님께서는 이 문제를 나처럼 여러 번의 서브쿼리로 풀기보다, WITH 구문으로 사용자별 재생 수를 먼저 정리한 뒤 PERCENT_RANK()를 사용해서 상위 비율을 구하는 방식으로 푸셨다. 나는 ROW_NUMBER()와 전체 인원 수를 이용해 직접 퍼센트를 계산했는데, 강사님 방식은 순위 계산용 함수가 바로 들어가 있어서 구조가 더 짧고 깔끔하게 느껴졌다.

 

📍4-2) 내가 많이 듣지만 다른 사람들은 많이 듣지 않는 곡 찾기

/*
4-2. 특정 사용자가 들은 곡 중 다른 사용자들은 많이 듣지 않는 곡을 찾아보세요.
(특정 사용자의 감상 횟수와 전체 사용자들의 평균 감상 횟수를 비교)
*/

SELECT t2.title, t2.playcount21, t3.avgplay
FROM (SELECT s.title AS title, COUNT(h.id) AS playcount21
           FROM history h
           LEFT JOIN songs s
           ON h.song_id = s.id
           WHERE h.user_id = 21
           GROUP BY title) AS t2
LEFT JOIN (SELECT title, AVG(playcount) AS avgplay
           FROM (SELECT h.user_id AS user, s.title AS title, COUNT(h.id) AS playcount
                 FROM history h
		             LEFT JOIN songs s
                 ON h.song_id = s.id
                 GROUP BY user, title) AS t
           GROUP BY title) AS t3
ON t2.title = t3.title
WHERE t2.playcount21 > t3.avgplay
ORDER BY (t2.playcount21 - t3.avgplay) DESC;

 

이 문제도 정말 오래 걸렸다. 특정 사용자 한 명의 감상 횟수만 보는 게 아니라 그 사용자의 감상 횟수와 전체 사용자 평균을 비교해야 했기 때문이다. 결국 이 문제는 다음 두 결과를 따로 만든 뒤 붙이는 구조였다.

• 특정 사용자가 곡별로 몇 번 들었는지
• 전체 사용자는 그 곡을 평균적으로 몇 번 들었는지

이 두 결과를 각각 서브쿼리로 만든 다음 조인해서 비교했다. 즉, 이번 문제는 한 번에 바로 답을 뽑는 것보다 비교를 위해 필요한 중간 테이블을 먼저 만든다는 사고가 더 중요했다. 이 과정을 직접 해보면서 서브쿼리가 왜 필요한지 조금 알 것 같았다. 단순히 어려운 문법이라서 쓰는 게 아니라 중간 결과를 이름 붙여 두고 다음 단계에서 활용하기 위해서 쓰는 것이었다.

WITH single_history AS (
	SELECT song_id
		, COUNT(id) AS one_played_count
	FROM history
	WHERE user_id = 1
	GROUP BY song_id
), others_history AS (
	SELECT song_id
		, AVG(other_played_count) AS avg_played_count
	FROM (
		SELECT user_id
			, song_id
			, COUNT(id) AS other_played_count
		FROM history
		WHERE user_id != 1
		GROUP BY user_id, song_id) t
	GROUP BY song_id
)

SELECT sh.song_id
	, sh.one_played_count
    , oh.avg_played_count
FROM single_history sh
LEFT JOIN others_history oh ON sh.song_id = oh.song_id
WHERE sh.one_played_count > IFNULL(oh.avg_played_count, 0)
ORDER BY sh.one_played_count - IFNULL(oh.avg_played_count, 0) DESC
;

 

강사님께서는 이 문제를 풀 때도 WITH 구문을 사용해 특정 사용자의 재생 기록과 다른 사용자들의 평균 재생 기록을 먼저 나눠 정리한 뒤 비교하는 방식으로 푸셨다. 나는 서브쿼리를 두 개 만든 뒤 조인해서 비교했는데, 강사님 방식은 각 단계가 이름으로 분리되어 있어서 구조를 이해하기가 조금 더 쉬운 풀이라고 느껴졌다. 또 강사님은 전체 사용자 평균이 없는 경우를 고려해서 IFNULL() 처리도 해주셨는데, 이런 부분까지 챙기면 쿼리가 더 안정적으로 작성될 수 있다는 점도 같이 배울 수 있었다.

 

📍4-3) 요일별 재생 비율

/*
4-3. 특정 사용자의 요일별 음악 재생 비율을 확인해 보세요.
*/

SELECT dayofweek, CONCAT((daycount / SUM(daycount) OVER()) * 100, '%') AS ratio
FROM (SELECT DAYOFWEEK(played_at) AS dayofweek, COUNT(id) AS daycount
      FROM history
      WHERE user_id = 21
      GROUP BY dayofweek) AS t
ORDER BY dayofweek;

 

이 문제는 파트 4 앞 문제들보다는 덜 복잡했지만 그래도 서브쿼리 구조를 이해하는 연습이 됐다. 우선 사용자 21의 요일별 재생 수를 먼저 구하고, 그 결과를 바깥 쿼리에서 전체 재생 수와 비교해서 비율을 계산하는 방식이었다. 즉, 

• 요일별 재생 횟수를 먼저 구한 뒤,
• 그 횟수를 전체 합으로 나누어 비율을 구하는

2단계 구조로 접근해야 했다. 처음엔 계산식 자체는 머릿속에서 바로 떠올랐다. 요일별 재생 수 / 전체 재생 수 * 100
문제는 그걸 SQL 안에서 어떻게 끊어서 쓰느냐였다. 이번 문제를 하면서 서브쿼리는 단순히 어려운 기능이 아니라 계산 단계를 나누어 주는 도구라는 생각이 조금 들었다.

SELECT DAYNAME(played_at) AS day_name
-- 	, COUNT(id) AS played_count
--  , SUM(COUNT(id)) OVER() AS total_played_count
    , ROUND(COUNT(id) / SUM(COUNT(id)) OVER() * 100, 1) AS played_ratio
FROM history
WHERE user_id = 1
GROUP BY DAYNAME(played_at)
ORDER BY FIELD(day_name
				, 'Monday'
				, 'Tuesday'
				, 'Wednesday'
				, 'Thursday'
				, 'Friday'
				, 'Saturday'
				, 'Sunday')
;

 

강사님께서는 이 문제를 풀 때 나처럼 서브쿼리로 요일별 재생 수를 먼저 구한 뒤 비율을 계산하는 방식이 아니라, 한 번에 요일별 비율을 계산하는 방식으로 푸셨다. 또한 나는 DAYOFWEEK()를 사용해서 숫자 요일로 조회했는데, 강사님은 DAYNAME()을 사용해서 요일명을 바로 보여주는 방식으로 작성하셨다. 그래서 결과를 확인할 때 훨씬 직관적이었고, ROUND()로 비율을 보기 좋게 정리하고 FIELD()로 월요일부터 일요일 순으로 정렬한 점도 더 깔끔하다고 느꼈다. 내 쿼리도 계산 로직 자체는 맞았지만, 출력 형태까지 고려하면 강사님 방식이 더 완성도 있는 쿼리라는 걸 알 수 있었다.

 

📍4-4) 시간대별 재생 비율

/*
4-4. 특정 사용자의 시간대별 음악 재생 비율을 확인해 보세요.
*/

SELECT hour, CONCAT((hourcount / SUM(hourcount) OVER()) * 100, '%') AS ratio
FROM (SELECT HOUR(played_at) AS hour, COUNT(id) AS hourcount
      FROM history
      WHERE user_id = 21
      GROUP BY hour) AS t
ORDER BY hour;

 

이 문제는 바로 위 문제와 구조가 동일했다. 요일 대신 HOUR(played_at)만 사용하면 되었기 때문에, 4-3을 풀고 나서는 거의 바로 해결할 수 있었다. 결국 SQL 문제는 같은 유형을 반복해서 풀수록 속도가 빨라지는 것 같다. 처음 보는 구조는 오래 걸리지만, 한 번 틀을 잡고 나면 그다음 문제는 훨씬 빠르게 풀린다.

SELECT CASE WHEN HOUR(played_at) BETWEEN 6 AND 11 THEN 'morning'
		WHEN HOUR(played_at) BETWEEN 12 AND 17 THEN 'afternoon'
        WHEN HOUR(played_at) BETWEEN 18 AND 23 THEN 'evening'
        ELSE 'night' END AS time_period
-- 	, COUNT(id) AS played_count
--  , SUM(COUNT(id)) OVER() AS total_played_count
    , ROUND(COUNT(id) / SUM(COUNT(id)) OVER() * 100, 1) AS played_ratio
FROM history
WHERE user_id = 1
GROUP BY time_period
ORDER BY FIELD(time_period
				, 'morning'
				, 'afternoon'
				, 'evening'
				, 'night')
;

 

강사님께서는 이 문제를 나처럼 시간 단위 그대로 비율을 구하는 방식이 아니라, CASE WHEN을 사용해서 morning, afternoon, evening, night처럼 시간대를 몇 개의 구간으로 나눈 뒤 비율을 계산하는 방식으로 푸셨다. 그래서 결과가 더 직관적으로 보였고, 시간대별 패턴을 한눈에 파악하기에는 이런 방식이 더 좋겠다고 느꼈다. 또한 ROUND()로 비율을 보기 좋게 정리하고, FIELD()로 시간대 순서를 직접 지정한 점도 결과를 보여주는 측면에서 더 깔끔하다고 느껴졌다.

😱이번 미션에서 특히 어려웠던 점

이번 미션을 하면서 가장 크게 느낀 어려움은 두 가지였다.

 

📍1) 머릿속 로직을 SQL 구조로 바꾸는 과정

문제를 읽으면 아, 이건 먼저 이걸 구하고 그다음에 비교해야겠네 정도까지는 생각이 되는데, 그걸 실제 SQL로 쓰려면 어느 쿼리를 안쪽에 두고 어느 쿼리를 바깥에 둘지, 어떤 컬럼을 별칭으로 빼둘지, GROUP BY를 어느 단계에서 해야 할지를 다 정해야 했다.
특히 서브쿼리 문제는 이 과정에서 한 번만 꼬여도 에러가 나거나 결과가 이상하게 나왔다. 문제를 90%까지는 풀겠는데 나머지 10%에서 자꾸 막히는 이유가 딱 여기 있었던 것 같다.

 

📍2) 조인과 집계를 동시에 생각해야 했던 문제들

이번 미션은 단순 조회 문제보다 조인해서 연결하고, 그 뒤에 그룹화해서 세고, 마지막에 정렬하는 구조가 많았다. 그래서 테이블 구조를 모르면 조인을 못 하고, 조인을 해도 집계 기준을 잘못 잡으면 결과가 틀어졌다. 특히 아티스트 단위 집계나 전체 사용자 평균 비교 문제에서는 이 흐름을 정확히 이해해야 했다.

💎이번 스프린트 미션에서 남은 것

이번 미션은 단순히 SQL 문제를 많이 푼 것 이상의 의미가 있었다. 지금까지 배운 문법들을 실제 데이터 구조 안에서 연결해서 써보는 경험이었기 때문이다. 내가 이번 미션에서 가장 크게 느낀 건 다음과 같다.

 

첫째, SQL은 문법 암기보다 문제를 단계로 나누는 사고가 중요하다.
특히 서브쿼리 문제는 한 줄로 해결하려고 하면 막막하지만 '이 결과를 만든다' → '그걸 바탕으로 다음 계산을 한다' 이런 식으로 쪼개면 조금씩 풀린다.

 

둘째, 조인은 결국 데이터 구조를 이해하는 문제이다.
무작정 외우는 게 아니라, 어떤 정보가 어느 테이블에 들어 있는지만 정확히 알면 필요한 조인 경로가 보인다.

 

셋째, 집계 문제에서는 무엇을 기준으로 묶는지가 핵심이다.
곡별인지, 아티스트별인지, 사용자별인지, 월별인지에 따라 GROUP BY 기준이 달라지고 결과도 완전히 달라진다.

 

넷째, 어려운 문제일수록 중간 결과를 먼저 떠올리는 연습이 필요하다.
특히 이번 파트 4를 하면서 이 점을 정말 많이 느꼈다. 한 번에 완성된 정답을 만들려고 하면 오히려 더 막히고, 중간 결과를 하나씩 만드는 방식으로 가야 덜 헷갈렸다.

 

이번 여섯 번째 스프린트 미션은 지금까지 했던 SQL 문제들 중에서도 꽤 난도가 있는 편이었다. 앞부분은 기본 조회와 조인, 그룹화 위주라서 비교적 수월했지만, 뒤로 갈수록 서브쿼리와 비교 분석이 들어오면서 확실히 어렵게 느껴졌다. 그래도 그만큼 SQL을 단순한 조회 언어가 아니라, 데이터를 구조적으로 다루는 도구로 보는 감각을 조금 더 익힐 수 있었던 미션이었다. 아직 완벽하게 익숙하다고 말하기는 어렵다. 특히 서브쿼리는 여전히 익숙하지 않고 문제를 보고 바로 구조가 떠오르지 않는 순간도 많다. 그래도 예전처럼 아예 손도 못 대는 느낌은 아니고 적어도 어떤 방향으로 접근해야 하는지는 조금씩 보이기 시작했다는 점에서 의미가 컸다. 이번 미션은 SQL 문법을 쓸 줄 안다에서 한 단계 더 가서 SQL로 문제를 어떻게 풀지 생각해보는 연습에 가까웠던 것 같다.