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

문제 7 : 고객 이탈 예측 지표 분석

• 주문을 한 지 1년 이상 지난 고객을 이탈 고객으로 가정합니다. 모든 고객에 대해 이탈 고객 여부를 판단하고, 이탈 고객 비율을 계산하는 쿼리를 작성하세요.
• customers, orders 테이블을 사용하세요.
• 가장 최근 주문일을 기준으로 1년 이상 주문하지 않은 고객을 "이탈"로 표시하고, 전체 고객 대비 이탈 고객의 비율을 출력하세요.
• 출력: 총 고객 수, 이탈 고객 수, 이탈 고객 비율 (%)
• 가장 최댓값이 2005-05-31 이므로 이 날을 기준으로 1년 이상 주문하지 않은 고객으로 가정

with t1 as (
    select c.customerNumber
        , max(orderDate) as last_order
    from orders as o
        right join customers as c on o.customerNumber = c.customerNumber
    group by c.customerNumber
)

select count(*) as total_customers
    , sum(case when last_order < date_sub('2005-05-31', interval 1 year) then '1' else 0 end) as outs
    , round(sum(case when last_order < date_sub('2005-05-31', interval 1 year) then '1' else 0 end) / count(*),3) as outs_ratio
from t1

 

문제 8 : 특정 제품군의 주간 판매량 변화 분석

• 특정 제품군(productLine)의 주별 판매량 변화를 분석하려고 합니다. 제품군 이름을 매개변수로 받아, 각 주마다 해당 제품군의 총 판매량과 주별 변화율을 계산하는 쿼리를 작성하세요.
• products, orderdetails, orders 테이블을 사용하세요.
  주별 판매량은 (quantityOrdered * priceEach)로 계산하고, 주별 판매량 변화율은 전주 대비 판매량 변화율로 계산하세요.
• 출력: 연도, 주, 주간 판매량, 주간 판매량 변화율 (%)

with t1 as (
    select o.orderDate 
        , year(o.orderDate) as year_order
        , week(o.orderDate) as week_order
        , p.productLine
        , od.priceEach * od.quantityOrdered as sales
    from orders as o
        left join orderdetails as od on o.orderNumber = od.orderNumber
        left join products as p on od.productCode = p.productCode
), t2 as (
    select year_order, week_order
        , sum(sales) as week_amount_sales
    from t1
    where productline = 'Classic Cars'
    group by year_order, week_order
)

select * 
    , ((lead(week_amount_sales) over () - week_amount_sales) / week_amount_sales) * 100 as week_amount_pct
from t2

 

문제 9 : 매출 상위 10% 제품의 매출 기여도 분석

• 전체 제품 중 매출 상위 10%가 전체 매출에서 차지하는 비율을 분석하는 쿼리를 작성하세요.
• orderdetails, products 테이블을 사용하세요.
• 각 제품의 매출을 계산하여 매출 상위 10% 제품군의 매출 기여도를 출력하세요.
• 출력: 전체 매출, 상위 10% 제품군 매출, 상위 10% 제품군 매출 비율 (%)

with t1 as (
    select productCode
        , sum(quantityOrdered * priceEach) as sales
    from orderdetails
    group by productCode
), t2 as (
    select *
        , ntile(10) over(order by sales desc) as ntile_10
        , sum(sales) over() as total_sales
    from t1
)

select productcode, sales, total_sales
    , sales / total_sales * 100 as sales_pct
from t2
where ntile_10 = 1

select total_sales, sum(sales)
    , sum(sales) / total_sales * 100 as sales_pct
from t2
where ntile_10 = 1
group by total_sales

 

문제 10 : 평균 주문 금액이 높은 고객군의 주문 패턴 분석

• 전체 고객 중 평균 주문 금액 상위 25%에 해당하는 고객군을 추출하고, 이들 고객의 연간 주문 횟수와 평균 주문 금액을 계산하세요.
• customers, orders, orderdetails 테이블을 사용하세요.
• 상위 25% 고객군에 대해 연도별 주문 횟수와 평균 주문 금액을 계산하세요.
• 출력: 연도, 고객 ID, 총 주문 횟수, 연평균 주문 금액

