[사전캠프 11일차] 아티클 스터디 + SQL 달리기반 4번 문제 풀이

사전캠프 11일차

아티클 스터디와 SQL 달리기반 문제 풀이를 진행했다.

 

아티클 스터디

오늘은 마케터에게 데이터 분석 능력이 꼭 필요한지, 어떻게 해당 역량을 기를 수 있는지에 대한 아티클이다.

 

https://brunch.co.kr/@shimseenote/5

마케터에게 데이터분석이란? 꼭 필요할까?

 

• 내가 정리한 내용

[주제]

마케터에게 데이터분석이란

 

[아티클 요약]

• 데이터 분석 능력이 왜 필요할까?
  • 데이터를 분석해서 나온 인사이트를 바탕으로 더 나은 의사결정을 하기 위해 >> 지표에 근거해서 A안을 선택
  • 브랜딩 콘텐츠를 제작하는 콘텐츠 마케터 vs 앱서비스의 질적 성장을 만드는 그로스 마케터 >> 필요로하는 데이터분석 능력의 깊이가 다름
• 실제로 어떻게 사용될까? 그로스 마케터의 경우 고객 행동 데이터를 바탕으로 서비스 개선점 찾고, 가설-검증 과정 거침
  • 문제 상황: 신규 유입 고객은 늘었지만, 실제 서비스 결제 고객은 0.5%뿐일 때
  • 문제 정의: 고객 이탈 원인을 찾자
  • 원인 분석: 로그인 단계에서 전환율이 떨어짐
    • 회원가입 방식이 너무 복잡하기 때문
  • 해결 방안: 회원가입 방식에 카카오톡, 네이버 ‘간편 로그인’ 방식 추가
  • 결과: 결제 방식 선택과 결제 완료 비율이 모두 상승
• 어떻게 준비해야할까?
  • 데이터 통해 가설 세우고 검증하는 ‘기획력’에 가까움
  • 실생활에서, 프로젝트에서 가설-검증의 방식을 접목
    • 실무 경험이 없어서 고민일 때 이렇게 구조화

[인사이트]

• 데이터 분석가 직무기술서에 ‘가설-검증 과정을 통해 통계적 유의성을 구별할 줄 아는 능력’이 종종 보이던데, 나도 실생활에서부터 가설을 세우고 검증하는 논리적인 사고 흐름을 익혀서 추후 프로젝트에 자연스럽게 적용하고 싶다는 생각을 했다.

 

SQL 달리기반 4번 문제 풀이

Orders 테이블:

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2024-01-01	150
102	2	2024-01-03	200
103	3	2024-01-04	300
104	4	2024-01-04	50
105	5	2024-01-05	80
106	6	2024-01-06	400

 

Customers 테이블:

CustomerID	CustomerID	Country
1	Alice	USA
2	Bob	UK
3	Charlie	USA
4	David	Canada

 

• 요구사항:

1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.
   • 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.
   • 기대결과

CustomerName	OrderCount	TotalSpent
Alice	2	450
Bob	2	280
Charlie	1	50
David	1	400

 

2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

• 기대결과

Country	Top_Customer	Top_Spent
USA	Alice	450
UK	Bob	280
Canada	David	400

 

• 제약사항:
  • 두 쿼리 모두 서브쿼리, JOIN, GROUP BY, HAVING 등을 사용해 풀 수 있어야 한다.
  • 주문을 한 적이 없는 고객도 첫 번째 쿼리 결과에 포함되어야 한다.

 

문제 접근

1.  주문한 적 없는 고객도 포함시키려면 두 테이블 customername 기준으로 left join

   - customerid로 groupby

   - 주문 건수는 count(orderid), 총 주문금액은 sum(totalamount)

   - 주문이 없는 고객의 경우 총 주문 금액을 0으로 처리 ifnull

   - 기대 결과의 칼럼명에 따라 as 써주기

 

2. 똑같이 left join 이후 이번엔 country로 groupby

   - 총 주문 금액이 최대인 사람의 이름을 추출하려면 select절에는 c.customername as top_customer을 쓰고 sum(o.totalamount) as top_spent도 써주기

   - 각 나라별 총 주문금액 최대값을 가진 고객을 필터링하기 위한 having절

      >> 서브쿼리 내에서 left join 한번 더 수행 후, where절을 써줘야 함, 근데 잘 모르겠음.. 그래서 해설 참고

  

최종 코드

 

1. 

SELECT c.CustomerName,
       COUNT(o.orderid) AS OrderCount,
       IFNULL(SUM(o.totalamount), 0) AS TotalSpent
FROM customers c
     LEFT JOIN orders o ON c.customerid = o.customerid
GROUP BY c.customername

 

2.

SELECT c.Country,
       c.customername AS Top_Customer,
       SUM(o.totalamount) AS Top_Spent
FROM customers c
     LEFT JOIN orders o ON c.customerid = o.customerid
GROUP BY c.country
HAVING -- 여기까지 내가 완성

 

밑에는 해설 참고해서 최종 완성한 코드

 

SELECT 
    c.Country,
    c.CustomerName AS Top_Customer,
    SUM(o.TotalAmount) AS Top_Spent
FROM Customers c
     LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Country, c.CustomerName
HAVING SUM(o.TotalAmount) = (

        SELECT MAX(SumSpent)
        FROM (
             SELECT SUM(o2.TotalAmount) AS SumSpent
             FROM Customers c2
                  JOIN Orders o2 ON c2.CustomerID = o2.CustomerID
             WHERE c2.Country = c.Country
             GROUP BY c2.CustomerID) AS Subquery
                                                 )

 

↑ having절에서 서브쿼리를 2번 중첩해서 썼음, 해석하는 것도 어렵다..