[패캠] SQL: 실무 문제 풀이 (유저 세그먼트별 + 매출 분석)

Q. 전체 유저의 Demographic을 알고 싶어요. 성/연령별로 유저 수를 알려주세요. 어느 세그먼트가 가장 숫자가 많나요? 

-- 참고로 기타 성별은 하나로, 연령은 5세 단위로 적당히 묶어주시고, 유저 수가 높은 순서대로 보여주세요.

 

-- gender가 비어있는(null) 경우 Others에 하나로 묶어주기
SELECT CASE
        WHEN gender IS NULL THEN 'Others' 
    	ELSE gender
    END AS gender, COUNT(*)
FROM fastcampus.tbl_customer
GROUP BY 1;

-- IS NULL로 확인하는 위 방법은 잘 안 먹힘 (공공데이터 등 데이터가 더티할 때 종종 있는 일)
-- 해결: LENGTH 함수 활용

-- 'gender 길이가 1보다 작으면' others로 묶기
SELECT CASE
        WHEN LENGTH(gender) < 1 THEN 'others'
    	ELSE gender
    END AS gender, COUNT(*)
FROM fastcampus.tbl_customer
GROUP BY 1;

-- 연령 5세 단위로 묶기

-- 데이터에 있는 연령 범위 확인
SELECT DISTINCT age
FROM fastcampus.tbl_customer
ORDER BY age;

 

-- 연령 묶어서 조회
SELECT CASE
WHEN age <= 15 THEN '15세 이하'
    WHEN age <= 20 THEN '20세 이하'
    WHEN age <= 25 THEN '25세 이하'    
    WHEN age <= 30 THEN '30세 이하'
    WHEN age <= 35 THEN '35세 이하'
WHEN age <= 40 THEN '40세 이하'
WHEN age <= 45 THEN '40세 이하'
WHEN age <= 50 THEN '40세 이하'
WHEN age <= 55 THEN '55세 이하'
    WHEN age > 55 THEN '55세 초과'
END AS ages,
COUNT(customer_id) 
FROM fastcampus.tbl_customer
GROUP BY ages   
ORDER BY 1;

 

-- gender 내용과 연령 내용 합친 최종 코드

SELECT CASE
WHEN LENGTH(gender) < 1 THEN 'Others'
ELSE gender
    END AS gender
    , CASE
WHEN age <= 15 THEN '15세 이하'
WHEN age <= 20 THEN '20세 이하'
WHEN age <= 25 THEN '25세 이하'    
WHEN age <= 30 THEN '30세 이하'
WHEN age <= 35 THEN '35세 이하'
WHEN age <= 40 THEN '40세 이하'
WHEN age <= 45 THEN '45세 이하'
WHEN age <= 50 THEN '50세 이하'
WHEN age <= 55 THEN '55세 이하'
WHEN age > 55 THEN '55세 초과'
  END AS age
    , COUNT(*)
FROM fastcampus.tbl_customer
GROUP BY 1, 2
ORDER BY 3 DESC;

 

 

 

Q. 성/연령을 '남성(25-29세)' 형태로 통합하고, 각 성/연령이 전체 고객에서 얼마나 차지하는지 분포(%)를 알려주세요.

-- 분포가 높은 순서대로 알려주세요.

-- gender의 컬럼값들을 한글로 변경 ('남성(25-29세)' 형태에 맞추기 위해)
SELECT CASE
	WHEN LENGTH(gender) < 1 THEN '기타'
        WHEN gender = 'Others' THEN '기타'
        WHEN gender = 'F' THEN '여성'
        WHEN gender = 'M' THEN '남성'
    END AS gender;

 

-- CONCAT 함수로 gender와 age 컬럼명(문자열)을 합쳐서 새로운 컬럼명을 만듦
-- 주의: CONCAT으로 묶을 때 중간에 있는 Alias는 삭제
SELECT CONCAT(
      CASE
        WHEN LENGTH(gender) < 1 THEN '기타'
        WHEN gender = 'Others' THEN '기타'
        WHEN gender = 'F' THEN '여성'
        WHEN gender = 'M' THEN '남성'
      END
    , "("
    , CASE
        WHEN age <= 15 THEN '15세 이하'
        WHEN age <= 20 THEN '20세 이하'
        WHEN age <= 25 THEN '25세 이하'    
        WHEN age <= 30 THEN '30세 이하'
        WHEN age <= 35 THEN '35세 이하'
        WHEN age <= 40 THEN '40세 이하'
        WHEN age <= 45 THEN '45세 이하'
        WHEN age <= 50 THEN '50세 이하'
        WHEN age <= 55 THEN '55세 이하'
        WHEN age > 55 THEN '55세 초과'
      END
      , ")"
) AS segment
    , COUNT(*)
