2024-10-09

Suhyeon Lee·2024년 10월 9일

CodeKata

SQL

45. 3월에 태어난 여성 회원 목록 출력하기

작성 코드

SELECT
  member_id
  , member_name
  , gender
  , DATE_FORMAT(date_of_birth, '%Y-%m-%d') AS date_of_birth
FROM
  member_profile
WHERE
  tlno IS NOT NULL 
  AND DATE_FORMAT(date_of_birth, '%m') = '03'
  AND gender = 'w'
ORDER BY
  member_id
;

WHERE 조건절에서 생일이 3월인 사람을 찾는 부분을 MONTH(date_of_birth) = 3으로 처리해도 됨

Python

5. 숫자 비교하기

프로그래머스 코딩테스트 연습

작성 코드

def solution(num1, num2):
    answer = 0
    if num1 == num2:
        answer = 1
    else: 
        answer = -1
    return answer

참고하면 좋을 코드

def solution(num1, num2):
    return 1 if num1==num2 else -1

def solution(num1, num2):
    return sum([num1==num2])*2-1
    
def solution(num1, num2):
    return ((num1 == num2)-0.5)*2

파이썬 특성상 True는 숫자 1, False는 숫자 0처럼 처리돼서 True/False도 정수처럼 계산이 가능

팀 과제

데이터 전처리 및 조건 설정

price의 값이 음수인 경우가 있어서 음수, 0은 무시하고 데이터 분석
주의 기준은 국제표준화기구(ISO)의 규정에 따라 월요일을 한 주의 시작, 일요일을 한 주의 끝으로 설정
- 2월 첫째 주: 2020/02/03 - 2020/02/09
- 2월 둘째 주: 2020/02/10 - 2020/02/16
- 2월 셋째 주: 2020/02/17 - 2020/02/23
- 2월 넷째 주: 2020/02/24 - 2020/02/29
- 기타: 2020/02/01, 2020/02/02
금액은 소수점 첫째 자리에서 반올림

날짜별 이벤트 발생 횟수와 총 판매 금액

SELECT
  pt.days
  , MAX(IF(event_type = 'view', cnt, 0)) AS view
  , MAX(IF(event_type = 'cart', cnt, 0)) AS cart
  , MAX(IF(event_type= 'remove_from_cart', cnt, 0)) AS remove_from_cart
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS purchase
  , MAX(IF(event_type = 'purchase', sales, 0)) AS sales
FROM (
  SELECT
    SUBSTR(event_time, 1, 10) AS days
    , event_type
    , COUNT(*) AS cnt
    , ROUND(SUM(price), 0) AS sales
  FROM
    ecommerce.feb
  WHERE
    price > 0
  GROUP BY
    1
    , 2
  ) AS pt
GROUP BY
  1
;

주목할 만한 점

월~수요일에 많이 구경하고 판매액도 많음
목, 금에 저조한 편
구매 횟수는 큰 변화가 없음 → 구매하는 사람이 항상 구매해서 그런가?
구매이력 있음 구매이력 없음
25,759 365,259
2월 셋째 월요일(2월 17일)이 미국 공휴일(대통령의 날 Presidents’ Day)이라 다른 월요일에 비해 구매가 많을 거라 생각했는데 꼭 그런 건 아니었음
- 하지만 다른 날짜들이 주차를 거듭할 수록 이벤트 횟수가 감소한 것과 달리 둘째 월요일(2월 10일)과 비교했을 때 비슷한 수치를 보인 부분이 주목할 만함
  - 제품을 구경한 횟수는 줄었지만 카트에 담는 행동과 구매 횟수, 판매액은 소폭 증가했다는 점도 포인트

구매이력 있음	구매이력 없음
25,759	365,259

→ 평균은 얼마인지 알고 싶어짐

SELECT 
  ROUND(AVG(a.view), 0) AS view_avg
  , ROUND(AVG(a.cart), 0) AS cart_avg
  , ROUND(AVG(a.remove_from_cart), 0) AS remove_avg
  , ROUND(AVG(a.purchase), 0) AS purchase_avg
  , ROUND(AVG(a.sales), 0) AS sales_avg
  FROM (
  SELECT
	pt.days
	, MAX(IF(event_type = 'view', cnt, 0)) AS view
	, MAX(IF(event_type = 'cart', cnt, 0)) AS cart
	, MAX(IF(event_type= 'remove_from_cart', cnt, 0)) AS remove_from_cart
	, MAX(IF(event_type = 'purchase', cnt, 0)) AS purchase
	, MAX(IF(event_type = 'purchase', sales, 0)) AS sales
  FROM (
	SELECT 
	  SUBSTR(event_time, 1, 10) AS days
	  , event_type
	  , COUNT(*) AS cnt
	  , ROUND(SUM(price), 0) AS sales
	FROM 
	  ecommerce.feb
	WHERE 
	  price > 0
	GROUP BY
	  1
	  , 2
	) AS pt
  GROUP BY
	pt.days
  ) a

