[251118] 내배캠 D+21

최다빈·2025년 11월 18일

SQL

목록 보기

1/1

Music Domain SQL Practice (Subquery & JOIN Skills)

📌 TIL

오늘은 Spotify/Melon/Youtube Music 같은 음악 스트리밍 서비스에서 쓸 법한 DB 스키마를 기반으로 다양한 SQL 실습을 하며, 그 과정 중에서 오답, 실패한 시도, 정답을 위한 과정, 최종 해결 쿼리까지 모두 기록한 TIL이다.

music is my life~~~🎧🎤🎹🔊🎶🎛️

1. 사용한 스키마 정의

오늘 실습에서는 아래와 같은 음악 도메인 스키마를 사용했다.

🎵 Music Domain Schema

-- Artists table
CREATE TABLE artists (
    artist_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    debut_year INT
);

-- Albums table
CREATE TABLE albums (
    album_id INT PRIMARY KEY,
    artist_id INT,
    title VARCHAR(255) NOT NULL,
    release_year INT,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Tracks table
CREATE TABLE tracks (
    track_id INT PRIMARY KEY,
    album_id INT,
    title VARCHAR(255) NOT NULL,
    duration_sec INT,
    genre VARCHAR(50),
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

-- Users table
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    nickname VARCHAR(255),
    signup_date DATE
);

-- Play history table
CREATE TABLE plays (
    play_id INT PRIMARY KEY,
    user_id INT,
    track_id INT,
    played_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);

2. 실습 목표

JOIN 심화 (INNER, LEFT, SELF JOIN)
Subquery 2~4개를 활용한 문제 해결
음악 스트리밍에서 발생할 법한 데이터 분석 상황 가정
GPT에게 문제 제시 요구, 틀리면 왜 틀렸는지 분석
정답으로 가기 위한 사고 과정 기록

3. 실습 문제 & 풀이 과정

🎯 문제 1. "가장 많이 재생된 곡 TOP 10 찾기"

🐞 첫 번째 시도 (틀린 쿼리)

SELECT t.title, COUNT(*) AS play_count
FROM tracks t
JOIN plays p ON t.track_id = p.track_id
ORDER BY play_count DESC
LIMIT 10;

📝 왜 틀렸나?

GROUP BY 없음 → MySQL ONLY_FULL_GROUP_BY 설정이면 에러
MySQL이 허용하더라도 비표준
사실 문제 제대로 안 봐서 틀린 것임.

🐞 두 번째 시도

SELECT t.title, COUNT(*) AS play_count
FROM tracks t
JOIN plays p ON t.track_id = p.track_id
GROUP BY t.title
ORDER BY play_count DESC
LIMIT 10;

❗ 아티스트 정보가 필요하단 걸 놓쳤다 !

→ JOIN을 하나 더 해야 한다 (albums → artists)

🎯 최종 쿼리

SELECT a.name AS artist_name, t.title AS track_title, COUNT(*) AS play_count
FROM plays p
JOIN tracks t ON p.track_id = t.track_id
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
GROUP BY a.name, t.title
ORDER BY play_count DESC
LIMIT 10;

📝 문제 2. "Indie 장르에서 가장 오래된 아티스트 찾기"

🐞 첫 시도 (틀린 쿼리)

SELECT a.name, a.debut_year
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
JOIN tracks t ON al.album_id = t.album_id
WHERE t.genre = 'Indie'
ORDER BY a.debut_year
LIMIT 1;

❌ 문제점

특정 아티스트가 Pop 장르로 여러 곡을 냈으면 중복 발생
DISTINCT 필요 또는 서브쿼리 필요

🐞 중복 제거 시도

SELECT DISTINCT a.artist_id, a.name, a.debut_year
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
JOIN tracks t ON al.album_id = t.album_id
WHERE t.genre = 'Pop'
ORDER BY a.debut_year
LIMIT 1;

하지만 'Pop 장르 트랙을 가진 아티스트 중 데뷔년도가 가장 빠른 사람'을 찾는 더 깔끔한 방식: 서브쿼리 활용

🎯 최종 서브쿼리 기반 풀이

SELECT a.name, a.debut_year
FROM artists a
WHERE a.artist_id IN (
    SELECT DISTINCT al.artist_id
    FROM albums al
    JOIN tracks t ON al.album_id = t.album_id
    WHERE t.genre = 'Pop'
)
ORDER BY a.debut_year ASC
LIMIT 1;

📝 문제 3. "유저별 가장 많이 들은 장르 찾기 (서브쿼리 3개)"

🐞 첫 시도 — 비효율적이고 틀린 접근

SELECT u.nickname, t.genre, COUNT(*) AS cnt
FROM users u
JOIN plays p ON u.user_id = p.user_id
JOIN tracks t ON p.track_id = t.track_id
GROUP BY u.user_id
ORDER BY cnt DESC;

❌ 문제점

유저별 최다 장르를 구하지 못함 → 유저별 집계가 아님
GROUP BY 컬럼 잘못함

🐞 두 번째 시도 — 유저+장르로 그룹화

SELECT u.user_id, t.genre, COUNT(*) AS cnt
FROM users u
JOIN plays p ON u.user_id = p.user_id
JOIN tracks t ON p.track_id = t.track_id
GROUP BY u.user_id, t.genre;

이제 유저별로 장르별 재생 수가 나옴 → 그중 MAX 장르를 뽑으면 됨.

🎯 최종 — 서브쿼리 3단 구성

SELECT final.user_id, final.genre, final.cnt
FROM (
    SELECT ug.user_id, ug.genre, ug.cnt
    FROM (
        SELECT u.user_id, t.genre, COUNT(*) AS cnt
        FROM users u
        JOIN plays p ON u.user_id = p.user_id
        JOIN tracks t ON p.track_id = t.track_id
        GROUP BY u.user_id, t.genre
    ) ug
    JOIN (
        SELECT user_id, MAX(cnt) AS max_cnt
        FROM (
            SELECT u.user_id, t.genre, COUNT(*) AS cnt
            FROM users u
            JOIN plays p ON u.user_id = p.user_id
            JOIN tracks t ON p.track_id = t.track_id
            GROUP BY u.user_id, t.genre
        ) sub
        GROUP BY user_id
    ) mx
    ON ug.user_id = mx.user_id AND ug.cnt = mx.max_cnt
) final;

📝 문제 4. "특정 사용자(user_id = 10)가 가장 좋아하는 아티스트 찾기"

🐞 틀린 시도

SELECT a.name, COUNT(*)
FROM plays p
JOIN tracks t ON p.track_id = t.track_id
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
WHERE p.user_id = 10
GROUP BY a.artist_id;

❌ 문제

가장 좋아하는 = 재생수 최다 → MAX 필요
ORDER BY, LIMIT 추가

🎯 최종

SELECT a.name, COUNT(*) AS play_count
FROM plays p
JOIN tracks t ON p.track_id = t.track_id
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
WHERE p.user_id = 10
GROUP BY a.artist_id, a.name
ORDER BY play_count DESC
LIMIT 1;

4. JOIN 오류 모음 및 교훈

🤦 실수 1 — JOIN 조건 빠뜨림

SELECT *
FROM tracks t
JOIN albums al;

CROSS JOIN 발생 → 예상치 못한 결과

🤦 실수 2 — 컬럼명이 동일한데 테이블 alias 안 붙이기

SELECT title FROM tracks JOIN albums;

ambiguous column 에러

🤦 실수 3 — LEFT JOIN인데 WHERE에서 NULL 제거

SELECT *
FROM artists a
LEFT JOIN albums al ON a.artist_id = al.artist_id
WHERE al.album_id IS NOT NULL;

사실상 INNER JOIN이 되어버림

5. Subquery 실수 모음

🤦 실수 1 — 서브쿼리에서 여러 행 반환되는 걸 WHERE = 로 비교

WHERE artist_id = (SELECT artist_id FROM albums);

🤦 실수 2 — FROM 서브쿼리 alias 누락

SELECT *
FROM (SELECT * FROM tracks);

→ ERROR: Every derived table must have its own alias

🤦 실수 3 — SELECT 서브쿼리에서 LIMIT 없이 MAX 기능 흉내

SELECT (SELECT debut_year FROM artists ORDER BY debut_year);

→ 다중 행 반환 에러

6. 오늘 TIL 요약

SQL 사고 과정, JOIN 구조 분석, 서브쿼리를 왜 사용하는지, INDEX가 필요할 수 있는 지점, 실제 서비스에서 쿼리 최적화가 어떤 식으로 일어나는지 등의 내용을 자세히 서술했다. 또한 문제를 해결하며 겪은 시행착오를 실례로 다시 설명했다.

🔍 1) JOIN을 실제 음악 스트리밍 서비스에서 어떻게 쓰는가