FROM fastcampus.tbl_customer
GROUP BY 1;

-- 각 성/연령이 전체 고객에서 얼마나 차지하는지 분포(%) 알아보기

-- 전체 유저 수 구하기
SELECT COUNT(*)
FROM fastcampus.tbl_customer;

 

-- 각 세그먼트를 전체 유저 수로 나눠주는 과정을 일일이 나눠주지 않고 자동화하려면?
-- SELECT절에 전체 유저 수 구하는 쿼리를 삽입 (스칼라 서브쿼리)

-- CONCAT 함수로 gender와 age 컬럼명(문자열)을 합쳐서 새로운 컬럼명을 만듦
-- 주의: CONCAT으로 묶을 때 중간에 있는 Alias는 삭제
SELECT CONCAT(
      CASE
        WHEN LENGTH(gender) < 1 THEN '기타'
        WHEN gender = 'Others' THEN '기타'
        WHEN gender = 'F' THEN '여성'
        WHEN gender = 'M' THEN '남성'
      END
    , "("
    , CASE
        WHEN age <= 15 THEN '15세 이하'
        WHEN age <= 20 THEN '20세 이하'
        WHEN age <= 25 THEN '25세 이하'    
        WHEN age <= 30 THEN '30세 이하'
        WHEN age <= 35 THEN '35세 이하'
        WHEN age <= 40 THEN '40세 이하'
        WHEN age <= 45 THEN '45세 이하'
        WHEN age <= 50 THEN '50세 이하'
        WHEN age <= 55 THEN '55세 이하'
        WHEN age > 55 THEN '55세 초과'
      END
      , ")"
) AS segment
    , ROUND(COUNT(*) / (SELECT COUNT(*) FROM fastcampus.tbl_customer) * 100, 2)
FROM fastcampus.tbl_customer
GROUP BY 1;

 

 

Q. 2020년 7월, 성별에 따라 총 구매 건수와 총 Revenue를 구해주세요. 이전처럼 남여 이외의 성별은 하나로 묶어주세요.

-- 구매 테이블과 고객 테이블을 조인
-- 조인할 때 각 테이블에 별칭 꼭 붙여줘야 함 → 안 붙여주면 모든 조합을 다 구해서(카테시안 곱) 엄청난 부하 발생

-- 내가 작성한 쿼리
-- INNER JOIN은 (교집합 컬럼을 중심으로) 두 테이블의 모든 컬럼을 가져옴
SELECT * 
FROM fastcampus.tbl_purchase P
INNER JOIN fastcampus.tbl_customer C
ON P.customer_id = C.customer_id
LIMIT 10;

 

-- A 테이블 전체(*)에 B 테이블에서 필요한 컬럼만 추가로 가져오기
-- 구매 테이블에서 null인 것도 가져오기 위해 LEFT JOIN 사용
SELECT P.*
, C.gender -- 고객 테이블에서 gender 컬럼만 가져옴
FROM fastcampus.tbl_purchase P
LEFT JOIN fastcampus.tbl_customer C
ON P.customer_id = C.customer_id;

 

 

최종 코드

SELECT CASE WHEN C.gender = 'F' THEN '여성'
WHEN C.gender = 'M' THEN '남성'
WHEN C.gender = 'Others' THEN '기타'
WHEN LENGTH(C.gender) < 1 THEN '기타' 
END AS 성별
        , COUNT(*) AS 구매건수
        , SUM(P.price) AS 수익
FROM fastcampus.tbl_purchase P
LEFT JOIN fastcampus.tbl_customer C
ON P.customer_id = C.customer_id
GROUP BY 1;

 

 

Q. 2020년 7월의 성별 연령대에 따라 구매 건수와 총 Revenue를 구해주세요

SELECT CASE WHEN C.gender = 'F' THEN '여성'
WHEN C.gender = 'M' THEN '남성'
WHEN C.gender = 'Others' THEN '기타'
            WHEN LENGTH(C.gender) < 1 THEN '기타'
   END AS 성별,
       CASE WHEN age <= 15 THEN '15세 이하'
WHEN age <= 20 THEN '20세 이하'
            WHEN age <= 25 THEN '25세 이하'
            WHEN age <= 30 THEN '30세 이하'
            WHEN age <= 35 THEN '35세 이하'
            WHEN age <= 40 THEN '40세 이하'
            WHEN age <= 45 THEN '45세 이하'
            WHEN age >= 46 THEN '46세 이상'
END AS 연령대,
       COUNT(*) AS 구매건수,
       SUM(P.price) AS 수익
FROM fastcampus.tbl_purchase P
LEFT JOIN fastcampus.tbl_customer C
ON P.customer_id = C.customer_id
WHERE P.purchased_at >= '2020-07-01'
AND P.purchased_at < '2020-08-01'
GROUP BY 1, 2
ORDER BY 3 DESC;

 

 

