题解 | #计算用户的平均次日留存率#

select
    sum(stay_flag) / count(*) as avg_ret
from
    (
        select
            a.device_id,
            a.date,
            coalesce(b.stay_flag, 0) as stay_flag
        from
            (
                select
                    device_id,
                    date
                from
                    question_practice_detail
                group by
                    1,
                    2
            ) a
            left join (
                select
                    device_id,
                    date_add(date,interval 1 day) as add_1day,
                    1 as stay_flag
                from
                    question_practice_detail
                group by
                    1,
                    2
            ) b on a.device_id = b.device_id
            and a.date = b.add_1day
    ) t1