SQL count consecutve days

Question

This is the SQL database data:

logs

| user_id  | created_at 
------------------------------------------
|   123    | 2023-06-09 01:14:41.034482 |
|   123    | 2023-06-09 01:14:25.576612 |
|   123    | 2023-06-08 19:29:29.035590 |
|   123    | 2023-06-07 18:56:58.093392 |
|   123    | 2023-06-07 18:50:52.096982 |
|   123    | 2023-06-07 16:09:23.021376 |
|   123    | 2023-06-06 15:51:08.487921 |
|   123    | 2023-06-06 15:48:57.417946 |
|   555    | 2023-06-03 15:43:57.417946 |
|   555    | 2023-06-02 15:28:57.417946 |
|   555    | 2023-06-01 15:18:57.417946 |

I'm trying to get the number of consecutives days that user1 has logged till today.

My desired output:

user_id | streak 
------------------------------------------
  123   | 4

I tried it with this code, but I'm not sure how to combine dates with the same day.

SELECT user_id, created_at,
ROW_NUMBER() OVER (ORDER BY created_at) as RN
from logs WHERE user_id=123

What database are you actually using? MySQL, Server-Server, and SqlLite are all different. — Error_2646, Jun 09 '23 at 17:15
I remove the tag spam, please tag only the RDBMS you are using. — Stu, Jun 09 '23 at 17:15
You can create a date table (that has all dates) and join to that. Then you will have a list of the days they logged in. Then you can check for gaps. — Brad, Jun 09 '23 at 17:33
Here? https://stackoverflow.com/questions/20402089/detect-consecutive-dates-ranges-using-sql — Marc, Jun 09 '23 at 18:06

score 1 · Answer 1 · answered Jun 09 '23 at 18:07

SQLFiddle's SqlLite option is being buggy, so this is in Postgres. Should be all the same except you'll have to play with the date add in SqlLite to find the equivalent of DATE - INTEGER_IN_DAYS in postgres, in the start_of_streak derivation.

SQLite equivalent of SQL Server DateAdd function

credit to https://blog.jooq.org/how-to-find-the-longest-consecutive-series-of-events-in-sql/ for the reference

CREATE TABLE some_table 
(
    user_id INT,
    created_at  TIMESTAMP
);
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-12 01:14:41.034482');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-09 01:14:41.034482');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-09 01:14:25.576612');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-08 19:29:29.035590');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-07 18:56:58.093392');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-07 18:50:52.096982');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-07 16:09:23.021376');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-06 15:51:08.487921');
INSERT INTO some_table (user_id, created_at) VALUES ('123', '2023-06-06 15:48:57.417946');
INSERT INTO some_table (user_id, created_at) VALUES ('555', '2023-06-03 15:43:57.417946');
INSERT INTO some_table (user_id, created_at) VALUES ('555', '2023-06-02 15:28:57.417946');
INSERT INTO some_table (user_id, created_at) VALUES ('555', '2023-06-01 15:18:57.417946');


with user_date_combos as (
    select distinct
           user_id,
           date(created_at) as created_date
      from some_table
),
consecutive_grouping AS (
    SELECT
      user_id,
      created_date,
      created_date - cast(ROW_NUMBER() OVER (
        partition by user_id
            ORDER BY created_date) as int) + 1 as start_of_streak
    FROM user_date_combos
  )
select user_id,
       max(length_of_streak) as longest_streak
  from (
       select user_id,
              start_of_streak,
              count(1) as length_of_streak
         from consecutive_grouping
        group
           by user_id,
              start_of_streak) as tmp
 group
    by user_id

user_id	longest_streak
123	4
555	3

Fiddle:

http://sqlfiddle.com/#!17/c068c8/7

@forpas SQLite is fine. Problem was with the SQLFiddle site when I was using the SQLite setting. — Error_2646, Jun 09 '23 at 19:24

score 0 · Answer 2 · answered Jun 09 '23 at 19:14

This can be done by using the window method lag() to obtain the preceding row and then determining whether or not there are consecutives:

with cte as (
  select user_id, date(created_at) as created_at
  from mytable
  group by user_id, date(created_at)
),
cte2 as (
  select *, julianday(created_at) - julianday(lag(created_at) over (partition by user_id order by created_at)) as date_diff
  from cte 
),
cte3 as (
  select *, SUM(CASE WHEN date_diff = 1 THEN 0 ELSE 1 END) OVER (partition by user_id order by created_at) AS grp 
  from cte2
),
cte4 as (
  select user_id, count(1) as period_length
  from cte3
  group by user_id, grp
)
select user_id, max(period_length) as longest_period
from cte4
group by user_id

Demo here

SQL count consecutve days

2 Answers2