2024-06-15

• Today

1. JD 분석

2. SQL 코드카타

3. SQL Challenge 3회차 과제 풀이

 

---

• Today I Learned

 

SQL

 

 

• There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.

1개의 테이블의 같은 컬럼안에 다른 정보가 있을 때의 차이 구하기

 

SELECT a.machine_id
     , ROUND(AVG(b.timestamp-a.timestamp),3) processing_time
  FROM (
        SELECT machine_id
             , process_id
             , timestamp
        FROM Activity
        WHERE activity_type = 'start'
        ) a
JOIN (
        SELECT machine_id
             , process_id
             , timestamp
        FROM Activity
        WHERE activity_type = 'end'
        ) b
    ON a.machine_id=b.machine_id
--    AND a.timestamp < b.timestamp
 GROUP
    BY a.machine_id

 

한 컬럼내에 다른 정보들이 들어있고, 각각의 조건을 걸고 싶다면 서브쿼리로 빼주어야 한다!

1. start 인 테이블과 end 인 테이블을 생성해준다.

2. 2개의 테이블을 JOIN 한 후 계산!

 

ON 절에 후속행동이 더 큰 값도 추가를 했었는데 그렇게 했더니 맞는 답이 나오지 않았다.

테이블 구조를 잘 확인해보니 

동일한 machine_id 에서 실행중인 process_id 가 2가지 였고 그 평균을 구하는 것이었기 때문에 나오지 않았던 것

 

 

 

(

1.52-0.712=0.808

+

4.12-3.14=0.98

)

/2 = 0.894

 

이렇게 나와야 하는데 

 

process_id 가 2쌍이기 때문에 

a.timestamp < b.timestamp 

를 추가 하게 되면

매칭시키는 timestamp 가 어떤 값을 가져와야하는지 알수가 없어진다!

요런 느낌...

 

어떤 구조를 가진 데이터베이스인지 잘 살펴보고 쿼리를 짜는 습관을 드려야겠다.

 

---

SQL Challenge 3회차 과제 풀이

 

 

과제 2번

 

과제 2. 회원가입 전환율 단계별 분석

---

목표: 유저의 계정 생성부터 회원가입 완료까지의 각 단계별 전환율을 24시간 이내에 완료된 경우로 계산.

• 계정 생성 후 이메일 입력 화면 진입률.
• 이메일 입력 화면 진입 후 개인 정보 입력 화면 진입률.
• 개인 정보 입력 화면 진입 후 회원가입 완료 전환율.
• 계정 생성 후 회원가입 완료 전환율.

SELECT ROUND(COUNT(DISTINCT email.user_id)/COUNT(DISTINCT created.user_id)*100,2) creation_to_email_rate
	 , ROUND(COUNT(DISTINCT info.user_id)/COUNT(DISTINCT email.user_id)*100,2) email_to_info_rate
	 , ROUND(COUNT(DISTINCT signup.user_id)/COUNT(DISTINCT info.user_id)*100,2) info_to_completion_rate
	 , ROUND(COUNT(DISTINCT signup.user_id)/COUNT(DISTINCT created.user_id)*100,2) conversion_rate
  FROM (
		SELECT user_id, occurred_at
		FROM yammer_events
		WHERE event_name = 'create_user'
		) AS created
  LEFT 
  JOIN(
		SELECT user_id, occurred_at
		FROM yammer_events
		WHERE event_name = 'enter_email'
		) AS email
	ON created.user_id=email.user_id
   AND timestampdiff(HOUR, created.occurred_at, email.occurred_at) <= 24
   AND created.occurred_at < email.occurred_at
  LEFT 
  JOIN(
		SELECT user_id, occurred_at
		FROM yammer_events
		WHERE event_name = 'enter_info'
		) AS info
	ON email.user_id=info.user_id
   AND timestampdiff(HOUR, email.occurred_at, info.occurred_at) <= 24
   AND email.occurred_at < info.occurred_at
  LEFT 
  JOIN(
		SELECT user_id, occurred_at
		FROM yammer_events
		WHERE event_name = 'complete_signup'
		) AS signup
	ON info.user_id=signup.user_id
   AND timestampdiff(HOUR, info.occurred_at, signup.occurred_at) <= 24
   AND info.occurred_at < signup.occurred_at

 

 

과제 3번

 

과제 3. 회원가입 전환율 및 이후 행동 전환율 분석

---

목표: 유저가 계정을 생성한 후 30분 이내에 각 단계별 전환율을 계산.

• 계정 생성 후 회원가입 완료 전환율.
• 회원가입 완료 후 홈페이지 진입률.
• 홈페이지 진입 후 메시지 좋아요 클릭률.
• 회원가입 후 메시지 좋아요 클릭 전환율.

SELECT ROUND(COUNT(DISTINCT signup.user_id)/COUNT(DISTINCT created.user_id)*100,2) creation_signup_rate
	 , ROUND(COUNT(DISTINCT page.user_id)/COUNT(DISTINCT signup.user_id)*100,2) signup_homepage_entry_rate
	 , ROUND(COUNT(DISTINCT message.user_id)/COUNT(DISTINCT page.user_id)*100,2) homepage_entry_like_message_rate
	 , ROUND(COUNT(DISTINCT message.user_id)/COUNT(DISTINCT signup.user_id)*100,2) signup_like_message_rate
  FROM (
		SELECT user_id 
			 , occurred_at 
		  FROM yammer_events 
		 WHERE event_name = 'create_user'
		  ) AS created
  LEFT 
  JOIN (
		SELECT user_id 
			 , occurred_at 
		  FROM yammer_events 
		 WHERE event_name = 'complete_signup'
		  ) AS signup
    ON created.user_id=signup.user_id
   AND TIMESTAMPDIFF(MINUTE,created.occurred_at, signup.occurred_at) <= 30
   AND created.occurred_at < signup.occurred_at
  LEFT 
  JOIN (
		SELECT user_id 
			 , occurred_at 
		  FROM yammer_events 
		 WHERE event_name = 'home_page'
		  ) AS page
    ON signup.user_id=page.user_id
   AND TIMESTAMPDIFF(MINUTE,created.occurred_at, page.occurred_at) <= 30
   AND signup.occurred_at < page.occurred_at
  LEFT 
  JOIN (
		   SELECT user_id 
			 , occurred_at 
		  FROM yammer_events 
		 WHERE event_name = 'like_message'
		  ) AS message
   ON page.user_id=message.user_id
  AND TIMESTAMPDIFF(MINUTE,page.occurred_at, message.occurred_at) <= 30
  AND page.occurred_at < message.occurred_at

 

 

 

---

• Next

1. SQL 코드카타

2. 파이썬 문제 풀이

3. 프로젝트