[SQL 레시피]

Hyeon·2024년 11월 12일

SQL 문제 풀이

목록 보기
37/61

12장 시계열에 따른 사용자 전체의 상태 변화 찾기

사용자는 사용시작일로부터 시간이 지나면?

  • 가입하지만 사용하지 않은 휴면 상태
  • 사용 중지
  • 충성도 높은 상태
    등 상태변화가 일어난다.

이때, 서비스를 운영하는 입장에서는 사용자가 계속해서 사용하기를 원할 것이며
목표와의 괴리를 어떻게 해결해야하는지 검토해야한다.

특히 휴면 사용자를 어떻게 하면 다시 사용하게 만들지도 계속해서 생각해야한다.

-> 서비스 사용을 시계열로 수치화하고 변화를 시각화하는 방법을 통해 현재 상태를
파악하고, 대책의 효과를 파악하는 것이 중요하다.

샘플 데이터 확인하기

1.mst users : 사용자 정보 데이터

2.action_log : 사용자 시간 데이터

등록 수의 추이와 경향 보기

  • 등록자가 감소 경향을 보인다면 ? 서비스를 활성화하기 어려워진다는 의미
  • 등록자가 증가 경향을 보인다면 ? 서비스를 이탈할지 아닐지를 보고, 활성화와 연결해보기

1.날짜별 등록수를 확인해보자

select register_date, count(distinct user_id) as counting from mst_users group by register_date;

이때 계속 오류가 뜸, WHY?

#error action code

select * from mst_users LIMIT 0, 1000

#error action code

INSERT INTO action_log VALUES     ('989004ea', 'U001', 'view'   ,'2016-10-01 18:00:00')   , ('989004ea', 'U001', 'view'   ,'2016-10-01 18:01:00')   , ('989004ea', 'U001', 'view'   ,'2016-10-01 18:10:00')   , ('47db0370', 'U001', 'follow' ,'2016-10-05 19:00:00')   , ('47db0370', 'U001', 'view'   ,'2016-10-05 19:10:00')   , ('47db0370', 'U001', 'follow' ,'2016-10-05 20:30:00')   , ('5asfv583', 'U001', 'follow' ,'2016-10-20 19:00:00')   , ('5asfv583', 'U001', 'view'   ,'2016-10-20 19:10:00')   , ('5asfv583', 'U001', 'follow' ,'2016-10-20 20:30:00')   , ('87b5725f', 'U002', 'follow' ,'2016-10-01 12:00:00')   , ('87b5725f', 'U002', 'follow' ,'2016-10-01 12:01:00')   , ('87b5725f', 'U002', 'follow' ,'2016-10-01 12:02:00')   , ('9afaf87c', 'U002', 'view'   ,'2016-10-02 13:00:00')   , ('9afaf87c', 'U002', 'comment','2016-10-02 15:00:00')

#error response code
Error Code: 1146. Table 'classicmodels.mst_users' doesn't exist

