데이터 테이블 설명

dim_years

• 2000년부터 2030년까지의 연도 데이터

컬럼 이름	데이터 타입	설명
year	int	연도(pk)

sales_order

컬럼 이름	데이터 타입	설명
salesorderid	int	주문 ID PK
unitprice	decimal(19,4)	제품 단가
orderqty	smallint	주문 수량
customerid	int	고객 ID(FK, 고객 테이블과 연결)
orderdate	timestamp	주문 날짜

customer

컬럼 이름	데이터 타입	설명
customerid	int	고객 IDPK
firstname	VARCHAR(100)	고객 이름 (성)
lastname	VARCHAR(100)	고객 이름 (이름)

문제 1 - 첫 주문 고객 연도별 매출 조회

“2011년 12월”에 첫 주문을 한 고객들의 연도별 매출을 조회하는 SQL 문을 작성하세요.
고객 이름은 이름과 성을 결합하여 생성합니다.
결과는 고객 ID(customerid)기준 오름차순, 연도(year) 기준 오름차순으로 정렬합니다.

출력 결과

• 고객 ID customerid
• 고객 이름 customer_name
  • 고객명 customer_name = 이름 firstname+ ' ' + 성 lastname
• 연도 year
• 총 거래액 gmv
  • GMV = 제품 단가 unitPrice * 주문 수량 orderQty

출력값 예시

+------------+------------------+--------+----------------+
| customerid | customer_name    | year   | gmv            |
+------------+------------------+--------+----------------+
| 1          | John Doe         | 2011   | 2024.12        | 
| 1          | John Doe         | 2012   | 444.46         | 
| 2          | Jane Smith       | 2011   | 6036.66        |
+------------+------------------+--------+----------------+

풀이

1. 첫 주문이 2011년 12월인 고객 아이디를 찾는다.
2. 해당 고객들만 필터링
3. 고객 아이디와 연도별로 그룹화하여 연도별 매출 계산

• 첫 주문이 2011년 12월인 고객 아이디 찾기

SELECT customerid
FROM sales_order
GROUP BY customerid 
HAVING SUBSTR(MIN(orderdate), 1, 7) = '2011-12'

• WHERE로 필터링하고 그룹화하여 연도별 매출 계산하기

SELECT so.customerid,
	CONCAT(c.firstname, ' ', c.lastname) AS customer_name,
	SUBSTR(so.orderdate, 1, 4) AS year,
	SUM(so.unitprice * so.orderqty) AS gmv
FROM sales_order so JOIN customer c ON so.customerid = c.customerid 
WHERE so.customerid in (SELECT customerid
						FROM sales_order
						GROUP BY customerid 
						HAVING SUBSTR(MIN(orderdate), 1, 7) = '2011-12')		
GROUP BY so.customerid, year		
ORDER BY so.customerid, year;

문제 2 - 고객별 연평균 총 거래액 집계

2011년 12월에 첫 주문을 한 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로, 해당 기간 동안의 연평균 총거래액(GMV)을 계산합니다. 소수점 2째자리까지 계산해주세요. 결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

• 총거래액, GMV(Gross Merchandise Value)는 1번 문제에서 집계한 값을 사용합니다.
• 특정 연도에 주문을 하지 않았다면, 그 연도의 GMV와 주문 수는 0으로 처리하고 계산합니다. 예:
  • 고객 A: 첫주문 2011년 ($100), 2012년 ($250), 마지막 주문 2013년 ($400)
    • 연평균 GMV = (100 + 250 + 400) / 3 = $250.00
  • 고객 B: 첫주문 2011년 ($100), 마지막 주문 2013년 ($200)
    • 연평균 GMV = (100 + 0 + 200) / 3 = $100.00 (2012년에는 주문이 없으므로 그 해의 총거래액(GMV)은 0으로 처리하여 평균 계산)
      
  • 고객 C: 첫주문 & 마지막 주문 2011년 ($100)
    • 연평균 GMV = 100 / 1 = $100.00

출력 결과

• 고객 ID (customerid)
• 고객 이름 customer_name
  • 고객명 customer_name = 이름 firstname+ ' ' + 성 lastname
    
• 연평균 매출 (avg_yearly_gmv), 소수점 2째자리

출력값 예시

+------------+------------------+----------------+
| customerid | customer_name    | avg_yearly_gmv |
+------------+------------------+----------------+
| 11387      | Megan Ramirez    | 2319.6         | 
| 11388      | Joseph Martin    | 2328.92        | 
| 11394      | George McDonald  | 2040.49        |
| 11398      | Colin Nath       | 2028.51        | 
| 11400      | Franklin Raji    | 2322.59        | 
...
+------------+------------------+----------------+

