[sql] 누적 접속자 쿼리, 최초 구매

 

 

누적접속자

 

select		aa.date
				,aa.server
				,count(distinct accountid) --dau
				,sum(count(x)) over (partition by server order by aa.date) -- 누적 접속자
from		
( 
select		date
				,server
				,accountid
				,row_number() over(partition by server, accountid order by date asc) as x
from		workdb2.dbo.dau2 with (nolock)
where
				date between  '2020-07-01' and '2020-07-02'
) as aa
group by
				aa.date
				,aa.server

 

최초구매

 

select		aaa.productname, suM(aaa.[user]) 'user'
from		
(
select		bb.productname, bb.server, count(distinct bb.accountid) 'user'
from		
(
select		server, accountid
from		[example].[dbo].[all_nru]
where
				cast(time as date) between '2020-01-21' and '2020-02-18'       ------ 기간
group by
				server, accountid
) as aa,
(
select		a.server, a.accountid, b.productname,  a.time,  ROW_NUMBER () over (partition by a.server, a.accountid order by a.time asc) as rn 
from		workdb.dbo.purchase a with (nolock)
join			workdb.dbo.cm_purchase b with (nolock) on a.clientproductid=b.clientproductid 
and
				cast(a.time as date) >=cast(b.created_Date as date) and cast(a.time as date) < cast(b.deleted_Date as date)
where
				cast(a.time as date) between '2020-01-21' and '2020-02-18' ------ 기간
) as bb
where
				aa.server=bb.server and aa.accountid=bb.accountid and rn= 7 ----- 최초~N번쨰 구매횟수
group by
				bb.productname, bb.server
) as aaa
group by
				aaa.productname
order by
				2 desc