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

문제 1: 특정 고객의 총 구매 금액 확인

문제

가장 많이 구매한 고객의 CustomerId, 이름(FirstName 및 LastName), 그리고 총 구매 금액(Total)을 구하세요.

 

힌트

• Invoices 테이블의 Total 컬럼 사용.
• GROUP BY와 ORDER BY를 활용.

select c.CustomerId
    , c.FirstName
    , c.LastName
    , count(*) as cnt
    , sum(i.Total) as total_sales
from invoice as i
    inner join customer as c on i.CustomerId = c.CustomerId
group by c.CustomerId
order by cnt desc, total_sales desc

 

 

 

문제 2: 특정 기간 동안의 매출 분석

문제

2009년 1월 1일부터 2011년 12월 31일까지 발생한 매출의 월별 총합을 구하세요. 결과는 연도, 월, 그리고 총 매출을 포함해야 하며, 매출은 내림차순으로 정렬하세요.

 

힌트

• Invoices 테이블에서 InvoiceDate를 활용.
• DATE_FORMAT() 함수를 사용하여 연도와 월 추출.

select date_format(InvoiceDate, '%Y-%m') as year_months
	, sum(Total) as total_sales
from invoice
group by date_format(InvoiceDate, '%Y-%m')
order by date_format(InvoiceDate, '%Y-%m')

 

 

 

문제 3: 인기 있는 장르 분석

문제

가장 많이 팔린 상위 3개 장르의 이름과 각 장르의 총 판매 트랙 수를 구하세요.

 

힌트

• Tracks, InvoiceLines, Genres 테이블 간 조인이 필요.
• LIMIT을 활용.

select g.GenreId, g.Name
	, count(*) as cnt
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 cnt desc

 

 

 

문제 4: 직원별 매출 기여도 계산

문제

각 직원(EmployeeId)이 담당한 고객이 생성한 청구서(Invoices)의 총 매출을 계산하고, 직원별 매출의 비율을 출력하세요.

 

힌트

• Employees와 Customers 간 조인을 활용.
• 매출 비율 계산은 총 매출 대비 직원별 매출의 비율로 계산.

with t1 as (
	select e.EmployeeId
		, sum(i.Total) as e_sales
	from employee as e
		inner join customer as c on e.EmployeeId = c.SupportRepId
		inner join invoice as i on i.CustomerId = c.CustomerId
	group by e.EmployeeId
)

select employeeId
	, round(e_sales / sum(e_sales) over(),2) as e_sales_pct
from t1

 

 

 

문제 5: 재생 목록의 음악 다양성 평가

문제

각 재생 목록(Playlists)에 포함된 서로 다른 장르(Genres)의 수를 계산하고, 장르의 수가 많은 순으로 정렬하세요.

 

힌트

• Playlists, PlaylistTrack, Tracks, Genres 간 다중 조인이 필요.
• COUNT(DISTINCT ...)를 활용.

select p.PlaylistId, p.Name
	, count(distinct g.GenreId) as g_cnt
from track as t
	inner join playlisttrack as pt on t.TrackId = pt.TrackId
    inner join playlist as p on pt.PlaylistId = p.PlaylistId
    inner join genre as g on g.GenreId = t.GenreId
group by p.PlaylistId, p.Name
order by g_cnt desc

 

 

 

문제 6. 직책별 평균 근속 연수 분석

문제

직책(Title)별 평균 근속 연수를 계산하고, 가장 오래 근속한 직책 순으로 정렬하세요.

 

힌트

• 근속 연수는 CURRENT_DATE와 HireDate의 차이를 계산하여 구합니다.
• GROUP BY와 집계 함수를 활용하세요.

with t1 as (
	select EmployeeId
		, LastName, FirstName
		, title
		, round(datediff(current_date(),HireDate) / 365,0) as hire_year
	from employee
)

select title
	, avg(hire_year)
from t1
group by title
order by avg(hire_year) desc

 

 

 

문제 7. 국가별 첫 구매 고객 분석

문제

