with tmp 
as(
    select
    device_id
    ,date
    from question_practice_detail
    group by device_id, date
)

select 
count(b.device_id) / count(a.device_id)
from tmp a
left join
tmp b
on a.device_id = b.device_id
and a.date = date_sub(b.date, interval 1 day)