SQLFiddle的SqlLite选项有缺陷，所以这是在Postgres中。应该都是一样的，除了你必须在SqlLite中使用日期添加来在postgres中找到DATE - INTEGER_IN_DAYS的等价物，在start_of_streak派生中。
SQLite equivalent of SQL Server DateAdd function
信用到https://blog.jooq.org/how-to-find-the-longest-consecutive-series-of-events-in-sql/以供参考

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

| 用户id|最长条纹|
| - -----|- -----|
| 一百二十三|4|
| 五五五|3|
小提琴：
http://sqlfiddle.com/#!17/c068c8/7

这可以通过使用窗口方法lag()来获得前一行，然后确定是否存在连续行来完成：

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