각 국가별로 첫 구매를 한 고객 수를 구하고, 이들이 전체 고객에서 차지하는 비율을 계산하세요.

• 결과를 국가별로 첫 구매 고객 수가 많은 순으로 정렬하여 출력합니다.
  • 전체 첫 구매 고객들 중 나라별로 구분해서 비율 계산

with t1 as (
	select c.CustomerId, i.BillingCountry
		, min(i.InvoiceDate) as first_date
	from invoice as i
		inner join customer as c on i.CustomerId = c.CustomerId
	group by c.CustomerId, i.BillingCountry
)

select BillingCountry
	, count(customerId) as cnt
	, round(count(customerId) / sum(count(customerId)) over() *100,2) as pct
from t1
group by BillingCountry
order by pct desc

 

 

 

문제 8. 첫 구매 경험 분석

문제

각 고객의 첫 구매 트렌드를 분석하세요.

• 각 고객의 첫 구매 날짜, 구매한 총 트랙 수, 지출 금액, 가장 많이 구매한 장르를 구하세요.
• 장르별 첫 구매 비율(%)을 계산하여 첫 구매 경험이 어느 장르에서 집중되는지 파악하세요.

with t1 as (
	select c.customerId, i.invoiceDate, t.trackId, i.Total, g.name
		, rank() over(partition by c.CustomerId order by i.InvoiceDate desc) as rnk
	from invoiceline as il
		inner join invoice as i on i.InvoiceId = il.InvoiceId
		inner join customer as c on i.CustomerId = c.CustomerId
        inner join track as t on il.TrackId = t.TrackId
        inner join genre as g on t.GenreId = g.GenreId
), t2 as (
	select customerId, name
		, date(min(invoiceDate)) as first_date
		, count(*) as g_cnt
		, sum(count(*)) over(partition by customerId) as c_cnt
		, round(count(*) / sum(count(*)) over(partition by customerId) * 100,2) as pct
		, avg(total) as total
	from t1
	where rnk = 1
	group by customerId, name
	order by customerId, pct desc
)

select * 
from t2

 

 

 

문제 9. 고객의 반복 구매 분석

문제

고객들의 반복 구매 및 일회성 구매를 분석하고, 반복 구매 고객 비율을 계산하세요.

• 반복 구매 고객(구매 횟수 > 1)과 일회성 구매 고객(구매 횟수 = 1)의 수를 구하세요.
• 반복 구매 고객의 비율을 전체 고객에서 차지하는 비율로 계산하세요.

select c.CustomerId
	, count(distinct i.InvoiceId) as cnt
    , case when count(distinct i.InvoiceId) > 1 then '반복구매'
		else '일회성구매'
	end as '반복구매고객'
from invoiceline as il
	inner join invoice as i on i.InvoiceId = il.InvoiceId
	inner join customer as c on i.CustomerId = c.CustomerId
group by c.CustomerId

 

 

 

문제 10. VIP 고객의 장르별 소비 분석

문제

VIP 고객(전체 고객 중 상위 10%의 지출 고객)에 대해 각 장르별로 소비 분석을 수행하세요.

• VIP 고객이 구매한 장르별로 구매 트랙 수, 평균 구매 금액을 구하세요.
• 가장 많이 구매한 장르 순으로 정렬된 결과를 출력하세요.

with t1 as (
	select c.CustomerId
		, sum(total) as total_sales
		, percent_rank() over(order by sum(total) desc) as percent
	from invoice as i
		inner join customer as c on i.CustomerId = c.CustomerId
	group by c.CustomerId
	order by total_sales desc
)

select g.name
	, count(t.TrackId) as buy_cnt
    , avg(i.total) as avg_total
from invoice as i
	inner join customer as c on i.CustomerId = c.CustomerId
    inner join invoiceline as il on i.InvoiceId = il.InvoiceId
    inner join track as t on il.TrackId = t.TrackId
    inner join genre as g on t.GenreId = g.GenreId
where c.CustomerId in (select CustomerId
					from t1
					where percent <= 0.1)
group by g.name
order by buy_cnt desc