牛客 SQL29_计算用户的平均次日留存率

目录

1.题目:

2.思路

3.代码

4.运行结果

---

1.题目:

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

2.思路

        0.  根据牛客的题解画图更好理解,左侧代码是借鉴的题解

        1.  根据deviced_id对客户去重,,再查到相应的date2,得到表bbb,

        2.  把表bbb和原表左关联,原表(aaa) left join 右表(bbb)

        3.  关联条件有两个:

                第一个条件是 aaa表的deviced_id = bbb表的deviced_id

                第二个条件是:根据 aaa表 的date1算出下一天的日期(second_date),让second_date =  表bbb 的date2,目的是查询到客户第二天也活跃

                注意:表aaa的date是date1 , 表bbb的date是date2

        4.对关联后的表处理,其中date2是第二天也活跃的用户,date1是第一天活跃的用户

        5.平均概率 avg_ret = count(date2) / count(date1)

3.代码

with
    bbb as (
    select
        DISTINCT device_id,
        date
#         date_add(date,INTERVAL 1 day) as second_day
    from niuke.question_practice_detail
),
t1 as (
    SELECT DISTINCT
        aaa.device_id,
        aaa.date AS date1,
        bbb.date AS date2
    FROM
        niuke.question_practice_detail AS aaa
            LEFT JOIN bbb
                      ON (aaa.device_id = bbb.device_id) AND (date_add(aaa.date, INTERVAL 1 DAY) = bbb.date)
)
select count(date2)/count(date1) as avg_ret from t1;

4.运行结果