current position:Home>SQL statistics of users logged in for N consecutive days

SQL statistics of users logged in for N consecutive days

2022-06-24 09:51:40The figure under the stars

Their thinking

  1. user Number of logins per day Probably not only once , Therefore, first set the user's login date every day duplicate removal ;
  2. Then press user ID grouping , according to Login date (yyyy-mm-dd) Sort rn;
  3. Calculation Login date -rn( Sort value of step 2 ) as diff_date – When the user logs in continuously , The result of each subtraction is the same ;
  4. according to user_id, diff_date grouping , Statistics count(*)>N The number of , That is, the final result .

Code implementation

select
    user_id, login_date, diff_date
from(
    select
        user_id, login_date,
        date_sub(login_date, rn) diff_date  --  The third step 
    from(
        select
            user_id, login_date,
            row_number() over(partition by user_id order by login_date) rn  --  The second step 
        from(select distinct user_id, login_date from login_info)t  --  First step 
        )tt
    )ttt
group by user_id, diff_date
having count(*)>N

copyright notice
author[The figure under the stars],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/175/202206240903016516.html

Random recommended