2024-09-03

• Today

데이터 분석 온보딩 과제

 

---

• Today I Learned

 

SQL

 

1. 첫 코칭 신청 이후, 다음 코칭을 받기 까지의 평균 기간

WITH r AS (
        SELECT userid
             , createdat
             , ROW_NUMBER() OVER (PARTITION BY userid ORDER BY createdat ASC) AS rnk
        FROM data_set
        WHERE result != 'cancel'
        ),
	c AS (
        SELECT userid
             , MAX(CASE WHEN rnk = 1 THEN createdat ELSE 0 END) AS first_createdat
             , MAX(CASE WHEN rnk = 2 THEN createdat ELSE 0 END) AS second_createdat
        FROM r
        GROUP BY userid
        HAVING count(*) >= 2
        ) 
SELECT AVG(datediff(second_createdat,first_createdat))
FROM c

orderid 별 createdat 오름차순 순서로 정렬을 하여 순서를 매겨준다.

* 동일한 유저가 여러번 신청가능 한 것으로 확인하였고,

코칭을 '받은' 이후, 두번째 코칭을 '받은' 것을 기준으로 순서를 매겨주기 위해 cancel 값을 제외하였다.

 

1번째 날짜와, 2번째 날짜의 차이를 datediff 로 계산하여 평균을 내주었다.

 

2. 코스 별 최종 합격인원은 각각 몇 명, 몇 퍼센트인가? 

WITH pass AS (
        SELECT course 
             , count(DISTINCT userid) pass_cnt
        FROM data_set
        WHERE status = '최종합격'
        GROUP BY course 
        ),
    	total AS (
        SELECT course
             , count(DISTINCT userid) total_user
        FROM data_set
        GROUP BY course
        )
SELECT p.course
     , p.pass_cnt
     , (p.pass_cnt/t.total_user)*100.0 AS percentage
FROM pass p
JOIN total t ON p.course=t.course

 

코스별로 그룹화 한 후

합격한 인원과 전체 인원을 구하고 퍼센트를 구해주었다.

* 코칭을 '받은' 것과 별개로 신청을 한 인원에 따라 분류하였으므로 CANCEL 값은 제외하지 않았다.

 

3. 최종 합격한 인원과 다른 이용자들과의 차이점

# type 별 최종 합격 인원과 최종합격이 아닌 인원의 차이(내배캠)
WITH a AS (
SELECT type
	 , count(userid) total_cnt
	 , count(DISTINCT userid) cnt_user
FROM data_set
WHERE status = '최종합격' AND course = 'NBCamp'
GROUP BY type
),
b AS (
SELECT type
	 , count(userid) total_no_cnt
	 , count(DISTINCT userid) cnt_no_user
FROM data_set
WHERE status != '최종합격' AND course = 'NBCamp'
GROUP BY type
)
SELECT *
FROM a
JOIN b ON a.type=b.TYPE

이렇게 쿼리를 짰지만..

생각해보니 앞서 최종합격한 인원은 제외 해야함을 깨달았다.

내일은 if 나 case 문을 사용해서 해보던지 아니면 필터링을 해볼 수 있는 방안을 고민해보아야겠다.

 

---

• Next

이용자 별 차이점 인사이트 도출

파이썬으로도 생각해보기