<aside> 👉 셋팅 안내

</aside>

[QCC] 환경 셋팅 가이드(3회차)

<aside> 👉 제출 안내

</aside>

• 제출 링크 : https://forms.gle/oPFDZR5fctXtahxR6
• 진행 시간 : 1시간
• 해설 : 2024. 11. 22. (금) 16:00 ~ 17:00

https://notion-widget-kit.vercel.app/timer?목표일=20241122&목표시간=1200&글꼴번호=2&크기=20&굵기=3&머리말=✅제출까지&꼬리말=남음🔥&외부간격=20&내부간격=10&배경색=White&글자색=Black

<aside> ✅

선행 커맨드: use qcc;

</aside>

<aside> ◾

테이블 설명

dim_years

sales_order

customer ****

• dim_years 은 2000년부터 2030년까지의 연도 데이터를 포함하고 있습니다.
• 구매 내역은 sales_order 테이블에 저장되어 있으며, 고객의 개인 정보는 customer 테이블에 저장되어 있습니다.
• 두 테이블은 customerid로 연결됩니다. </aside>

<aside> ◾

문제

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

분석해야 할 내용은 다음과 같습니다 :

1. 첫 주문 날짜가 2011년 12월인 고객만 필터링합니다.
2. 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로, 그 기간 동안의 **연평균 총거래액(GMV)**을 계산합니다. 소수점 2째자리까지 계산해주세요.

   • 총거래액, GMV(Gross Merchandise Value)는 다음과 같이 구합니다:

     <aside> 💸

     GMV = 제품 단가 (unitPrice) × 주문 수량 (orderQty)

     </aside>

   • 특정 연도에 주문을 하지 않았다면, 그 연도의 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
3. 고객별 각 연도에서의 주문 수를 집계하고, 최대값과 그 최대값을 발생시킨 연도를 찾습니다.
   • 만약 최대 주문 수가 여러 연도에서 발생하면, 더 최근의 연도를 선택합니다. 예:
     • 고객 A: 2011년 (주문 5개), 2012년 (주문 10개), 2013년 (주문 11개)
       • 최대 주문수: 11개
       • 최대 주문수가 발생한 연도: 2013년
     • 고객 B: 2011년 (주문 10개), 2012년 (주문 5개), 2013년 (주문 10개)
       • 최대 주문수: 10개
       • 최대 주문수가 발생한 연도: 2013년 (2011년과 같은 주문 수지만 더 최근의 연도)
4. 결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

출력 결과

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

• 고객 ID (customerid)

• 고객 이름 (customer_name)

  • 고객 이름은 firstName과 lastName을 결합하여 customer_name을 생성합니다.

    <aside> 👤

    고객명 (customer_name) = 고객이름 (firstName) + " " + 고객성 (lastName)

    </aside>

• 연평균 매출 (avg_yearly_gmv), 소수점 2째자리

• 최대 주문 수 발생 연도 (max_order_year)

• 최대 주문 수 (max_order_count)

출력 값 예시

+------------+------------------+----------------+----------------+-----------------+
| customerid | customer_name    | avg_yearly_gmv | max_order_year | max_order_count |
+------------+------------------+----------------+----------------+-----------------+
| 11387      | Megan Ramirez    | 2319.6         | 2013           | 5               |
| 11388      | Joseph Martin    | 2328.92        | 2013           | 6               |
| 11394      | George McDonald  | 2040.49        | 2013           | 4               |
| 11398      | Colin Nath       | 2028.51        | 2014           | 2               |
| 11400      | Franklin Raji    | 2322.59        | 2013           | 7               |
...
+------------+------------------+----------------+----------------+-----------------+

</aside>

<aside> 👉 제출

</aside>

• 제출 링크 : https://forms.gle/oPFDZR5fctXtahxR6