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

문제 1 : 사무실별 최고 매출 직원 조회

• 설명: 각 사무실(offices)에서 가장 많은 매출을 기록한 직원(employeeNumber, firstName, lastName, totalSales)을 조회하세요.
• 난이도: 매우 어려움

with t1 as (
    select od.*, c.customerNumber, c.salesRepEmployeeNumber, quantityOrdered*priceEach as sales
    from orderdetails as od
        inner join orders as o on od.orderNumber = o.orderNumber
        inner join customers as c on c.customerNumber = o.customerNumber
), t2 as (
    select officeCode
        , firstName
        , lastName
        , salesRepEmployeeNumber, sum(sales) as total_sales
        , dense_rank() over(partition by officeCode order by sum(sales)) as rnk
    from t1
        inner join employees as e on t1.salesRepEmployeeNumber = e.employeeNumber
    group by officeCode, salesRepEmployeeNumber
)

select salesRepEmployeeNumber as employeeNUmber
    , firstName
    , lastName
    , total_sales
from t2
where rnk = 1

 

문제 2 : 평균 주문 금액 이상의 결제 기록만 있는 고객

• 설명: 모든 결제(payments) 기록이 전체 평균 결제 금액 이상인 고객의 customerNumber, customerName을 조회하세요.
• 난이도: 매우 어려움

with t1 as (
    select c.customerNumber, c.customerName, p.amount
        , avg(p.amount) over() as avg_amount
    from customers as c
     inner join payments as p on c.customerNumber = p.customerNumber
 )

 select customerNumber, customerName
 from t1
 where amount >= avg_amount

 

문제 3 : 각 직원이 처리한 고객의 총 결제 금액

• 설명: 각 직원(employees)이 담당한 고객(customers)이 결제한 총 금액을 계산하여, employeeNumber, lastName, firstName, totalPaymentAmount을 조회하세요.
• 난이도: 어려움

select e.employeeNumber, e.lastName, e.firstName, sum(p.amount) as totalPaymentAmount
from customers as c
    inner join employees as e on c.salesRepEmployeeNumber = e.employeeNumber
    inner join payments as p on c.customerNumber = p.customerNumber
group by e.employeeNumber

 

문제 4 : 제품 가격대별 주문 수량 분석

• 설명: products 테이블에서 buyPrice 구간을 10씩 나누어(0-10, 10-20, ...) 각 구간별 총 주문 수량(quantityOrdered)을 조회하세요.
• 난이도: 매우 어려움

with t1 as (
    select buyPrice
        , (buyPrice div 10)*10 as bp_range
    from products
)

select bp_range, count(*) as quantityOrdered
from t1
group by bp_range
order by bp_range asc

 

문제 5 : 연도별 평균보다 높은 매출을 올린 주문 조회

• 설명: 각 연도별 평균 매출 금액을 계산하고, 해당 연도 평균보다 높은 매출을 기록한 주문(orderNumber, orderDate, totalOrderAmount)을 조회하세요.
• 난이도: 매우 어려움

with t1 as (
    select year(orderDate) as years
        , orderDate
        , o.orderNumber, quantityOrdered, priceEach
        , quantityOrdered * priceEach as sales
        , avg(quantityOrdered * priceEach) over (partition by year(orderDate)) as avg_sales
    from orders as o
        inner join orderdetails as od on o.orderNumber = od.orderNumber
)

select orderNumber, orderDate, sales as totalOrderAmount
from t1
where sales >= avg_sales