11. 최고 매출 가수 찾기
- 최고 매출을 기록한 아티스트(Artist)의 이름과 총 매출을 구하세요.
with t1 as (
select a.ArtistId
, sum(il.Quantity * il.UnitPrice) as total_sales
from invoiceline as il
inner join track as t on il.TrackId = t.TrackId
inner join album as a on t.AlbumId = a.AlbumId
group by a.ArtistId
order by total_sales desc
limit 1
)
select a.*, t1.total_sales
from artist as a
inner join t1 on a.ArtistId = t1.ArtistId
12. 장르별 가장 비싼 트랙 찾기
- 각 장르별로 가장 비싼 트랙의 이름과 가격을 구하세요. --> 장르별로 트랙 가격이 다 같음...
with t1 as (
select g.*, t.TrackId, t.UnitPrice, t.Name as trackNamet.GenreId, max(il.UnitPrice)
from track as t
inner join genre as g on g.GenreId = t.GenreId
inner join invoiceline as il on il.TrackId = t.TrackId
)
select genreId, Name, UnitPrice, trackName
from t1
where (genreId, UnitPrice) in (select genreId, max(UnitPrice)
from t1
group by genreId, Name)
13. 고객 유지율 분석
- 2번 이상 구매한 고객(Customer) 수와, 그렇지 않은 고객 수를 계산하세요.
with t1 as (
select CustomerId
, count(distinct InvoiceId) as cnt
from invoice
group by CustomerId
having count(distinct InvoiceId) >= 2
order by CustomerId
)
select count(*) as cnt_2
from t1
14. 월별 매출 분석
select year(InvoiceDate) as year
, month(InvoiceDate) as month
, sum(Total) as sum_total
from invoice
group by year(InvoiceDate), month(InvoiceDate)
15. 특정 직원의 고객 지원 기여도
- 직원(Employee)별로 지원한 고객(Customer)의 수를 계산하세요.
select EmployeeId
, count(distinct ReportsTo) as cnt
from employee
group by EmployeeId
16. 고객 생애 가치(LTV) 모델링
- 각 고객의 평균 구매 주기(일수)와 평균 주문 금액을 계산한 후, 이를 기반으로 1년간 고객 생애 가치를 추정하세요.
- 결과에는 고객 이름, 평균 구매 주기, 평균 주문 금액, LTV를 포함합니다.
- LTV는 다음 공식으로 계산됩니다:
LTV=평균 구매 주기평균 주문 금액×365
with t1 as (
select CustomerId, InvoiceDate as day1
, lead(InvoiceDate, 1) over(partition by CustomerId) as day2
, Total
from invoice
)
select CustomerId
, avg(datediff(day2,day1)) as avg_day
, avg(Total) as avg_sales
, avg(Total) / avg(datediff(day2,day1)) * 365 as LTV
from t1
group by CustomerId
17. 고객 유지율 분석
- 6개월 기간 동안 매월 구매를 유지한 고객의 비율을 계산하세요. -> (6개월 동안 매달 산? or 6개월 동안 1번이라도 구매를 진행한?-> 한달치의 구매 유지율)
- 결과는 월별 고객 유지율과 전체 유지율을 포함합니다.
with t1 as (
select c.CustomerId
, date_format(i.InvoiceDate, '%Y-%m') as y_month
from invoice as i
right join customer as c on c.CustomerId = i.CustomerId
), t2 as (
select y_month, count(CustomerId) as cnt
from t1
group by y_month
)
select *
, sum(cnt) over() as total_cnt
, cnt / sum(cnt) over() * 100 as cnt_pct
from t2
order by y_month
18. 장르 간의 매출 비율 분석
- 각 장르의 매출을 계산하고, 전체 매출에서 해당 장르가 차지하는 비율을 함께 출력하세요.
- 결과는 매출 비율이 높은 순으로 정렬하세요.
with t1 as (
select g.*
, il.UnitPrice * il.Quantity 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
)
select distinct GenreId, Name
, sum(sales) over (partition by GenreId) as g_sum
, sum(sales) over() as total_sum
, sum(sales) over (partition by GenreId) / sum(sales) over() *100 as pct_g_sales
from t1
order by pct_g_sales desc
19. 고객별 LTV 계산 문제
- 문제 설명:
고객(Customer)별 LTV(Lifetime Value)를 계산하세요. LTV는 해당 고객의 전체 구매 금액 합계로 정의됩니다.
- 테이블 구조:* Customer, Invoice
- 출력 결과:
CustomerId, FullName (이름과 성을 합친 값), TotalSpent (고객별 총 구매 금액), Rank (총 구매 금액 기준으로 내림차순 순위)
with t1 as (
select c.CustomerId
, concat(c.FirstName, ' ', c.LastName) as FullName
, sum(i.Total) as TotalSpent
from customer as c
inner join invoice as i on c.CustomerId = i.CustomerId
group by c.CustomerId
)
select *
, rank() over(order by TotalSpent) as 'Rank'
from t1
20. Top 3 아티스트 분석 문제
- 문제 설명: 전체 판매 데이터를 기준으로 가장 많이 팔린 트랙(Track)을 제공한 상위 3명의 아티스트를 찾아보세요.
- 테이블 구조: Track, Album, Artist, InvoiceLine
- 출력 결과:
ArtistId, ArtistName, TotalSales (해당 아티스트의 트랙 총 매출 금액)
with t1 as (
select ar.ArtistId, ar.Name as ArtistName
, il.UnitPrice * il.Quantity as sales
from track as t
inner join album as al on t.AlbumId = al.AlbumId
inner join artist as ar on al.ArtistId = ar.ArtistId
inner join invoiceline as il on il.TrackId = t.TrackId
)
select ArtistId, ArtistName, sum(sales) as TotalSales
from t1
group by ArtistId, ArtistName
order by TotalSales desc
limit 3