음악 스트리밍 서비스에서는 수많은 테이블이 서로 연결된다. 아티스트–앨범–트랙은 기본 구조이고, 여기에 사용자 정보(users), 재생 기록(plays), 플레이리스트(playlists), 좋아요 정보(likes), 구독 상품(subscription) 같은 데이터가 붙는다. 이런 구조에서 JOIN은 비즈니스 지표를 구축하는 핵심 도구가 된다.

예를 들어, 특정 유저가 가장 많이 듣는 아티스트를 구하는 쿼리는 사용자 추천 모델의 기반이 된다. 또 어떤 장르가 특정 연령대에서 많이 들리는지 확인하면 큐레이션 알고리즘의 근거가 될 수 있다. 그리고 이러한 데이터는 대개 여러 테이블을 한 번에 묶어야 나오기 때문에 JOIN이 필수다.

🔍 2) 서브쿼리를 사용하는 이유

서브쿼리는 크게 두 가지 이유로 쓰게 된다.

중간 결과를 만들기 위해
필터링 조건을 더 정교하게 주기 위해

JOIN만으로는 답을 내기 까다로운 문제도 있고,
필터링 조건이 최대 재생 수를 가진 장르, 특정 장르를 가진 아티스트, 재생 수 상위 5% 안에 드는 사용자처럼 복잡해지면 서브쿼리가 훨씬 직관적이고 유지보수하기도 쉽다.

