Hive sql 常见面试题-查询所有用户的连续登录两天及以上的日期区间

1. 题目需求

从登录明细表（user_login_detail）中查询出，所有用户的连续登录两天及以上的日期区间，以登录时间（login_ts）为准。

期望结果如下：

2. 需要用到的表：

登录明细表：user_login_detail

3. 查询sql

SELECT DISTINCT

  user_id,

  first_value (login_ts) OVER (PARTITION BY user_id,subdate ORDER BY login_ts) start_date,

  last_value (login_ts) OVER (PARTITION BY user_id,subdate ORDER BY login_ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) end_date

FROM

  (

    SELECT

      user_id,

      login_ts,

      subdate,

      count(*) OVER (PARTITION BY user_id, subdate) count_num

    FROM

      (

        SELECT

          user_id,

          login_ts,

          date_sub (login_ts, rk) subdate

        FROM

          (

            SELECT distinct

              user_id,

              date_format (login_ts, 'YYYY-MM-dd') login_ts,

              rank() over (PARTITION BY user_id order BY login_ts) rk

            FROM user_login_detail

          ) t1

      ) t2

  ) t3

WHERE count_num >= 2