[SQL/Classicmodels Database] 틀렸던 문제 풀기 (오답정리)

Hyeon·2024년 11월 21일
sql

SQL 문제 풀이

목록 보기
45/61

틀렸던 문제 다시보기!
classicmodels을 이용해서 데이터 다운 받은 다음
chat gpt 을 통해서 만든 13문제가 있었다.
이 중 틀렸던 문항을 다시 한번 복습하고자 풀어보았다..!
출처: kaggle 출처

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

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

정답

-- use classicmodels;

SELECT officeCode, employeeNumber, firstName, lastName, totalSales
FROM (
    SELECT e.officeCode, e.employeeNumber, e.firstName, e.lastName, SUM(od.quantityOrdered * od.priceEach) AS totalSales,
           RANK() OVER (PARTITION BY e.officeCode ORDER BY SUM(od.quantityOrdered * od.priceEach) DESC) AS rank_1
    FROM employees e
    JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
    JOIN orders o ON c.customerNumber = o.customerNumber
    JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY e.officeCode, e.employeeNumber, e.firstName, e.lastName
) AS rankedSales

WHERE rank_1 = 1;

정답 수정

select officeCode,employeeNumber , firstName, lastName, totalSales
from
(
select officeCode,a.employeeNumber , firstName, lastName, sum(priceEach * quantityOrdered) as totalSales,
rank() over (partition by officeCode order by sum(priceEach * quantityOrdered) desc) as ranking

from employees a inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber inner join orders c on b.customerNumber = c.customerNumber 
inner join orderdetails d on c.orderNumber = d.orderNumber

group by officeCode,a.employeeNumber , firstName, lastName
) t
where ranking =1
;

틀렸던 점

rank() over () 구문에 order by 뒤에 desc 을 안넣어줌
가장 위에 있는 (높은 값에 속해있는)것을 출력해야하니까
내림차순 옵션 필수!

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

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

정답

SELECT CASE
           WHEN p.buyPrice < 10 THEN '0-10'
           WHEN p.buyPrice < 20 THEN '10-20'
           WHEN p.buyPrice < 30 THEN '20-30'
           ELSE '30+'
       END AS priceRange,
       SUM(od.quantityOrdered) AS totalQuantityOrdered
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY priceRange;

### 정답 수정

>각 구간별 총 주문 수량을 구하는것이니까 주문 개수의 합계를 구해야한다.
총 주문 수량은 orderdetails 테이블에 있으므로 join 필수

count() -> sum()
select buyPrice	, sum(quantityOrdered) as quantityOrdered
from
(select concat(round(buyPrice /10 ,0)*10,' - ',(round(buyPrice /10 ,0)+1 )*10 ) as buyPrice, a.productCode,quantityOrdered 
from products a join orderdetails b on a.productCode = b.productCode) t
group by buyPrice
order by 1

문제 8

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

정답

WITH WeeklySales AS (
    SELECT 
        YEAR(orderDate) AS year,
        WEEK(orderDate) AS week,
        SUM(quantityOrdered * priceEach) AS weekly_sales
    FROM orderdetails
    JOIN orders USING(orderNumber)
    JOIN products USING(productCode)
    WHERE productLine = 'Classic Cars'  -- 특정 제품군을 지정
    GROUP BY YEAR(orderDate), WEEK(orderDate)
),
WeeklyChange AS (
    SELECT 
        year, 
        week, 
        weekly_sales,
        LAG(weekly_sales) OVER (ORDER BY year, week) AS prev_week_sales
    FROM WeeklySales
)
SELECT 
    year,
    week,
    weekly_sales,
    ROUND(((weekly_sales - prev_week_sales) / prev_week_sales) * 100, 2) AS weekly_change_percentage
FROM WeeklyChange
WHERE prev_week_sales IS NOT NULL;

정답 수정

