recursive moving average with sql

supose we have the next table: table example

and what i need is:

• frst iteration: calculate the moving average 5 days before the last day including the last day = (2+1+2+3+4)/5 = 2.4 and "save" this result, that result will be a prediction for the next day.

• scnd iteration: calculate the moving average 5 days before the last, day where the last day basal cell is the value calculated in the previous iteration. (1+2+3+4+2.4)/5 = 2.48
  ..

and so on.. the recursion will stop for a concrete future day for example: 2022-12-9

deseable output for future day: 2022-12-9

| date_       | art_id     | basal_sell |
| ------------| -----------|------------|
| 2022-12-01  | 1          | 2          |
| 2022-12-02  | 1          | 1          |
| 2022-12-03  | 1          | 2          |
| 2022-12-04  | 1          | 3          |
| 2022-12-05  | 1          | 4          |
| 2022-12-06  | 1          | 2.4        |
| 2022-12-07  | 1          | 2.48       |
| 2022-12-08  | 1          | 2.776      |
| 2022-12-09  | 1          | 2.9312     |

this is the partial problem, in the real problem will be a bunch of arts_ids but i think the idea for this parcial problem will be the solution for the big problem (with some little changes).

• what i think:

I thought a recursive cte where in the recursive part of the cte i have a union that will be union the temporary table with the new row that i calculated.

Something like:

with MiCte as (
  select * 
  from sells

  union all
  (
    select * from MiCte 
  )
  union
  (
    select dateadd(day, 1, date_), art_id, basal_sell
    from(
        select top 1 c.date_, c.art_id,
        AVG(c.basal_sell) OVER (partition by c.art_id
                              ORDER BY c.date_
                              rows BETWEEN 4 PRECEDING AND current row) basal_sell
        from MiCte c
        order by c.date_ desc
        ) as tmp
  )
) select * from MiCte

Obviously if I contemplate having more than one art_id I have to take this into account when making top 1 (which I still couldn't think of how to solve).

• the example table:

CREATE TABLE sells
    (date_ DATETIME,
     art_id int, 
     basal_sell int)
;

INSERT INTO sells
    (date_, art_id , basal_sell)
VALUES ('2022-12-1', 1, 2),
       ('2022-12-2', 1, 1),
       ('2022-12-3', 1, 2),
       ('2022-12-4', 1, 3),
       ('2022-12-5', 1, 4);