-> 해당 테이블이 존재하지 않는다고 뜸
-> 다시 진행함 (테이블을 생성한뒤 ,classicmodels 특정 데이터베이스 확인되었는지 지정하기

#correct action code : 테이블 생성하기

CREATE TABLE mst_users(     user_id         varchar(255),     sex             varchar(255),     birth_date      varchar(255),     register_date   varchar(255),     register_device varchar(255),     withdraw_date   varchar(255) )

#correct action code : 특정 데이터베이스 지정하기

USE classicmodels

#correct action code : 테이블 존재여부 확인하기

SHOW TABLES

#correct action code : 테이블 삽입 재시도

INSERT INTO mst_users VALUES     ('U001', 'M', '1977-06-17', '2016-10-01', 'pc' , NULL        )   , ('U002', 'F', '1953-06-12', '2016-10-01', 'sp' , '2016-10-10')   , ('U003', 'M', '1965-01-06', '2016-10-01', 'pc' , NULL        )   , ('U004', 'F', '1954-05-21', '2016-10-01', 'pc' , NULL        )   , ('U005', 'M', '1987-11-23', '2016-10-01', 'sp' , NULL        )   , ('U006', 'F', '1950-01-21', '2016-10-01', 'pc' , '2016-10-10')   , ('U007', 'F', '1950-07-18', '2016-10-01', 'app', NULL        )   , ('U008', 'F', '2006-12-09', '2016-10-01', 'sp' , NULL        )   , ('U009', 'M', '2004-10-23', '2016-10-01', 'pc' , NULL        )   , ('U010', 'F', '1987-03-18', '2016-10-01', 'pc' , NULL        )   , ('U011', 'F', '1993-10-21', '2016-10-01', 'pc' , NULL        )   , ('U012', 'M', '1993-12-22', '2016-10-01', 'app', NULL        )   , ('U013', 'M', '1988-02-09', '2016-10-01', 'app', NULL        )   , ('U014', 'F', '1994-04-07', '2016-10-01', 'sp' , NULL        )   , ('U015', 'F', '1994-03-01', '2016-10-01', 'app', NULL        )   , ('U016', 'F', '1991-09-02', '2016-10-01', 'pc' , NULL        )   , ('U017', 'F', '1972-05-21', '2016-10-01', 'app', NULL        )   , ('U018', 'M', '2009-10-12', '2016-10-01', 'app', NULL        )   , ('U019', 'M', '1957-05-18', '2016-10-01', 'pc' , NULL        )   , ('U020', 'F', '1954-04-17', '2016-10-02', 'app', NULL        )   , ('U021', 'M', '2002-08-14', '2016-10-02', 'sp' , NULL        )   , ('U022', 'M', '1979-12-09', '2016-10-02', 'app', NULL        )   , ('U023', 'M', '1992-01-12', '2016-10-02', 'sp' , NULL        )   , ('U024', 'F', '1962-10-16', '2016-10-02', 'app', NULL        )   , ('U025', 'F', '1958-06-26', '2016-10-02', 'app', NULL        )   , ('U026', 'M', '1969-02-21', '2016-10-02', 'sp' , NULL        )   , ('U027', 'F', '2001-07-10', '2016-10-02', 'pc' , NULL        )   , ('U028', 'M', '1976-05-26', '2016-10-02', 'app', NULL        )   , ('U029', 'M', '1964-04-06', '2016-10-02', 'pc' , NULL        )   , ('U030', 'M', '1959-10-07', '2016-10-02', 'sp' , NULL        )

#correct action code : 성공!

select * from mst_users

월별 등록 수와 전월비 확인하기

  • 전월비란? 이전달과의 비율을 의미
  • year_month로 집약해서 등록수를 집계한 뒤, lag 윈도 함수를 사용해서 전월비 구하기

1.월별 등록 수 확인하기

SELECT DATE_FORMAT(register_date, '%Y-%m') AS 'year_month',
       COUNT(DISTINCT user_id) AS counting 
FROM mst_users
GROUP BY DATE_FORMAT(register_date, '%Y-%m');

2.lag 함수를 통해 전월비 구하기

전월비 : (이번 달 등록수 / 저번 달 등록수) 100

select 'year_month', counting, concat(round((counting / lag(counting, 1) over (order by 'year_month') ) * 100,1),'%') as '전월비' 
from 
(
SELECT DATE_FORMAT(register_date, '%Y-%m') AS 'year_month',COUNT(DISTINCT user_id) AS counting 
FROM mst_users
GROUP BY DATE_FORMAT(register_date, '%Y-%m')
) as t ;

등록 디바이스별 추이

  • 원본 데이터는 다음과 같으나, register device를 기준으로 등록 디바이스 counting 할 예정
  • case when 구문 활용해서 월별 기준 등록 디바이스(app,sp,pc별) counting 하기
select 
date_format(register_date, '%Y-%m') as 'year_month',
sum(distinct case when register_device = 'pc' then 1 else 0 end) as pc,
sum(distinct case when register_device = 'sp' then 1 else 0 end) as sp,
sum(distinct case when register_device = 'app' then 1 else 0 end) as app
from mst_users
group by date_format(register_date, '%Y-%m') ;

지속력과 정착률 산출하기

  • 사용자가 등록하더라도 서비스를 지속해서 사용하지 않는다면 사용자가 아무리 많아도 활용으로 이어지지 않음
  • 예를 들어 인스타에서 사용자를 팔로우하더라도 해당 서비스를 지속하지 않으면 피드 전달 받을 수 없음
  • 등록 시점을 기준으로 일정 기간동안 사용자가 지속하고 있는지를 확인하기 위해 지속률과 정착률을 통해 경향을 확인할 수 있음

지속률: 등록일 기준으로 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표
정착률: 등록일 기준으로 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표

지속률은 사용자가 연속으로 서비스를 사용했을 때 counting
-> 뉴스 사이트, 게임 , SNS 지표에서 쓰이기 좋음
정착률은 한번이라도 서비스를 이용한 경우에 정착자로 불려 기준을 산출
-> 리뷰 사이트, Q&A 사이트 지표에서 쓰이기 좋음(사용자가 무언가를 체험할 때마다 사용하기를 기대해서)

지속률 구하기

  • step 1)
    준비해야할 컬럼 : user_id, register_date, action_date, latest_date , next_day_1
    : user별로 구분한 뒤 접속 일자, 다음 접속 일자,최신날짜, 접속 일자 + 1일 를 구하기
    -> 1. 최신날짜보다 이전 날짜여야한다.
    -> 2. 다음 접속 일자 = 접속일자 + 1일인 경우 지속했다고 판단한다.
select user_id , date_format(register_date,'%Y-%m-%d') as register_date,
lead(register_date,1) over (partition by user_id order by register_date) as action_date,
max(register_date) over (partition by user_id) as latest_date ,
date_add(register_date, interval 1 day) as next_day_1
from mst_users 
;

  • step 2)
    : next_1_day_action 컬럼을 만들어서 , 1번과 2번 모두 OK일 경우 1로 산출 / 그렇지 않을 경우 0으로 산출
with cte_1 as (select user_id , date_format(register_date,'%Y-%m-%d') as register_date,
lead(register_date,1) over (partition by user_id order by register_date) as action_date,
max(register_date) over (partition by user_id) as latest_date ,
date_add(register_date, interval 1 day) as next_day_1
from mst_users 
group by user_id , register_date
)

select user_id, register_date, 
case 
	when action_date <= latest_date  then 
	case 
		when next_day_1 = action_date then 1 
        else 0 end 
        else 0 end
        as next_1_day_action
from cte_1
;

  • step 3)
    : 일별 next_1_day_action 비율 산정하기
    최종 table : 날짜 & 날짜별 사용자의 평균 지속률
profile
Hyeon
이전 포스트

[SQL/Classicmodels Database] classicmodel 데이터를 활용한 실습 3문제 풀이

다음 포스트

[SQL/Classicmodels Database] 문제 1, 문제 2 풀이 (사무실별 최고 매출 직원 조회 | 평균 주문 금액 이상의 결제 기록만 있는 고객 )

0개의 댓글