[SQL] 잔존율 retention 집계 하기 D+7, M+1

 

 

use workdb2
go

WITH ##repeat_interval AS (
    SELECT * FROM (
        VALUES
             ('01 day repeart', 1)
            ,('02 day repeart', 2) 
	       ,('03 day repeart', 3) 
            ,('04 day repeart', 4)
		  ,('05 day repeart', 5)
		  ,('06 day repeart', 6)
		  ,('07 day repeart', 7)
        ) AS a (index_name, interval_date)
    )
,##rau_0501_0531 AS (
select		cast(time as date) 'date', server, accountid
from		example2.dbo.all_rau 
where
				cast(time as date) between '2020-05-01' and '2020-05-31'
group by
				cast(time as date) , server, accountid
)
, ##login_0501_0731 AS (
select		date, server, accountid
from		workdb2.dbo.dau2
where
				date between '2020-05-01' and '2020-07-31'
group by
				date, server, accountid
)
, ##action_log_with_users AS (
select		a.server
				,a.accountid
				,a.date as return_date
				,b.date as action_date
				,max(b.date) over () as latest_date
				,dateadd(dd, 1, a.date) 'next_day1'
from		##rau_0501_0531 a 
left join	##login_0501_0731 b  on a.server=b.server and a.accountid =b.accountid
)
,  ##action_log_with_interval_date as (
select		server
				,accountid
				,return_date
				,action_date
				,latest_date
				,b.index_name
				,dateadd(dd, interval_date, return_date) as index_date
from		##action_log_with_users a 
cross	join	##repeat_interval b 
), ##user_action_flag as (
select		server
				,accountid
				,return_date
				,index_name
				,sign(sum(case when index_date <= latest_date then
								  case when index_date = action_date then 1 else 0 end
								   end
								   ) 
								   )  as index_date_action
from		##action_log_with_interval_date
group by
				server,accountid,return_date,index_name
)

select		return_date
				,server
				,index_name
				,avg(100.0* index_date_action) as repeat_rate
from		##user_action_flag
where
				server = 'kr'
group by
				return_date ,server,index_name
order by
				return_date ,server,index_name

 

 

drop table #dau

declare @start_date  datetime,  @end_date datetime
set		@start_date = '2020-01-01'
set		@end_date = '2020-07-01'

select		replace(convert(char(7), date, 120), '-', '') 'year_month'
				,server
				,accountid
into		#dau
from		workdb2.dbo.dau2 with (nolock)
where
				date between  convert(char(8), @start_date, 120) + '01' and convert(char(8), @end_date, 120) + '01'
group by
				replace(convert(char(7), date, 120), '-', ''), server, accountid



select		aa.year_month
				,aa.server
				,count(distinct aa.accountid) 'MAU'
				,count(distinct case when bb.year_month = aa.year_month+1 then bb.accountid else null end) 'M+1'
				,count(distinct case when bb.year_month = aa.year_month+2 then bb.accountid else null end) 'M+2'
				,count(distinct case when bb.year_month = aa.year_month+3 then bb.accountid else null end) 'M+3'
				,count(distinct case when bb.year_month = aa.year_month+4 then bb.accountid else null end) 'M+4'
				,count(distinct case when bb.year_month = aa.year_month+5 then bb.accountid else null end) 'M+5'
				,count(distinct case when bb.year_month = aa.year_month+6 then bb.accountid else null end) 'M+6'
				,count(distinct case when bb.year_month = aa.year_month+7 then bb.accountid else null end) 'M+7'
from		#dau aa 
left join	#dau bb on aa.accountid =bb.accountid and aa.server=bb.server
group by
				aa.year_month, aa.server

 

 

 

 

declare @date datetime
set @date = '2020-06-01'

select		aa.year_month
				,aa.server
				,count(distinct aa.accountid) 'users'
				,count(distinct bb.accountid) 'M=1'
from		
(
select		replace(convert(char(7), @date, 120), '-', '') 'year_month'
				,server
				,accountid
from		workdb2.dbo.dau2 with (nolock)
where
				date between  convert(char(8), @date, 120) + '01' and CONVERT(CHAR(10), DATEADD(mm, 1, @date) - DAY(@date), 23)
) as aa
left join
(
select		replace(convert(char(7), @date, 120), '-', '') 'year_month'
				,server
				,accountid
from		workdb2.dbo.dau2 with (nolock)
where
				date between dateadd(mm, +1, convert(char(8) , @date, 120) + '01' ) and 	CONVERT(CHAR(10), DATEADD(mm, 2, @date) - DAY(@date), 23)
) as bb		on	aa.accountid=bb.accountid and aa.server=bb.server
group by
				aa.year_month, aa.server