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

select

(

select count(date_add_1day) from

(

select table1.device_id,date_add_1day from

(

select device_id,date from question_practice_detail

group by device_id,date

) as table1

join

(

select device_id,date_add(date,interval 1 day) as date_add_1day from question_practice_detail

group by device_id,date_add(date,interval 1 day)

) as table2

on table1.device_id = table2.device_id and table1.date = table2.date_add_1day

) as t1

)

/

(

select sum(date_count) from

(

select device_id,count(distinct date) as date_count from question_practice_detail

group by device_id

) as t    

)

as avg_ret