🔍 3) 실무에서 자주 등장하는 패턴들

오늘 실습 과정에서 자연스럽게 등장한 패턴들은 음악 서비스뿐 아니라 모든 서비스에서 자주 쓰인다.

🧩 패턴 1: 집계 후 다시 JOIN하는 방식

예: 유저별 가장 많이 들은 장르, 유저별 가장 많이 들은 아티스트
이유: GROUP BY user_id, genre 같은 중간 결과에서 MAX(cnt)를 구한 뒤 다시 JOIN해야 원하는 행만 뽑을 수 있기 때문

🧩 패턴 2: DISTINCT 기반 필터링

예: 어떤 아티스트가 특정 장르를 보유했는지 판별
JOIN만으로 중복이 생길 때 DISTINCT 또는 GROUP BY로 정제하는 방식

🧩 패턴 3: FROM 서브쿼리(alias 필수)

복잡한 계산을 한 번에 처리하기 위해 중간 테이블을 만드는 방식
이 패턴을 활용하면 쿼리가 구조화되어 훨씬 읽기 좋아짐

🔍 5) 추가적인 실습을 통해 느낀 것들

쿼리를 작성하기 전에 데이터를 먼저 이미지화해보면 에러가 줄어든다.
- 예: users → plays → tracks → albums → artists
- 이런 선형 관계를 머릿속에서 먼저 그려보면 JOIN 조건을 실수할 가능성이 훨씬 줄어든다.
GROUP BY와 ORDER BY는 의도에 따라 결과가 완전히 달라진다.
- 예: GROUP BY를 잘못하면 전체 기준이 아니라 특정 기준으로 묶여버리고, ORDER BY는 중간 결과 기준인지 최종 결과 기준인지 헷갈리기 쉽다.
서브쿼리는 과용하면 느려질 수 있다.
- 하지만 오늘 같은 문제에서는 오히려 서브쿼리가 더 직관적이었다.
- 실무에서는 index, explain plan을 보고 최적화할 필요가 있다.

🔍 6) 오늘 실습에서 얻은 핵심 정리

