select e.ds,count(*) as new_uv,

count(case when num_days>1 then 1 else null) as retain_1,

count(case when num_days>7 then 1 else null) as retain_7

from (select * from a where ds between ‘2023-05-01’ and ‘2023-05-31’) e left join

      from (select *,date_sub(ds,interval rn-1 day) as results

               from (

                         select uid,ds,row_number() over(partition by uid order by ds) as rn

                         from (

                                  select distinct uid,ds

                                  from b

                                  where uid in (select uid from a where ds between ‘2023-05-01’ and ‘2023-05-31’)) t1) t2) t3

        group by uid, results) t4

on e.uid=t4.uid and e.ds=t4.results

group by e.ds

58:18