SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
    user_id	INT,
    created_at	TIMESTAMP
);

✓
INSERT INTO mytable (user_id, created_at) VALUES
('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');
✓
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


user_id
longest_period


123
4

555
3