→ 정확히 보기 위해 요일로 돌려보자

SELECT 
  evt.day
  , MAX(IF(event_type = 'view', cnt, 0)) AS 'view'
  , MAX(IF(event_type = 'cart', cnt, 0)) AS 'cart'
  , MAX(IF(event_type = 'remove_from_cart', cnt, 0)) AS 'remove_from_cart'
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS 'purchase'
  , MAX(IF(event_type = 'purchase', sales, 0)) AS 'sales'
FROM (
  SELECT 
    CASE
      WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 3 THEN 2
      WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 4 THEN 3
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 5 THEN 4
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 6 THEN 5
      WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 0 THEN 6
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 1 THEN 7
  	  WHEN MOD(SUBSTR(event_time, 9, 2), 7) = 2 THEN 1
    END AS day
    , event_type
    , COUNT(*) cnt
    , ROUND(SUM(price), 0) AS sales
  FROM
    ecommerce.feb
  WHERE 
    price > 0
  GROUP BY 
    1
    , 2
  ) evt
GROUP BY 
  1
;

주목할 만한 점

2월은 토요일이 5번 있어서(1, 8, 15, 22, 29일) 1일 데이터는 빼고 계산
월~수요일이 제품 구경도 많이 하고 비싼 제품을 더 구매하는 것으로 보임
- 구매가 발생한 횟수(빨강)은 큰 차이가 없는데 총 판매액이 높은 편
금요일에는 구경도 덜 하고 구매도 덜 한다.

주별 이벤트 발생 횟수 및 총 판매 금액

SELECT 
  evt.week_num
  , MAX(IF(event_type = 'view', cnt, 0)) AS 'view'
  , MAX(IF(event_type = 'cart', cnt, 0)) AS 'cart'
  , MAX(IF(event_type = 'remove_from_cart', cnt, 0)) AS 'remove_from_cart'
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS 'purchase'
  , MAX(IF(event_type = 'purchase', sales, 0)) AS 'sales'
FROM (
  SELECT 
    CASE
      WHEN SUBSTR(event_time, 9, 2) < 3 THEN 'etc.'
      WHEN SUBSTR(event_time, 9, 2) < 10 THEN '1st'
  	  WHEN SUBSTR(event_time, 9, 2) < 17 THEN '2nd'
  	  WHEN SUBSTR(event_time, 9, 2) < 24 THEN '3rd'
  	  ELSE '4th'
    END AS week_num
    , event_type
    , COUNT(*) cnt
    , ROUND(SUM(price), 0) AS sales
  FROM
    ecommerce.feb
  WHERE 
    price > 0
  GROUP BY 
    1
    , 2
  ) evt
GROUP BY 
  1
;

주목할 만한 점

etc.와 4주차는 7일이 채워지지 않아서 빼고 그래프로 그려보았음
소비자의 모든 행동이 첫 번째 주에 가장 활발하고 총 판매액도 가장 높음
- 할인 행사와 같은 이벤트를 첫 번째 주에 여는 게 효과적일 것이라 예상

시간별 이벤트 발생 횟수 및 총 판매 금액

SELECT
  pt.hh
  , MAX(IF(event_type = 'view', cnt, 0)) AS view
  , MAX(IF(event_type = 'cart', cnt, 0)) AS cart
  , MAX(IF(event_type= 'remove_from_cart', cnt, 0)) AS remove_from_cart
  , MAX(IF(event_type = 'purchase', cnt, 0)) AS purchase
  , MAX(IF(event_type = 'purchase', sales, 0)) AS sales
FROM (
  SELECT 
    SUBSTR(event_time, 12, 2) AS hh
    , event_type
    , COUNT(*) AS cnt
    , ROUND(SUM(price), 0) AS sales
  FROM 
    ecommerce.feb
  WHERE 
    price > 0
  GROUP BY
    1
    , 2
  ) AS pt
GROUP BY
  pt.hh
;

주목할 만한 점

오전 10시 - 오후 12시, 오후 6시 - 7시에 구매액이 크다
오후 6시 - 7시는 구경 많이 하고 카트에 넣고 빼는 과정도 많음
- 살까 말까 고민을 많이 한다는 뜻이 아닐까
- 이 때 제품 할인 이벤트, 쿠폰 이벤트 등을 하는 것도 구매까지 이어질 수 있을 듯

일별, 시간별 구매 횟수

