What's the most efficient way to calculate a rolling aggregate in SQL?

This is an interesting and pretty well-asked question. I would start by enumerating the days from the beginning of the first stay of each client until 90 days after the end of its last stay with a recursive cte. You can then bring the stay table with a left join, and use window functions to flag the "VIP" days (note that this assumes no overlaping stays for a given client, which is consistent with your sample data).

What follows is gaps-and-islands: you can use a window sum to put "adjacent" VIP days in groups, and then aggregate.

with cte as (
    select clientID, min(dateStart) dt, dateadd(day, 90, max(dateEnd)) dateMax
    from stays 
    group by clientID
    union all
    select clientID, dateadd(day, 1, dt), dateMax 
    from cte 
    where dt < dateMax
)
select clientID, min(dt) VIPStart, max(dt) VIPEnd
from (
    select t.*, sum(isNotVip) over(partition by clientID order by dt) grp
    from (
        select 
            c.clientID, 
            c.dt,
            case when count(s.clientID) over(
                partition by c.clientID 
                order by c.dt 
                rows between 90 preceding and current row
            ) >= 30
                then 0
                else 1
            end isNotVip
        from cte c
        left join stays s 
            on c.clientID = s.clientID and c.dt between s.dateStart and s.dateEnd
    ) t
) t
where isNotVip = 0
group by clientID, grp
order by clientID, VIPStart
option (maxrecursion 0)

This demo on DB Fiddle with your sample data produces:

clientID | VIPStart   | VIPEnd    
-------: | :--------- | :---------
       1 | 2020-01-30 | 2020-04-01
       1 | 2020-05-03 | 2020-07-04
       2 | 2020-02-01 | 2020-04-28
       3 | 2020-02-07 | 2020-04-20

You can put this in a view as follows:

• the order by and option(maxrecursion) clauses must be omitted when creating the view

• each and every query that has the view in its from clause must end with option(max recursion 0)

Demo

You can eliminate the recursion by creating a tally table in the view. The approach is then the following:

• For each period, generate dates from 90 days before the period to 90 days after. These are all the "candidate days" that the period could affect.
• For each row, add a flag as to whether it is in the period (as opposed to the 90 days before and after).
• Aggregate by client id and date.
• Use a running sum to get the days with 30+ in the previous 90 days.
• Then filter for the ones with 30+ days and treat this as a gaps-and-islands problem.

Assuming that 1000 days is sufficient for the periods (including the 90 days before and after), then the query looks like this:

with n as (
      select v.n
      from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
     ),
     nums as (
      select (n1.n * 100 + n2.n * 10 + n3.n) as n
      from n n1 cross join n n2 cross join n n3
     ),
     running90 as (
      select clientid, dte, sum(in_period) over (partition by clientid order by dte rows between 89 preceding and current row) as running_90
      from (select t.clientid, dateadd(day, n.n - 90, datestart) as dte,
                   max(case when dateadd(day, n.n - 90, datestart) >= datestart and dateadd(day, n.n - 90, datestart) <= t.dateend then 1 else 0 end) as in_period
            from t join
                 nums n
                 on dateadd(day, n.n - 90, datestart) <= dateadd(day, 90, dateend)
            group by t.clientid, dateadd(day, n.n - 90, datestart)
           ) t
     )
select clientid, min(dte), max(dte)
from (select r.*,
             row_number() over (partition by clientid order by dte) as seqnum
      from running90 r
      where running_90 >= 30
     ) r
group by clientid, dateadd(day, - seqnum, dte);

Having no recursive CTE (although one could be used for n), this is not subject to the maxrecursion issue.

Here is a db<>fiddle.

The results are slightly different from your results. This is probably due to some slight difference in the definitions. The above includes the end day as an "occupied" day. The 90 days is 89 days before plus the current day in the above query. The second-to-last query shows the 90 days running days, and that seems correct to me.