查询新增用户产生 活跃行为 之后造成流失的用户数据

Question

需求：查询3月1号-3月7号新增的用户，产生某活跃行为的总数，以及产生该行为后流失的用户数


流失定义：本周活跃，下周未活跃的用户定为流失


user表：

user_action表：

预期结果：

指标含义：


用户总数：在3/1号-3/7号注册的用户产生对应的行为总用户数


流失用户数：发生对应行为后，之后的一周没有活跃记录的用户数

本人试写过一段查询逻辑，但是报错，大概知道错误和Case那块有关，但不知道具体如何解决

select
t1.行为,
count(DISTINCT t1.memberid) as 总用户数,
sum(case when t1.memberid not in (select t1.memberid from t1 where time = 1) then 1 else 0 end) as 流失用户数
from
    (
    SELECT 
        fua.UserID as memberid,
        week(fua.CreateTime) - week(t.created_at) as time,
        Action as 行为
        FROM
            (
                select user_id, created_at 
                from user
                where created_at between Date_format('2019-03-01 00:00:00','%Y-%m-%d') and Date_format('2019-03-07 23:59:59','%Y-%m-%d')
            ) as t
        INNER JOIN user_action fua 
        ON t.user_id = fua.UserID
     where Action is not NULL
    GROUP BY memberid,行为
    ) as t1
GROUP BY t1.行为

Answer 1

子查询没有看到有聚合函数，GROUP BY memberid,行为 这行先去掉试试，即：

select
t1.行为,
count(DISTINCT t1.memberid) as 总用户数,
sum(case when t1.memberid not in (select t1.memberid from user as t1 where time = 1) then 1 else 0 end) as 流失用户数
from
    (
    SELECT 
        fua.UserID as memberid,
        week(fua.CreateTime) - week(t.created_at) as time,
        Action as 行为
        FROM
            (
                select user_id, created_at 
                from user
                where created_at between Date_format('2019-03-01 00:00:00','%Y-%m-%d') and Date_format('2019-03-07 23:59:59','%Y-%m-%d')
            ) as t
        INNER JOIN user_action fua 
        ON t.user_id = fua.UserID
     where Action is not NULL

    ) as t1
GROUP BY t1.行为