请教关于SQL求retention users的问题

最近在练习SQL找工作，自己稍微设想了一些面试会碰到的题目，像是如果给你一个table:user_id, login_time

用这两栏去求daily, weekly, monthly retention users，目前想到的是用这方法来求解：
STEP1: 先求出distinct login_time, id
STEP2: 用window function求出上次登入相隔的天数
STEP3: 用where clause选出相隔天数等于1, count distinct id

1. 
   
2. #step 3
   
3. select login_time, count(distinct id)
   
4. from
   
5. ( # step 2
   
6. select login_time, id, login_time - lag(login_time) over (partition by id order by login_time asc) as return_days
   
7. from
   
8. ( # step 1
   
9. select distinct login_time::date as login_time, id
   
10. from sessions
    
11. ) tmp1
    
12. ) tmp2
    
13. where return_days =1
    
14. group by 1
    

复制代码

这样只要在用extract(week from login_time) 和 extract(month from login_time)就可以求出周回客数跟月回客数

问题来了:

1. 这个思路正确吗?

2. 这个解法解daily是没问题,不过解周和月因为是用week 和month，最终只能求出固定的周和月，而不是daily rolling 的weekly 和monthly的回客数,不知道以DS或是DA的角度,这种题目有可能会出现吗?


补充内容 (2020-8-20 06:45):
补充一下,有好心的版友分享了如何算daily, weakly 和 monthly retained 基于比较login_date和前七天或前30天的登入。
但如果要求的weekly retention是以周为单位,就是假设这周四到上周四比较上周四到上上周四的
...

首先，这有可能是个人观点但是我很少用nesting, 因为很多时候你的code别人也要看的，用cte的话别人看得比较清楚（说实话很多时候自己过一阵子回去看都看不太懂）。

不是很确定你的rolling retention definition, 但是这个用self join应该比较方便吧。

1. 
   
2. with user_data as (
   
3. select distinct user_id
   
4.        , login_time::date as login_day
   
5. from sessions
   
6.   )
   
7. 
   
8. select ud.login_day
   
9.        , count(distinct ud.user_id) as cohort_size
   
10.        , count(distinct case when datediff('day', ud.login_day, ud2.login_day) between 1 and 6 then ud2.user_id end) as week_1_retained
    
11.        , count(distinct case when datediff('day', ud.login_day, ud2.login_day) between 1 and 29 then ud2.user_id end) as month_1_retained
    
12. from user_data ud
    
13.   left join user_data ud2
    
14.     on ud.user_id = ud2.user_id
    
15.     and ud2.login_day between ud.login_day + 1 and ud.login_day + 29
    
16. group by 1
    

复制代码

jz042 发表于 2020-8-20 03:58
首先，这有可能是个人观点但是我很少用nesting, 因为很多时候你的code别人也要看的，用cte的话别人看得比较 ...

感谢大佬分享，我也是赞成用CTE比要清楚，不过有次面试的时候面试官反而要求我用subquery，所以现在都会混着用练习一下

用大佬的算法跑了一下，照我的理解cohort size就是dau, week_retained和month_retained代表的是同一批人在七天内和这个月内是否有登入过,是这样理解没错吧?

ryanhuang0703 发表于 2020-8-20 04:38
感谢大佬分享，我也是赞成用CTE比要清楚，不过有次面试的时候面试官反而要求我用subquery，所以现在都会 ...

是的，我也不太确定你说的rolling retention是怎么define的？

jz042 发表于 2020-8-20 05:31
是的，我也不太确定你说的rolling retention是怎么define的？

我稍微想了一下,我觉得weekly retention 应该是上周有登入过的ID在这周也有登入过,所以单位应该是以周为单位
这也是为什么一开始我选择用lag window function 然后以week function 做为单位去计算
不过这样的话就只能得到结果是礼拜一到礼拜天的week,而不是以login_day为基准的last week,不知道这样解释的通吗?

根据我的面试和工作经验，现在不是很提倡用cte了，具体原因DE的大佬求解惑。
题目的定义感觉楼主需要再讲一下，比如求哪些人的daily/weekly/monthly retention？retention 的定义是什么？
如果我们要求的是今天登录的人，哪些是weekly retented，而weekly retention的概念是今天的人在之前7天之内曾经登录过的话，可以用下面的方法：

这个求出来了在任何一个时刻，任何一个user，是不是daily/weekly/monthly retained user，如果相求某一时间段的user总数直接把时间限制加在这个query里面，再在这个基础上select count(distinct user) 就行了

哦，懂了，这个复杂的是在cohort这边，所以其实是要从今天开始往回计算 to guarantee you have a full month of data是吗？

Weekly比较简单，可以用extract DOW来算。我下面这个方法比较flexible，任何day range都可以，但是看起来特别复杂... 也许有更简单的方式？

1. select DATEADD(day, (DATEDIFF(day, CURRENT_DATE, login_day)/30-1)*30, CURRENT_DATE-1) as rolling_month_start
   
2.        , count(distinct ud.user_id) as cohort_size
   
3.        , count(distinct case when datediff('day', ud.login_day, ud2.login_day) between 1 and 29 then ud2.user_id end) as month_1_retained
   
4. from user_data ud
   
5.   left join user_data ud2
   
6.     on ud.user_id = ud2.user_id
   
7.     and ud2.login_day between ud.login_day + 1 and ud.login_day + 29
   
8. group by 1

复制代码