JOIN은 많아도 상관없지만, ON 조건이 틀리면 모든 게 틀린다.
서브쿼리는 정답을 뽑는 과정을 계층적으로 표현할 때 매우 강력하다.
실습을 하면서 틀린 쿼리를 먼저 작성하는 것도 공부에 큰 도움이 된다.
음악 스트리밍 구조는 테이블 간 관계가 명확해 SQL 연습하기 좋은 도메인이다.
중첩된 집계는 한 번의 GROUP BY로 절대 해결되지 않는다.
DISTINCT와 GROUP BY는 비슷해 보이지만 목적이 다르다. 상황에 따라 적절히 선택해야 한다.

🔍 7) 마무리: SQL은 결국 사고방식의 문제

SQL은 "문법"보다 "사고 과정"이 훨씬 더 중요하다.

어떤 중간 구조를 만들 것인지
어떤 조건이 데이터의 범위를 결정하는지
어떤 기준으로 집계할지
어떤 순서로 JOIN해야 하는지

이 네 가지를 명확히 하면 서브쿼리든 JOIN이든 자연스럽게 흘러간다.

지금까지 작성한 문제들은 음악 도메인 SQL의 좋은 예시였고, 앞으로도 조금 더 난도 높은 문제(예: 윈도우 함수 포함, 재생 패턴 분석, retention 기반 지표 계산 등)를 이어서 작성해볼 수 있을 것 같다. 재밌었다 오늘~!#@!~@!~

끝~#@~!#~@!!#~!@~!#

최다빈

Running on hopes and tiny skills...

[251118] 내배캠 D+21

SQL

Music Domain SQL Practice (Subquery & JOIN Skills)

📌 TIL

music is my life~~~🎧🎤🎹🔊🎶🎛️

1. 사용한 스키마 정의

🎵 Music Domain Schema

2. 실습 목표

3. 실습 문제 & 풀이 과정

🎯 문제 1. "가장 많이 재생된 곡 TOP 10 찾기"

🐞 첫 번째 시도 (틀린 쿼리)

📝 왜 틀렸나?

🐞 두 번째 시도

❗ 아티스트 정보가 필요하단 걸 놓쳤다 !

🎯 최종 쿼리

📝 문제 2. "Indie 장르에서 가장 오래된 아티스트 찾기"

🐞 첫 시도 (틀린 쿼리)

❌ 문제점

🐞 중복 제거 시도

🎯 최종 서브쿼리 기반 풀이

📝 문제 3. "유저별 가장 많이 들은 장르 찾기 (서브쿼리 3개)"

🐞 첫 시도 — 비효율적이고 틀린 접근

❌ 문제점

🐞 두 번째 시도 — 유저+장르로 그룹화

🎯 최종 — 서브쿼리 3단 구성

📝 문제 4. "특정 사용자(user_id = 10)가 가장 좋아하는 아티스트 찾기"

🐞 틀린 시도

❌ 문제

🎯 최종

4. JOIN 오류 모음 및 교훈

🤦 실수 1 — JOIN 조건 빠뜨림

🤦 실수 2 — 컬럼명이 동일한데 테이블 alias 안 붙이기

🤦 실수 3 — LEFT JOIN인데 WHERE에서 NULL 제거

5. Subquery 실수 모음

🤦 실수 1 — 서브쿼리에서 여러 행 반환되는 걸 WHERE = 로 비교

🤦 실수 2 — FROM 서브쿼리 alias 누락

🤦 실수 3 — SELECT 서브쿼리에서 LIMIT 없이 MAX 기능 흉내

6. 오늘 TIL 요약

🔍 1) JOIN을 실제 음악 스트리밍 서비스에서 어떻게 쓰는가

🔍 2) 서브쿼리를 사용하는 이유

🔍 3) 실무에서 자주 등장하는 패턴들

🧩 패턴 1: 집계 후 다시 JOIN하는 방식

🧩 패턴 2: DISTINCT 기반 필터링

🧩 패턴 3: FROM 서브쿼리(alias 필수)

🔍 5) 추가적인 실습을 통해 느낀 것들

🔍 6) 오늘 실습에서 얻은 핵심 정리

🔍 7) 마무리: SQL은 결국 사고방식의 문제

끝~#@~!#~@!!#~!@~!#

0개의 댓글