매출 관련 추가 분석

Q. 2020년 7월 일별 매출의 전일 대비 증감폭, 증감률을 구해주세요.

-- 일별 매출 구하기
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, '%Y-%m-%d') AS date_at
, SUM(price) AS sales
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY 1;

 

 

WITH문

똑같은 서브쿼리를 여러 군데에 사용해야 할 때 임시 테이블로 미리 정의해두는 구문

WITH 새로운 테이블명 AS (   
       저장할 서브쿼리
)

 

WITH 문으로 일별 revenue 구하는 쿼리 저장해두기

WITH tbl_revenue AS (
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, '%Y-%m-%d') AS date_at
    , SUM(price) AS revenue
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY 1
)

WITH문으로 정의한 테이블 사용

SELECT *
  , LAG(revenue) OVER(ORDER BY date_at ASC)
FROM tbl_revenue;

-- 전일 대비 증감폭과 증감률 구하기
-- 증감폭: 오늘 - 이전 날, 증감률은 비율 
-- 전일의 revenue를 가져오는 함수인 LAG 함수 사용 (윈도우 함수 중 하나)

LAG: '뒤처지다' 라는 뜻 (특정 날짜 기점으로 '뒤로 가서' 지정한 컬럼명의 값을 가져온다)

SELECT LAG(컬럼명) OVER(ORDER BY 날짜 ASC)

 

 

증감폭과 증감률은 다르다

예를 들어 어제 100만 원, 오늘 110만 원 수익이 났다면
증감폭: 어제 대비 '얼마다 더 벌었는지'의 값 (110 - 100 = 10)
증감률: 어제 대비 더 번 것의 비율 (110 - 100(= 어제보다 더 번 것) / 100(= 어제 번 것) = 0.1(10%))

SELECT *
    , revenue - LAG(revenue) OVER(ORDER BY date_at ASC) AS diff_revenue -- 증감폭
    , ROUND((revenue - LAG(revenue) OVER(ORDER BY date_at ASC)) / LAG(revenue) OVER(ORDER BY date_at ASC) * 100, 2) AS diff_percent -- 증감율
FROM tbl_revenue;

 

결과: 7월 1일에는 갖고 올 것이 없어서 null, 그 외에는 전날 revenue를 잘 불러옴

 

 

Q. 7월에 일별 구매금액 기준으로 가장 많이 지출한 Top 3 고객을 뽑아주세요.

ORDER BY + LIMIT 사용한 방법

SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, '%Y-%m-%d') AS date_at
    , SUM(price) AS revenue
    , customer_id
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY 1, 3
ORDER BY 2
LIMIT 3;

-- ORDER BY + LIMIT 방법은 매일 하루마다 총 31번 쿼리를 돌려야 함
-- 자동화하기 위해 순위 함수 사용

 

날짜별로(PARTITION) 매출 TOP3 고객 조회

 

    -- GROUP BY와 달리 PARTITION BY는 파티션할 것만 지정 (GROUP BY는 SELECT 절에 집계함수 빼고 모두 지정 필수)
    -- 수익이 일치하는 경우가 있을 때를 대비하여, RANK 대신 동일 값이 있더라도 다음 순위 값을 건너뛰지 않는 DENSE_RANK 사용

SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, '%Y-%m-%d') AS date_at
    , customer_id
    , SUM(price) AS revenue
    , DENSE_RANK() OVER (PARTITION BY DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, '%Y-%m-%d') ORDER BY SUM(price) DESC)
FROM fastcampus.tbl_purchase
WHERE purchased_at >= '2020-07-01'
AND purchased_at < '2020-08-01'
GROUP BY 1, 2;

결과: 일별 매출 TOP3 고객 확인 (7월 1일의 1,2,3위 / 7월 2일의 1,2,3위 등)

 

Q. 각 일자별 TOP 3들만 보고 싶어요.

 

-- 방법: 위 쿼리를 서브쿼리로 넣고 '이 결과값 중 순위(rank_dev)가 4 미만인 경우만 조회'라는 조건 걸기

SELECT *
FROM (
    SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, '%Y-%m-%d') AS date_at
        , customer_id
        , SUM(price) AS revenue
        , DENSE_RANK() OVER (PARTITION BY DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, '%Y-%m-%d') ORDER BY SUM(price) DESC) AS rank_dev
    FROM fastcampus.tbl_purchase
    WHERE purchased_at >= '2020-07-01'
    AND purchased_at < '2020-08-01'
    GROUP BY 1, 2) AS foo
WHERE rank_dev < 4;

 

 

결과:

일자별로 1,2,3위 고객과 매출 출력

(직접 workbench에서 작성한 화면에서 캡쳐)