Weighted moving average with partitions in SQL

Question

I have a simple table with sales of different products by date (YYYYMMDD). There are many days and many products. The table looks like this:

date      product     sales
...
20210101  Apples       112
20210101  Bananas      13
20210102  Apples       12
20210102  Bananas      101
20210103  Apples       18
20210103  Bananas      155
20210101  Lemons       14
... 

I need to calculate a 3-day weighted moving average for each of the products. The main tricky thing is with partition. I am trying to squeeze it in this code:

    SELECT table.date,
       max(table.sales) as sales,
       CASE 
       WHEN table.date - (select min(date) from table) >1
       THEN sum(weighting * ma_table.sales)
       END as WMA 
FROM table
JOIN table AS ma_table ON table.date - ma_table.date BETWEEN 0 AND 2
JOIN (SELECT id_1, 1/(2 + 1.) as weighting FROM (VALUES (0, 1, 2)) as t(id_1)) weights ON
  id_1 = table.date - ma_table.date
GROUP BY table.date
ORDER BY table.date

Is there anything that can be done with this code to include partition there?

Answer 1

The following is SQL to calculate 3-days moving average for each product, using window function.
Is this what you want?

with
a as ( -- serial numbers upto N (=3)
    select row_number() over () as i
    from TBL
    limit 3
),
b as (
  select
      TBL.date, TBL.product, TBL.sales,
      lag("sales", cast(a.i - 1 as integer)) 
        over(partition by TBL.product, a.i order by date) as sales2,
      (3 - a.i + 1) as weight
  from TBL
  cross join a
)
select date, product, sales,
    sum(weight*sales2) / (3*(3+1)/2) as avg
from b
group by date, product, sales
order by product, date

DEMO