풀이

1. 1번에서 만든 테이블을 with cte as를 활용하여 이용하기
2. 고객 아이디별로 그룹화하여 매출 합계 구하기
3. max와 min을 이용하여 첫 구매연도와 마지막 구매연도의 차를 구해 평균 구하기

WITH cte AS (SELECT so.customerid,
				CONCAT(c.firstname, ' ', c.lastname) AS customer_name,
				SUBSTR(so.orderdate, 1, 4) AS year,
				SUM(so.unitprice * so.orderqty) AS gmv
			FROM sales_order so JOIN customer c ON so.customerid = c.customerid 
			WHERE so.customerid in (SELECT customerid
									FROM sales_order
									GROUP BY customerid 
									HAVING SUBSTR(MIN(orderdate), 1, 7) = '2011-12')		
			GROUP BY so.customerid, year		
			ORDER BY so.customerid, year)	
SELECT customerid, customer_name,
	ROUND(SUM(gmv) / (MAX(year) - MIN(year) + 1), 2) AS avg_yearly_gmv
FROM cte
GROUP BY customerid
ORDER BY customerid;

문제 3 - 고객별 최대 주문 수량 연도와 주문 수량 집계

2011년 12월에 첫 주문을 한 고객 대상으로, 고객별 각 연도에서의 주문 수량을 집계하고, 최대 주문 수량과 그 최대 주문 수량을 발생시킨 연도를 찾습니다. 최대 주문 수량이 같은 연도가 있다면, 가장 최근 연도를 출력합니다. 결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

• 만약 최대 주문 수량이 여러 연도에서 발생하면, 더 최근의 연도를 선택합니다. 예:
  • 고객 A: 2011년 (주문 5개), 2012년 (주문 10개), 2013년 (주문 11개)
    • 최대 주문 수량 : 11개
    • 최대 주문 수량이 발생한 연도 : 2013년
  • 고객 B: 2011년 (주문 10개), 2012년 (주문 5개), 2013년 (주문 10개)
    • 최대 주문 수량 : 10개
    • 최대 주문 수량이 발생한 연도: 2013년 (2011년과 주문 수량이 같지만 더 최근의 연도)

출력 결과

• 고객 ID (customerid)
• 고객 이름 customer_name
  • 고객명 customer_name = 이름 firstname+ ' ' + 성 lastname
• 최대 주문 수량 발생 연도 (max_qty_year)
• 최대 주문 수량 (max_total_qty)

출력값 예시

+------------+------------------+----------------+-----------------+
| customerid | customer_name    | max_qty_year   | max_total_qty   |
+------------+------------------+----------------+-----------------+
| 11387      | Megan Ramirez    | 2013           | 5               |
| 11388      | Joseph Martin    | 2013           | 6               |
| 11394      | George McDonald  | 2013           | 4               |
| 11398      | Colin Nath       | 2014           | 2               |
| 11400      | Franklin Raji    | 2013           | 7               |
...
+------------+------------------+----------------+-----------------+

풀이

1. 1, 2번 문제와 같은 방식으로 고객의 연도별 구매 수량 구하기
2. window function RANK를 이용하여 구매 수량이 가장 많은 연도와 구매 수량 구하기⭐️
3. with cte as를 활용하여 위 테이블 이용하기
4. 고객별 RANK가 1인 행만 출력하기

WITH cte AS (
    SELECT so.customerid,
           CONCAT(c.firstname, ' ', c.lastname) AS customer_name,
           SUBSTR(so.orderdate, 1, 4) AS year,
           SUM(orderqty) AS qty
    FROM sales_order so JOIN customer c ON so.customerid = c.customerid
    WHERE so.customerid IN (
        SELECT customerid
        FROM sales_order
        GROUP BY customerid
        HAVING SUBSTR(MIN(orderdate), 1, 7) = '2011-12'
    )
    GROUP BY so.customerid, year
),  
ranked_cte AS (
    SELECT customerid,
           customer_name,
           year,
           qty,
           RANK() OVER (PARTITION BY customerid ORDER BY qty DESC, year ASC) AS rnk
    FROM cte
)
SELECT customerid,
       customer_name,
       year AS max_qty_year,
       qty AS max_total_qty
FROM ranked_cte
WHERE rnk = 1
ORDER BY customerid;