面试题：连续登陆3天的用户

方法1：

SELECT  a.name1 
FROM ord a 
JOIN ord b 
    on a.name1=b.name1 and DATEDIFF(b.orderdate,a.orderdate)=1
JOIN ord c 
    on c.name1=b.name1 and DATEDIFF(c.orderdate,b.orderdate)=1;

方法2：

select user_id,data_sub(login_date,rank) as diff,count(1) as cnt
from
(
    select user_id,login_date,row_number() over (partition by user_id order by login_date) as rank
    from (select user_id,to_date(login_date) from log) log
） tmp
group by user_id,data_sub(login_date,rank)
having count(1)>3

//login_date 时间原始是时间戳格式

方法3：

SELECT * 
FROM
(
    SELECT *,LEAD(orderdate,2)over(PARTITION by name1 ORDER BY orderdate)date3 
    FROM ord
    // 该处SQL结果示例在上图
) a
WHERE DATEDIFF(date1,orderdate)=2;