metrics : 연도와 월별 성과 지표 및 직원별 성과 분석 SQL (BDA학회 SQL 문법 기초 연습반)

 

연도와 월별 성과 지표를 계산하고, 직원별 성과 데이터를 분석할 것이다.

---

📊 1. 연도와 월별 성과 지표 계산 (월간 집계)

먼저, 연도와 월별로 비즈니스 성과를 집계하는 쿼리를 작성한다. 이를 위해 CTE(Common Table Expression)를 활용하여 데이터를 효율적으로 계산 및 구성한다.

쿼리 설명

1. MonthlyMetrics CTE:
   • 주요 계산 항목:
     • 연도, 월
     • 총 주문 금액, 주문 수, 고유 고객 수, 총 상품 수, 고유 상품 수, 총 원가
   • 이를 통해 연도와 월별로 중요한 성과 지표를 요약한다.
2. MonthlyMetricsWithLag CTE:
   • 이전 월의 총 주문 금액(LAG 함수 사용)을 추가로 계산한다.
   • 이를 기반으로 월간 성장률(전월비)을 계산한다.
3. 최종 결과:
   • 연도(order_year), 월(order_month)을 기준으로 성과 지표를 확인할 수 있다.
   • 월별 성장률(month_over_month_change)로 월간 성과 변화를 쉽게 확인할 수 있다.

주요 코드

WITH MonthlyMetircs AS (
    SELECT 
        YEAR(o.orderdate) AS order_year, 
        MONTH(o.orderdate) AS order_month, 
        SUM(od.priceEach * od.quantityOrdered) AS total_order_amount, 
        COUNT(DISTINCT o.orderNumber) AS total_orders, 
        COUNT(DISTINCT o.customerNumber) AS unique_customers, 
        COUNT(od.productCode) AS prd_total, 
        COUNT(DISTINCT od.productCode) AS prd_dist_total, 
        SUM(od.quantityOrdered * p.buyPrice) AS total_cost 
    FROM orders AS o 
    JOIN orderdetails AS od 
        ON o.orderNumber = od.orderNumber 
    JOIN products AS p 
        ON p.productCode = od.productCode 
    GROUP BY YEAR(o.orderdate), MONTH(o.orderdate)
),
MonthlyMetricsWithLag AS (
    SELECT 
        *, 
        LAG(total_order_amount) OVER (ORDER BY order_year, order_month) AS prev_month_order_amount
    FROM MonthlyMetircs
)
SELECT 
    order_year, 
    order_month, 
    total_order_amount, 
    total_orders, 
    unique_customers, 
    prd_total, 
    prd_dist_total, 
    total_cost, 
    CASE
        WHEN prev_month_order_amount IS NULL THEN NULL
        ELSE (total_order_amount - prev_month_order_amount) / prev_month_order_amount
    END AS month_over_month_change
FROM MonthlyMetricsWithLag
ORDER BY 1, 2;

---

🧑‍💼 2. 직원별 성과 지표 계산

다음으로, 직원별로 비즈니스 성과를 분석하는 SQL 쿼리를 작성한다. 이를 통해 직원들의 기여도를 파악하고, 비즈니스 의사결정에 활용할 수 있다.

쿼리 설명

1. EmployeeSales CTE:
   • 주요 계산 항목:
     • 직원 번호, 이름, 직책
     • 총 매출액, 처리한 주문 수, 고유 고객 수
   • 직원별 데이터를 그룹화하여 효율적으로 집계한다.
2. 최종 결과:
   • 직원의 총 매출액(total_sales)을 기준으로 내림차순 정렬하여 성과를 확인한다.
   • 주문당 평균 매출(average_sales_per_order), 고객당 평균 매출(average_sales_per_customer)을 추가로 계산한다.

주요 코드

WITH EmployeeSales AS (
    SELECT 
        e.employeeNumber, 
        e.firstName, 
        e.lastName, 
        e.jobTitle, 
        SUM(od.quantityOrdered * od.priceEach) AS total_sales, 
        COUNT(DISTINCT o.orderNumber) AS total_orders, 
        COUNT(DISTINCT c.customerNumber) AS unique_customers
    FROM employees AS e 
    JOIN customers AS c 
        ON e.employeeNumber = c.salesRepEmployeeNumber 
    JOIN orders AS o 
        ON c.customerNumber = o.customerNumber 
    JOIN orderdetails AS od 
        ON o.orderNumber = od.orderNumber 
    GROUP BY e.employeeNumber, e.firstName, e.lastName, e.jobTitle
)
SELECT 
    employeeNumber, 
    firstName, 
    lastName, 
    jobTitle, 
    total_sales, 
    total_orders, 
    unique_customers, 
    CASE 
        WHEN total_orders = 0 THEN NULL 
        ELSE total_sales / total_orders 
    END AS average_sales_per_order, 
    CASE 
        WHEN unique_customers = 0 THEN NULL 
        ELSE total_sales / unique_customers 
    END AS average_sales_per_customer
FROM EmployeeSales
ORDER BY total_sales DESC;

---

🔍 마무리

위 두 쿼리는 비즈니스 데이터를 효과적으로 분석하고 중요한 지표를 도출하는 데 활용된다. 월간 성과 분석을 통해 시간에 따른 변화와 추세를 확인할 수 있으며, 직원별 성과 분석으로 내부 기여도를 파악할 수 있다.