susinlee 님의 블로그

1. 코호트 분석(Cohort Analysis) 이란?코호트 분석은 특정 시점에서 공통된 특성을 가진 사용자 그룹(코호트)을 추적하여 행동 패턴을 분석하는 기법이다 핵심 개념시간 기반(Time-based): 사용자를 가입 월, 첫 구매 월 등 특정 기간을 기준으로 그룹화한다.이벤트 기반(Event-based): 특정 행동을 기준으로 사용자 그룹을 구분할 수도 있다.장기적인 사용자 행동 변화 분석에 유리하다.코호트 분석의 필요성특정 기간 동안의 신규 고객 유지율(Retention Rate) 분석 가능특정 시점에서 가입한 고객이 얼마나 제품을 지속적으로 사용하는지 평가신규 기능 출시 후 사용자 반응 분석마케팅 캠페인의 효과 측정코호트 분석 사례유료 전환율 분석1월, 2월, 3월 가입한 고객을 각각 분석하여 ..

재귀 쿼리의 개념재귀 쿼리는 트리 구조 데이터 또는 그래프 데이터(네트워크, 경로 탐색)를 다룰 때 사용되는 SQL 기법기본적으로 자기 자신을 참조하는 방식으로 데이터를 반복적으로 조회할 수 있음SQL에서 재귀 쿼리는 CTE의 확장 기능이며, WITH RECURSIVE를 사용함 재귀 쿼리의 구조재귀 쿼리는 세 가지 요소로 구성됨WITH RECURSIVE recursvie_cte AS ( -- 1. Anchor 쿼리 (기본 시작 데이터) SELECT 초기값 FROM 테이블 WHERE 조건 UNION ALL -- 2. Recursive 쿼리 (자기 자신을 참조하여 반복 수행) SELECT 다음 단계의 값 FROM 테이블 JOIN recursive_ct..

1. 날짜/시간 추출기능PostgreSQLMySQL설명연도 추출EXTRACT(YEAR FROM date_col)YEAR(date_col)연도를 추출월 추출EXTRACT(MONTH FROM date_col)MONTH(date_col)월을 추출일 추출EXTRACT(DAY FROM date_col)DAY(date_col)일을 추출시간 추출EXTRACT(HOUR FROM date_col)HOUR(date_col)시간을 추출분 추출EXTRACT(MINUTE FROM date_col)MINUTE(date_col)분을 추출초 추출EXTRACT(SECOND FROM date_col)SECOND(date_col)초를 추출 2. 날짜/시간 차이 계산기능PostgreSQLMySQL설명날짜 차이(일 단위)AGE(date_co..

[문제]https://datalemur.com/questions/sql-bloomberg-stock-min-max-1 Bloomberg SQL Interview Question | DataLemurBloomberg SQL Interview Question: Write a query to retrieve the highest and lowest open prices for each FAANG stock by month over the years.datalemur.com [풀이]DISTINCT ON (컬럼) - PostgreSQL 전용괄호안 컬럼 기준으로 중복을 제거ORDER BY 절을 통해 첫 번째로 등장하는 행을 설정할 수 있다예를 들어, ORDER BY open_price DESC를 적용할 경우, 컬럼..

오전부터 한 오후 4시 30분까지 전처리를 진행한 것 같다. 어우.. 유저별로 구매 항목에 특정 브랜드에 속하는지 속하지 않는지 파악하기 위해 이것저것 추출하고, 바꾸고 채워넣고... 그러고나서 분석을 진행하였는데 결과가 나진 않았지만 목적을 가지고 첨부터 마무리까지 지어서 분석하는 느낌이 들었다. 구매로 이어진 고객과 그렇지 않은 고객을 나눠서 특징을 비교해보고 모델링도 해서 인사이트를 내려고 했는데 결과는 꽝이었다. 아마 잘못된 방법이었을 거다. 구매 하지 않은 고객이 이후에 구매를 할 수도 있는 것이고... 특정 기간 내에 구매로 이어질지 아닐지를 예측하려면 시간데이터도 써야할 것이기 때문에... 내일은 연관분석과 추천시스템을 구현해보지 않을까 싶다.SQL 라이브 세션 복습도 해야하고, 시계열이랑 ..

최종 프로젝트를 본격적으로 시작했다.행동 로그 데이터를 어떻게 얻게되어서 연관분석과 고객 세그먼테이션을 진행해보았다. 근데 뭐랄까.. 그냥 해본 느낌이다.어떠한 문제를 발견하고, 그 문제를 해결하기 위해 분석을 하는 게 아닌 그냥 데이터가 있어서 하는 느낌이랄까..? 지금 가지고 있는 데이터셋에서 무얼 발견하고, 어떤걸 제시할 수 있을지 모르겠다. 일반적으로 이러한 데이터로 하는 분석들이 어느정도 정립되어 있을 것이라고 생각하고, 그래서 정해진 답을 찾으려고 하는 편인데... 그러다 보니 GPT에 더 의존하는 것 같기도 하고 아무튼 그닥 좋지 않은 것 같다. 뭔가 고민하는 걸 멈춘 느낌이랄까 내일은 컨셉을 좀 잡거나 EDA를 각잡고 해서 문제를 정의하든 목표를 설정하든 해봐야겠다.

[문제]https://datalemur.com/questions/supercloud-customer [풀이]1. 두 테이블을 병합한 뒤 customer_id 별로 그룹화해준다2. 고유한 제품카테고리 개수를 세어주고 그 개수가 3개인 행만 필터링한다3. 해당 customer_id만 출력한다 [코드]SELECT c.customer_idFROM customer_contracts cJOIN products p ON c.product_id = p.product_idGROUP BY c.customer_idHAVING COUNT(DISTINCT p.product_category) = 3

[문제]https://datalemur.com/questions/signup-confirmation-rate [풀이]1. email 테이블을 돌면서 text 테이블의 email_id와 같으면서 action이 Confirmed 인 애들을 1 아니면 null로 변환2. 를 count를 해주면 null이 아닌 id만 카운트3. 전체 count로 나눠주면 ok (정수/정수가 되지않아 1.으로 소수로 만들어줌).  [코드]SELECT ROUND( COUNT( CASE WHEN EXISTS ( SELECT 1 FROM texts t WHERE t.email_id = e.email_id AND t.signup_action = 'Confirmed' ) THEN 1 E..