2024-06-13

• Today

1. SQL 코드카타

2. 머신러닝 라이브세션 수강

3. SQL Challenge 3회차 과제 풀이

4. Python 으로 코드 짜보기

 

---

• Today I Learned

 

SQL

• Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.

Visits 테이블에는 있지만 Transactions 테이블에는 없는 user 를 찾는 질문이다!

방문을 했지만 업무를 하지 않는 customer_id 가 몇번 방문 했는가를 추출해야한다.

 

1. Visits 테이블에 있는 user 정보는 전부 불러와야 하기 때문에 LEFT JOIN 사용

2. Transactions 테이블에는 없는 user 정보를 가져와야 하기 때문에 IS NULL 를 사용

3. 두 테이블을 조인할 수 있는 키로 user_id 컬럼을 사용

4. customer_id 가 몇번 방문했는지를 계산해야하기 때문에 그룹화를 해준다.

 

SELECT *
FROM Visits v 
LEFT JOIN Transactions t 
ON v.visit_id=t.visit_id 
WHERE t.visit_id IS NULL

그룹화를 하지 않은 상태로 출력을 해보면 위와 같이 나온다!

SELECT customer_id
     , COUNT(v.visit_id) count_no_trans
FROM Visits v 
LEFT JOIN Transactions t 
ON v.visit_id=t.visit_id 
WHERE t.visit_id IS NULL
GROUP BY customer_id

성훈 튜터님 튜터링대로 순서를 생각해보고, 중간중간에 실행을 해보았더니 어느정도 수월하게 풀어볼 수 있었다!

 

 

 

---

 

SQL Challenge 3회차

 

과제 1번

 

어제 풀이를 다시 보다가 전환율이 NULL로 나오는 user 들을

0으로 바꾸고 싶기도 했고, JOIN 조건이 아닌 case when 으로 사용하면 어떻게 풀이를 해볼 수 있을까 해서 찾아보았다

 

# 0으로 바꾸기
SELECT location
	 , device
	 , ROUND((COUNT(DISTINCT 
	 			CASE WHEN timestampdiff(HOUR, a.occurred_at, b.occurred_at) <= 24
                # 첫번째 행동 후 후속 행동이 하루 안에 완료 (24시간 이내)
	 			AND b.user_id IS NOT NULL 
	 			AND a.occurred_at < b.occurred_at
                # 순서가 정해져있는 것은 = 가 안들어가는게 좋음 
	 			THEN a.user_id END)
	 			/COUNT(DISTINCT a.user_id) *100) , 2) AS 'creation_signup_conversion'
FROM 
		(
		SELECT user_id, occurred_at, location, device
		FROM yammer_events
		WHERE event_name = 'create_user'
		) a				# 계정 생성한 유저 테이블
LEFT JOIN 
		(
		SELECT user_id, occurred_at
		FROM yammer_events
		WHERE event_name = 'complete_signup'
		) b				# 회원가입 완료한 유저 테이블
ON a.user_id=b.user_id		# user_id 로 join		
GROUP BY location, device

 

CASE WHEN 을 사용하려면 SELECT 절에 적어주어야 하는데

이럴 경우 ON 절에서 작성했던 코드를 줄줄이 적어주어야 해서 내가 판단하기에는 너무 복잡해보이는 쿼리가 탄생한다.

 

CASE WHEN 에서 b.user_id IS NOT NULL 을 적어줘야 하는 이유가 잘 이해되지 않아 헤매었다.

>> 

b 테이블은 후속행동을 한 user 들인데

a 행동만 한 user 일 경우 b 테이블에는 NULL 값이 들어갈 수 있다.

a 행동후 b 행동으로의 전환율을 구한다면 b 테이블의 user_id 가 NULL 이 아닐 경우 라는 조건을 넣어주어야 한다

 

AND a.occurred_at < b.occurred_at

>>

현재 이 데이터에서는 순서대로 log 가 쌓여있지만 그렇지 않는 경우도 있다.

이렇게 전후관계가 중요한 경우에는 위와 같이 조건을 추가해주는 것이 좋다!

 

 

---

JD 분석

 

 

비즈니스 기회란?

"문제를 해결하거나 요구 사항을 충족하거나 기존 상황을 개선하여 개인이나 조직에 가치를 창출하는 상황 또는 조건"

구글링을 통해 찾아본 단어는 위와 같다!

 

튜터님과의 튜터링 해본 결과, 

"데이터분석에 따라 도출된 인사이트로 현재 회사의 지표가 좋아지는 결과를 이끌어내는 방법을 발굴해내라"

라는 뜻과 같았다.

 

인사이트에 따라 시행하지 못하고 있었던 것이나, 시행하고 있었던 것을 더 잘할 수 있는 방법을 발견하는 것

SWOT 분석의 Opportunity 기회 에서 비롯된 것이다. 

 

* SWOT 분석이란?

Strengths 강점: 조직이 잘하는 것, 특별한 점

Weakness 약점: 개선할 점, 개선할 수 있는 리소스

Opportunity 기회: 앞으로 활용할 수 있는 기회나 잠재적인 이점

Threats 위협: 앞으로 발생할 수 있는 이험한 요소나 잠재적인 문제

 

자사의 약점을 보완하고 기회를 찾아 키우는 것!

 

 

---

 

Python

 

 

프로젝트에 조금이나마 도움이 되기 위해 매일마다 파이썬을 공부해보려고 한다.

조금이나마 흥미있게 느껴졌던 파이썬 시각화로 데이터를 파악해보기부터 시작!

 

기초 프로젝트 때 정욱튜터님께서 제공해주셨던 제주특산물 데이터로 활용해보기로 했다.

간단히 확인해보니 결측치는 없는 것으로 확인되었다.

item 별로 유통된 갯수도 확인해보았다!

 

item 별로의 매출금액을 구해보고 싶었는데

price(원/kg) : 유통된 품목들의 kg 마다의 가격, 원 단위 여서 

supply(kg) : 유통된 물량, kg 단위 를 곱해서 계산을 해야할 것 같다

 

 

 

---

• Next

1. SQL 코드카타

2. 머신러닝 라이브세션 수강

3. SQL Challenge 3회차 과제 풀이

4. Python 으로 코드 짜보기

+ 머신러닝 개인과제 4번