WITH RECURSIVE temp AS (
  (SELECT
    0 AS hours
  )
  UNION
  (SELECT
    hours + 1
  FROM
    temp
  WHERE
    hours < 23
  )
)
SELECT
  pt.days
  , MAX(IF(pt.hh = 0, cnt, 0)) AS AM00
  , MAX(IF(pt.hh = 1, cnt, 0)) AS AM01
  , MAX(IF(pt.hh = 2, cnt, 0)) AS AM02
  , MAX(IF(pt.hh = 3, cnt, 0)) AS AM03
  , MAX(IF(pt.hh = 4, cnt, 0)) AS AM04
  , MAX(IF(pt.hh = 5, cnt, 0)) AS AM05
  , MAX(IF(pt.hh = 6, cnt, 0)) AS AM06
  , MAX(IF(pt.hh = 7, cnt, 0)) AS AM07
  , MAX(IF(pt.hh = 8, cnt, 0)) AS AM08
  , MAX(IF(pt.hh = 9, cnt, 0)) AS AM09
  , MAX(IF(pt.hh = 10, cnt, 0)) AS AM10
  , MAX(IF(pt.hh = 11, cnt, 0)) AS AM11
  , MAX(IF(pt.hh = 12, cnt, 0)) AS PM12
  , MAX(IF(pt.hh = 13, cnt, 0)) AS PM01
  , MAX(IF(pt.hh = 14, cnt, 0)) AS PM02
  , MAX(IF(pt.hh = 15, cnt, 0)) AS PM03
  , MAX(IF(pt.hh = 16, cnt, 0)) AS PM04
  , MAX(IF(pt.hh = 17, cnt, 0)) AS PM05
  , MAX(IF(pt.hh = 18, cnt, 0)) AS PM06
  , MAX(IF(pt.hh = 19, cnt, 0)) AS PM07
  , MAX(IF(pt.hh = 20, cnt, 0)) AS PM08
  , MAX(IF(pt.hh = 21, cnt, 0)) AS PM09
  , MAX(IF(pt.hh = 22, cnt, 0)) AS PM10
  , MAX(IF(pt.hh = 23, cnt, 0)) AS PM11
FROM (
  SELECT 
    SUBSTR(event_time, 1, 10) AS days
    , SUBSTR(event_time, 12, 2) AS hh
    , COUNT(*) AS cnt
  FROM 
    ecommerce.feb
  WHERE 
    event_type = 'purchase'
    AND price > 0
  GROUP BY
    1
    , 2
  ) AS pt 
  JOIN temp ON pt.hh = temp.hours
GROUP BY
  pt.days
;

주목할 만한 점

2020년 2월 3일 월요일 오후 1시에 가장 많은 구매가 발생
2020년 2월 9일 일요일 오후 8시에도 구매가 많았음
- 모두 첫째 주라는 점이 주목할 만함
첫째 주(2020/02/03 - 2020/02/09)

둘째 주(2020/02/10 - 2020/02/16)

셋째 주(2020/02/17 - 2020/02/23)

넷째 주(2020/02/24 - 2020/02/29)

기타(2020/02/01, 2020/02/02)

인사이트

이벤트를 열 거면 첫 번째 주에 열자
요일은 월~수 또는 토
시간은 오전에 할 거면 오전 10시 전후, 오후에 할 거면 오후 6시 전후로 고르자

느낀 점

1개월의 작은 데이터인데도 쿼리 작성을 통해 알아볼 수 있는 내용이 많아서 놀라웠음
- 고객
  - 보기만 하고 구매는 한 번도 안 한 사람
  - 구매 많이 한 사람’
  - 카트에 넣었지만 구매는 하지 않은 사람
  - 카트에 넣었다 뺐다 많이 하는 사람 등
- 제품
  - 가장 많이 구매한 제품
  - 가강 많이 본 제품
  - 가장 많이 카트에 담겼지만 판매는 안 된 제품
  - 가장 많이 카트에서 빠진 제품 등
- 고객 행동
  - 일별 행동 이벤트 발생 횟수
  - 주별
  - 시간별 등
그러나 한 달 동안의 데이터만 가지고는 의미 있는 결과를 도출해내긴 어렵다는 생각이 들었음
쿼리 작성 시 조건 설정을 잘 하지 않으면 잘못된 결과를 얻을 수 있으므로 주의해야 함
쿼리문 조건들도 잘 생각하고 알맞은 연산자를 사용해야 함

Suhyeon Lee

2 B R 0 2 B

이전 포스트

6일차

다음 포스트

2024-10-09

CodeKata

SQL

45. 3월에 태어난 여성 회원 목록 출력하기

Python

5. 숫자 비교하기

팀 과제

데이터 전처리 및 조건 설정

날짜별 이벤트 발생 횟수와 총 판매 금액

주목할 만한 점

주목할 만한 점

주별 이벤트 발생 횟수 및 총 판매 금액

주목할 만한 점

시간별 이벤트 발생 횟수 및 총 판매 금액

주목할 만한 점

일별, 시간별 구매 횟수

주목할 만한 점

인사이트

느낀 점

6일차

예제로 익히는 SQL - 5회차

0개의 댓글