select count(b.device_id)/count(1) as avg_ret
from 
    (
     select distinct device_id,`date`
     from question_practice_detail 
    )a
left join (select distinct device_id,`date`
           from question_practice_detail ) b on date_add(a.`date`,interval 1 day) = b.`date`
                                                 and a.device_id = b.device_id;

MySQL

DATE_ADD(dt, INTERVAL 1 DAY)

或

dt + INTERVAL 1 DAY

DATE_ADD(dt, INTERVAL -1 DAY)

或

dt - INTERVAL 1 DAY

Hive / Spark

DATE_ADD(dt, 1)

(注意：没有 INTERVAL 关键字，直接传数字)

DATE_SUB(dt, 1)

(Hive 有专门的 DATE_SUB 函数)

PostgreSQL

dt + INTERVAL '1 day'

dt - INTERVAL '1 day'

Oracle

dt + 1

(Oracle 可以直接数字加减，1代表1天)

dt - 1

SQL Server

DATEADD(DAY, 1, dt)

DATEADD(DAY, -1, dt)