1)우선 productLine에 classicCars 이라는 조건을 설정하여 답을 구함
2)정답에는 week()을 디폴트로 설정해서 기존 week(orderDate, 1 ) -> week(orderDate)로 변경
3)rate_of_range가 null값일때 출력 안되도록 설정

WITH WeeklySales AS (
    SELECT 
        YEAR(orderDate) AS year,
        WEEK(orderDate) AS week,
        SUM(quantityOrdered * priceEach) AS weekly_sales
    FROM orderdetails
    JOIN orders USING(orderNumber)
    JOIN products USING(productCode)
    WHERE productLine = 'Classic Cars'  -- 특정 제품군을 지정
    GROUP BY YEAR(orderDate), WEEK(orderDate)
),
WeeklyChange AS (
    SELECT 
        year, 
        week, 
        weekly_sales,
        LAG(weekly_sales) OVER (ORDER BY year, week) AS prev_week_sales
    FROM WeeklySales
)
SELECT 
    year,
    week,
    weekly_sales,
    ROUND(((weekly_sales - prev_week_sales) / prev_week_sales) * 100, 2) AS weekly_change_percentage
FROM WeeklyChange
WHERE prev_week_sales IS NOT NULL;

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

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

정답 수정

  • percent_rank을 활용하지 말고, ntile분위수 구하는 코드를 바로 사용해서 구하기
  • 코드 간소화하고 싶다면? case when 활용하기
select * , round(10_per_revenue*100/total_revenue,2) as 'percentage(%)'
from
(
select sum(revenue) as total_revenue , sum(case when rank_revenue= 1 then revenue end) as 10_per_revenue
from
(
select productCode,sum(quantityOrdered* priceEach) as revenue, ntile(10) over (order by sum(quantityOrdered* priceEach) desc) as rank_revenue
from orderdetails
group by productCode
) t
) t2

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

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

정답 수정

  • percent_rank -> ntile
  • 고객 및 연도별 평균 주문 금액 : avg(매출) 로 계산
with cte_1 as (
select customerNumber 
from(
select b.customerNumber, 
round(avg(quantityOrdered * priceEach),2) as avg_revenue , 
ntile(4) over (order by round(avg(quantityOrdered * priceEach),2) desc) as ntile_revenue
from customers a 
inner join orders b on a.customerNumber = b.customerNumber
inner join orderdetails c on b.orderNumber = c.orderNumber
group by b.customerNumber) t
where ntile_revenue = 1)

-- 연도별 주문 횟수, 평균 주문 금액

select year(orderDate), a.customerNumber, count(b.orderNumber) as '주문횟수', round(avg(quantityOrdered * priceEach),2) as '평균주문금액'
from cte_1 a 
inner join orders b on a.customerNumber = b.customerNumber
inner join orderdetails c on b.orderNumber = c.orderNumber
group by year(orderDate),a.customerNumber
order by 1,2;

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

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

정답 수정

ntile 사용시 각 직원별로 고객 매출을 구하는거라서,
partition by employeenumber order by customernumber
을 적어줘야한다.

select employeeNumber ,total_revenue, 20_per_revenue, round(20_per_revenue/total_revenue,2) as 'percentage(%)'
from
(
select employeeNumber, sum(customer_revenue) as total_revenue , 
sum(case when ntile_revenue = 1 then customer_revenue end) as 20_per_revenue
from
(
select employeeNumber,b.customerNumber, sum(quantityOrdered * priceEach) as customer_revenue,ntile(5) over (partition by employeeNumber order by sum(quantityOrdered * priceEach) desc) as ntile_revenue
from employees a inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber
inner join orders c on b.customerNumber = c.customerNumber 
inner join orderdetails d on c.orderNumber = d.orderNumber
group by b.customerNumber
) t
group by employeeNumber
) t2
profile
Hyeon
이전 포스트

[SQL/Classicmodels Database] 심화문제 3,4(고객의 주문 상세 분석,복합 조건 고객 분류)

다음 포스트

Advented of SQL 2024 : 게임 평점 예측하기 1 (DAY 9)

0개의 댓글