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

1. 월별 매출 성장률 분석 문제

• Chinook 스토어의 월별 총 매출과 전월 대비 성장률을 계산하세요.
• 테이블 구조: Invoice
• 출력 결과: Month (YYYY-MM 형식), MonthlySales (월별 총 매출), GrowthRate (전월 대비 성장률, 소수점 2자리까지)

with t1 as (
    select date_format(InvoiceDate, '%Y-%m') as Month
        , sum(Total) as MonthlySales
    from invoice
    group by date_format(InvoiceDate, '%Y-%m')
)

select * 
    , round((MonthlySales - lag(MonthlySales) over(order by Month))
        / lag(MonthlySales) over(order by Month) * 100 ,2) as GrowthRate
from t1

 

 

2. 장르별 매출 비중 분석 문제

• 문제 설명:판매 데이터를 기준으로 장르별 매출 비중을 계산하세요.
• 테이블 구조: Genre, Track, InvoiceLine
• 출력 결과: GenreName, TotalSales (해당 장르의 총 매출), Percentage (전체 매출에서 해당 장르가 차지하는 비율, 소수점 2자리까지)

select distinct g.Name as GenreName
    , sum(il.Quantity * il.UnitPrice) over(partition by g.Name) as TotalSales
    , round((sum(il.Quantity * il.UnitPrice) over(partition by g.Name) / sum(il.Quantity * il.UnitPrice) over() * 100),2) as Percetage
from invoiceline as il
    inner join track as t on il.TrackId = t.TrackId
    inner join genre as g on t.GenreId = g.GenreId
order by Percetage desc 

 

 

3. ABC 분석 문제

문제 설명: 트랙의 매출 데이터를 기준으로 ABC 분석을 수행하세요.

• A 그룹: 상위 70% 매출
• B 그룹: 70~90% 매출
• C 그룹: 나머지 10% 매출
• 테이블 구조: Track, InvoiceLine
• 출력 결과: TrackId, TrackName, TotalSales, ABCGroup (A, B, C로 분류)

with t1 as (
    select il.TrackId
        , t.Name as TrackName
        , sum(il.Quantity * il.UnitPrice) as TotalSales
    from invoiceline as il
        inner join track as t on il.TrackId = t.TrackId
    group by il.TrackId
), t2 as (
    select * 
        , ntile(10) over(order by TotalSales desc ) as tile
    from t1
)

select TrackId, TrackName,TotalSales
    , case 
        when tile <= 7 then 'A'
        when tile <= 9 then 'B'
        else 'C'
    end as 'ABCGroup'
from t2

 

 

4. 고객 행동 분석 문제

문제 설명:다음 행동 흐름을 기준으로 고객 이탈율을 계산하세요.

• 구매 횟수를 기준으로 고객을 3그룹(1회 구매, 2~3회 구매, 4회 이상 구매)으로 나누고, 각 그룹의 고객 수와 매출 기여도를 계산하세요.
• 테이블 구조: Customer, Invoice
• 출력 결과:PurchaseGroup (1회 구매, 2~3회 구매, 4회 이상 구매), CustomerCount (해당 그룹의 고객 수), RevenueContribution (그룹의 매출 기여도, %)

with t1 as (
    select c.CustomerId
        , count(c.CustomerId) as CustomerCount
        , sum(i.Total) as GroupSales
    from customer as c
        left join invoice as i on c.CustomerId = i.CustomerId
    group by c.CustomerId
), t2 as (
    select * 
        , case 
            when CustomerCount  = 1 then '1회 구매'
            when CustomerCount <=3 then '2-3회 구매'
            else '4회 이상 구매'
        end as PurchaseGroup
        , sum(GroupSales) over() as TotalSales
    from t1
)

select PurchaseGroup
    , sum(CustomerCount) as CustomerCount
    , sum(GroupSales / TOtalSales *100) as RevenueContribution 
from t2
group by PurchaseGroup

 

 

5. 고객별 평생가치(Lifetime Value) 추적

목적 : 고객별 구매 이력을 기준으로 누적 매출액과 각 구매의 순위를 계산하여 평생가치를 측정합니다.

출력해야 할 컬럼

1. CustomerId: 고객 ID.
2. InvoiceDate: 구매 날짜.
3. InvoiceTotal: 해당 구매의 금액.
4. CumulativeSales: 누적 매출액.
5. Rank: 고객별 구매 순서.

사용해야 할 테이블과 컬럼

• 테이블: Invoice
• 컬럼: CustomerId, InvoiceDate, Total

select CustomerId
    , InvoiceDate
    , Total
    , sum(Total) over(partition by CustomerId order by InvoiceDate rows unbounded preceding) as CumulativeSales
    , rank() over(order by InvoiceDate asc) as 'Rank'
from invoice

 

 

6.구매 주기 분석 (Customer Purchase Cycle Analysis)

목적: 고객별로 구매 간격 평균을 분석하여 고객 행동 패턴을 이해합니다.

출력해야 할 컬럼

1. CustomerId: 고객 ID.
2. AveragePurchaseCycle (Days): 고객의 평균 구매 간격 (일수).

사용해야 할 테이블과 컬럼

• 테이블: Invoice
• 컬럼: CustomerId, InvoiceDate

with t1 as (
    select CustomerId
        , InvoiceDate
        , datediff(lead(InvoiceDate) over(partition by CustomerId), InvoiceDate) as p_cycle
    from invoice
)

select CustomerId
    , avg(p_cycle) as 'AveragePurchaseCycle (Days)'
from t1
group by CustomerId