SQL29 计算用户的平均次日留存率

最新推荐文章于 2024-05-21 21:12:00 发布

我要去看得最远的地方

最新推荐文章于 2024-05-21 21:12:00 发布

阅读量107

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/qq_42434171/article/details/130609121

版权





select count(date2)/count(date1) as avg_ret from (
     select  distinct t1.device_id, t1.date as date1,t2.date as date2 from question_practice_detail t1
     left join (select distinct device_id,`date` from question_practice_detail) t2
     on t1.device_id=t2.device_id and date_add(t1.date,interval 1 day)=t2.date )t3;


# select count(date2) / count(date1) as avg_ret
# from (
#     select
#         distinct qpd.device_id,
#         qpd.date as date1,
#         uniq_id_date.date as date2
#     from question_practice_detail as qpd
#     left join(
#         select distinct device_id, date
#         from question_practice_detail
#     ) as uniq_id_date
#     on qpd.device_id=uniq_id_date.device_id
#         and date_add(qpd.date, interval 1 day)=uniq_id_date.date
# ) as id_last_next_date