题解 题目定义有问题没，我按照常用的次日留存率定义算了一下

 # with

#     tep as (

#         select

#             device_id,

#             date,

#             lead(date) over (

#                 partition by

#                     device_id

#                 order by

#                     date

#             ) as nextdate

#         from

#             (

#                 select distinct

#                     device_id,

#                     date

#                 from

#                     question_practice_detail

#                 order by

#                     date

#             ) t

#     )

# SELECT

#     ROUND(

#         COUNT(

#             distinct CASE

#                 WHEN DATEDIFF(nextdate, date) = 1 THEN device_id

#             END

#         ) / COUNT(distinct device_id),

#         4

#     ) AS avg_ret

# FROM

#     tep;

WITH first_day AS (

    SELECT device_id, MIN(date) AS first_date

    FROM question_practice_detail

    GROUP BY device_id

),

next_day AS (

    SELECT

        f.device_id,

        f.first_date,         -- 这里必须返回 first_date

        MIN(q.date) AS next_date

    FROM first_day f

    LEFT outer JOIN question_practice_detail q

        ON f.device_id = q.device_id

       AND q.date > f.first_date

    GROUP BY f.device_id, f.first_date

)

SELECT

        round(COUNT(CASE WHEN DATEDIFF(next_date, first_date) = 1 THEN device_id END)

        / COUNT(*),4) AS avg_ret

FROM next_day;