select
    c.user_id,
    max(c.cont_) as max_consec_days
from
(
select
    b.user_id,
    count(b.grp) cont_
from
(
select
    a.user_id,
    fdate,
    sum(lag_) over(partition by user_id  order by fdate) as grp
from
(
select
    user_id,
    fdate,
    case when date_sub(fdate,interval 1 day) = lag(fdate) over(partition by user_id order by fdate)  then 0 else 1 end as lag_
from
    tb_dau
where
    fdate between '2023-01-01' and '2023-01-31') a ) b
group by b.user_id,b.grp) c
group by c.user_id







# select
#     b.user_id,
#     max(b.cnt) as max_consec_days
# from
# (
# select
#     t.user_id,
#     date_sub(t.fdate,interval t.rank_ day) as a,
#     count(*) as cnt 
# from
# (
# select
#     fdate,
#     user_id,
#     row_number() over(partition by user_id order by fdate) as rank_
# from
#     tb_dau) t
# group by
#     t.user_id,a) b
# group by b.user_id

# # with recursive cte as(
# #     select
# #         fdate,
# #         user_id,
# #         1 as depth
# #     from
# #         tb_dau

# #     union all
    
# #     select
# #         t.fdate,
# #         t.user_id,
# #         depth + 1 as depth
# #     from
# #         tb_dau t
# #     join
# #         cte c on c.user_id  = t.user_id
# #     where
# #         date_add(c.fdate, interval 1 day) = t.fdate
# # )

# # select
# # user_id,
# # max(depth) as max_consec_days
# # from
# # cte
# # group by user_id