SQL | Chinook 데이터를 활용한 쿼리 연습 4)

문제 1: 가장 많이 판매된 장르와 총 매출 계산하기

목표: 가장 많이 판매된 음악 장르와 해당 장르의 총 매출을 구하세요.

힌트:

• Genres와 Tracks, InvoiceLines를 연결하세요.
• 각 장르별로 판매 금액을 집계하세요.
• 매출 기준으로 정렬 후 상위 1개 결과만 가져오세요.

select g.GenreId, g.Name, sum(il.Quantity * il.UnitPrice) as sales
from invoiceline as il
    inner join track as t on il.TrackId = t.TrackId
    inner join genre as g on t.GenreId = g.GenreId
group by g.GenreId, g.Name
order by sales desc

문제 2: 특정 국가에서의 고객별 총 매출 구하기

목표: 'USA'에 거주하는 고객별로 총 매출을 계산하고, 매출 기준 상위 5명의 고객을 구하세요.

힌트:

• Customers와 Invoices를 연결하세요.
• 국가 필터링 후 고객별 매출 집계.

select c.CustomerId, sum(Total) as sales
from invoice as i
    inner join customer as c on i.CustomerId = c.CustomerId
where BillingCountry = 'USA'
group by c.CustomerId
order by sales desc
limit 5

문제 3: 가장 많은 트랙이 포함된 앨범 찾기

목표: 트랙 수가 가장 많은 앨범 이름과 아티스트 이름을 구하세요.

힌트:

• Albums, Tracks, Artists를 연결.
• 앨범별 트랙 수를 집계.

select art.name, a.AlbumId, a.title
    , count(t.TrackId) as track_cnt
from track as t
    inner join album as a on t.AlbumId = a.AlbumId
    inner join artist as art on art.ArtistId = a.ArtistId
group by art.name, a.AlbumId
order by track_cnt desc

문제 4: 월별 총 매출 및 최고 매출 국가 구하기

목표: 월별로 총 매출과 가장 높은 매출을 기록한 국가를 출력하세요.

힌트:

• DATE_FORMAT()을 사용해 InvoiceDate에서 연도와 월을 추출.
• 국가별 매출 집계 후 윈도우 함수로 최고 매출 국가 찾기.

with t1 as (
    select BillingCountry, date_format(InvoiceDate, '%Y-%m') as InvoiceDate
        , sum(Total) as sales
    from invoice
    group by BillingCountry, date_format(InvoiceDate, '%Y-%m') 
), t2 as (
    select * 
        , rank() over(partition by InvoiceDate order by sales desc) as rnk
    from t1
)

select BillingCountry, InvoiceDate, sales
from t2
where rnk = 1
order by InvoiceDate

문제 5: 특정 장르의 매출 비중 구하기

목표: 각 장르의 총 매출과 전체 매출 대비 비중을 계산하세요.

힌트:

• 총 매출을 서브쿼리로 계산해 비율을 구하세요.

with t1 as (
    select g.GenreId, g.Name, sum(il.Quantity * il.UnitPrice) as sales
    from invoiceline as il
        inner join track as t on il.TrackId = t.TrackId
        inner join genre as g on t.GenreId = g.GenreId
    group by g.GenreId, g.Name
)

select *
    , sum(sales) over() as total_sales
    , round(sales / sum(sales) over() * 100,2) as sales_pct
from t1
order by sales_pct desc

문제 6: 고객 유지율 분석

목표: 고객의 첫 구매 이후 평균적으로 몇 개월 동안 재구매가 이루어졌는지 계산하세요.

힌트:

• DATEDIFF() 또는 TIMESTAMPDIFF()를 사용해 첫 구매와 이후 구매 간의 기간을 계산.
• 고객별 평균 구매 간격 산출.

with t1 as (
    select * 
        , lead(InvoiceDate, 1) over(partition by CustomerId order by InvoiceDate asc) as next_date
        , row_number() over(partition by CustomerId order by InvoiceId asc) as id
    from invoice
    order by CustomerId, InvoiceDate
)

select CustomerId, datediff(next_date,InvoiceDate) as next_day
from t1
where id = 1

문제 7: 신규 고객 확보와 매출 성장 분석

목표: 연도별 신규 고객 수와 연도별 매출 증가율(%)을 구하세요.

힌트:

• YEAR()를 사용해 연도를 추출.
• 신규 고객은 Invoices 테이블에서 고객의 첫 구매 연도를 기준으로 계산.

with t1 as (
    select * 
        , year(InvoiceDate) as year
        , row_number() over(partition by CustomerId order by InvoiceDate asc) as rn
    from invoice
), t2 as (
    select year, count(CustomerId) as new_customer
    from t1 
    where rn = 1
    group by year
)

select * 
    , round((lead(new_customer,1) over() - new_customer) / new_customer,2) as pct
from t2

문제 8: 가장 인기 있는 아티스트의 총 매출 구하기

목표: 판매된 트랙 기준으로 가장 인기 있는 아티스트를 찾고, 해당 아티스트의 총 매출을 계산하세요.

힌트:

• 트랙별 판매 수를 집계하고, 아티스트 정보를 연결하세요.
• 매출과 판매량 기준으로 정렬하세요.

with t1 as (
    select il.TrackId, sum(il.Quantity * il.UnitPrice) as sales
        , count(il.TrackId) as cnt
        , art.name
    from invoiceline as il
        inner join track as t on il.TrackId = t.TrackId
        inner join album as a on t.AlbumId = a.AlbumId
        inner join artist as art on art.ArtistId = a.ArtistId
    group by il.TrackId
)

select name, sum(cnt) as cnt
    , sum(sales) as total_sales
from t1 
group by name
order by total_sales desc

문제 9: 특정 연도에 가장 많은 매출을 올린 고객 구하기

목표: 2011년 기준으로, 가장 많은 매출을 올린 고객의 이름과 총 매출을 구하세요.

힌트:

• YEAR() 함수를 이용해 구매 연도를 필터링하세요.
• 고객별 매출을 집계 후 정렬하세요.

select  i.CustomerId, c.FirstName, c.LastName
    , sum(i.Total)  as sales
from invoice as i
    inner join customer as c on i.CustomerId = c.CustomerId
where year(i.InvoiceDate) = 2021
group by i.CustomerId
order by sales desc

문제 10: 국가별 평균 구매 금액과 상위 고객 비율 분석

목표: 각 국가별 평균 구매 금액과 상위 10% 고객이 해당 국가의 총 매출에서 차지하는 비율(%)을 계산하세요.

힌트:

• 각 국가의 총 매출과 고객별 매출을 집계하세요.
• PERCENT_RANK() 윈도우 함수를 사용해 상위 10% 고객을 필터링하세요.
• 비율을 계산하여 국가별로 정리하세요.

with t1 as (
    select i.CustomerId, c.Country
        , sum(i.Total) as sales
        , avg(sum(i.Total)) over(partition by c.Country) as country_avg
        , sum(sum(i.Total)) over(partition by c.Country) as country_sum
        , round(percent_rank() over(partition by c.Country order by sum(i.Total) desc),2) as pct
    from invoice as i
        inner join customer as c on i.CustomerId = c.CustomerId
    group by i.CustomerId
)

select Country
    , sum(sales / country_sum * 100) as pct
from t1
where pct <=0.1
group by Country