with t1 as (
    select year(o.orderdate) as year_order
        , o.customerNumber
        , od.quantityOrdered * od.priceEach as sales
    from orderdetails as od
        inner join orders as o on od.orderNumber = o.orderNumber
), t2 as (
    select customernumber
        , avg(sales) as avg_sales
        , ntile(4) over (order by avg(sales) desc) as ntile_4
    from t1
    group by customernumber
)

select year_order, customernumber
    , count(customernumber) as sales_cnt
    , avg(sales) as avg_sales
from t1
where customernumber in (select customernumber 
                        from t2
                        where ntile_4 = 1)
group by year_order, customernumber

 

문제 11 : 신규 고객 유입 및 초기 매출 분석

• 새로운 고객이 최초로 주문한 시점과 이후 3개월 동안의 총 매출을 계산하세요.
• customers, orders, orderdetails 테이블을 사용하세요.
• 각 고객의 첫 주문일을 기준으로, 첫 주문 후 3개월 동안 발생한 매출 합계를 계산하세요.
• 출력: customerNumber, 첫 주문일, 3개월 내 총 매출

with t1 as (
    select o.customerNumber, o.orderdate, od.*
    from orders as o
        inner join orderdetails as od on o.orderNumber = od.orderNumber
), t2 as (
    select customernumber, min(orderdate) as first_date
        , date_add(min(orderdate), interval 3 month) as after_3_month
    from t1
    group by customernumber
)

select t2.customernumber, t2.first_date
    , sum(t1.quantityOrdered * t1.priceEach) as total_sales_3_month
from t1
    inner join t2 on t1.customernumber = t2.customernumber
        where t1.orderdate between t2.first_date and t2.after_3_month
group by t2.customernumber
order by t2.customernumber

 

문제 12 : 직원별 담당 고객 매출 상위 비율 분석

• 각 직원이 담당하는 고객의 매출 중 상위 20% 고객의 매출이 차지하는 비율을 분석하세요.
• employees, customers, orders, orderdetails 테이블을 사용하세요.
• 각 직원별로 담당 고객의 매출을 합산한 후, 상위 20% 고객이 차지하는 매출 비율을 계산하세요.
• 출력: 직원번호, 직원이 담당하는 고객의 전체 매출, 상위 20% 고객 매출, 상위 20% 매출 비율 (%)

with t1 as (
    select e.employeeNumber, c.customerNumber, o.orderNumber
        , od.priceEach * od.quantityOrdered as sales
    from customers as c
        inner join employees as e on c.salesRepEmployeeNumber = e.employeeNumber
        inner join orders as o on c.customernumber = o.customernumber
        inner join orderdetails as od on o.ordernumber = od.ordernumber
), t2 as (
    select *
        , ntile(5) over(partition by employeenumber order by sales desc) as ntile_20
    from t1
), t3 as (
    select employeenumber, customernumber
        , sum(sales) over(partition by employeenumber) as total_employee_sales
        , sum(sales) over(partition by customernumber) as total_customer_sales
    from t2
    where ntile_20 = 1
)

select employeenumber, avg(total_employee_sales) as total_employee_sales
    , sum(total_customer_sales) as total_customer_sales
    , sum(total_customer_sales) / avg(total_employee_sales) as pct
from t3
group by employeenumber
order by employeenumber

 

문제 13 : 제품 공급자별 매출 기여도 분석

• 제품 공급자(productVendor)별로 총 매출을 계산하고, 전체 매출에서 차지하는 비율을 구하세요.
• products, orderdetails, orders 테이블을 사용하세요.
• 각 공급자의 매출을 구하고, 공급자별로 전체 매출에서 차지하는 비율을 계산하여 내림차순으로 정렬하세요.
• 출력: productVendor, 총 매출, 전체 매출 대비 비율 (%)

with t1 as (
    select p.productVendor, p.productCode
        , sum(od.priceEach * od.quantityOrdered) as sum_sales
    from orderdetails as od 
        inner join products as p on p.productCode = od.productCode
    group by p.productVendor, p.productCode
)

select productvendor
    , sum(sum_sales) over(partition by productvendor) as total_sales
    , sum_sales
    , round(sum_sales / sum(sum_sales) over(partition by productvendor) *100,2) as pct
from t1 
order by pct desc