[QCC 3회차]

[4기] QCC - 3회차

[4기] QCC - 3회차 | Notion

[ 문제 ]

당신은 이커머스 회사 ’루팡(주)’의 고객 코호트 분석팀에 속해 있습니다. 이번 분석의 목표는 2011년 12월에 첫 주문을 한 고객에 대해, 여러 연도별 매출 관련 지표를 집계하는 것입니다.

 

1. 첫 주문 날짜가 2011년 12월인 고객만 필터링

2. 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로, 그 기간 동안의 연평균 총거래액(GMV)을 계산합니다. 소수점 2째자리까지 계산해주세요.

3. 고객별 각 연도에서의 주문 수를 집계하고, 최대값과 그 최대값을 발생시킨 연도를 찾습니다.

4. 결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

 

 

• 총거래액, GMV(Gross Merchandise Value)는 다음과 같이 구합니다:GMV = 제품 단가 (unitPrice) × 주문 수량 (orderQty)
• 특정 연도에 주문을 하지 않았다면, 그 연도의 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

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

출력 결과

결과에는 다음과 같은 데이터가 포함되어야 합니다:

• 고객 ID (customerid)
• 고객 이름 (customer_name)
  • 고객 이름은 firstName과 lastName을 결합하여 customer_name을 생성합니다.고객명 (customer_name) = 고객이름 (firstName) + " " + 고객성 (lastName)
• 연평균 매출 (avg_yearly_gmv), 소수점 2째자리
• 최대 주문 수 발생 연도 (max_order_year)
• 최대 주문 수 (max_order_count)

[ SQL 작성 ]

1. 첫 주문 날짜 2011-12인 고객id, 고객이름 (성, 이름 concat), customer, sales_order 테이블4기] QCC - 3회차

 

 

 

 

[ SQL 작성 ]

 

1. 첫 주문 날짜 2011-12인 고객id, 고객이름 (성, 이름 concat), customer, sales_order 테이블 join (left 기본) 

 

 

1

 

[ 오답 ]

select customerid,

customer_name,

avg(GMV) avg_yearly_gmv,

max(year) max_order_year,

max(cnt_order) max_order_count

from

(

select c.customerid customerid,

concat (c.firstname,' ', c.lastname) customer_name,

min(orderdate) as first_order_date,

max(orderdate) as last_order_date,

year(orderdate) as year,

round((unitprice * orderqty)/3, 2) GMV, # 연도별 GMA 구하는 법 모르겠음

count(orderqty) cnt_order

FROM sales_order s join customer c on s.customerid = c.customerid

group by 1

having first_order_date like ('2011-12%')

) a

group by 1

order by 1

 

 

 

 

2

 

 

2-1

 

 

with 로 합쳐 GMV

 

 

row_number (중복없는  순위)