using nested query for 'IN' 'clause oracle with PIVOT

Question

I am trying to come up with code that would produce values for each product on weekly basis. I'm using pivot clause but when I try to set up time parameters it wants me to name the week individualy which I would rather do using nested query.

Code

    SELECT *
  FROM (select T.product_owner,
               T.product_name,
               T.product_id,
               T.interest_rate,
               week
          from table t
         where 1 = 1
           and T.WEEK between '2022/47' AND '2023/11')
pivot(avg(interest_rate)
   for week in(select week from table_date where week between '2022/47' and '2023/11'))
 order by product_id

The error I get is 00936 - missing expression.

I even tried, just to make sure, this alternative, but it seems it literally resists any sort of query in the in clause

    SELECT *
  FROM (select T.product_owner,
               T.product_name,
               T.product_id,
               T.interest_rate,
               week
          from table t
         where 1 = 1
           and T.WEEK between '2022/47' AND '2023/11')
pivot(avg(interest_rate)
   for week in(select '2023/01' week from dual))
 order by product_id

Structure of source table:

week	product_owner	product_name	product_id	interest_rate
2022/47	po1	pn1	111111	0.37
2023/06	po2	pn2	22222	0.11

target table would be

product_owner	product_name	product_id	2023/02	2023/01	...
po1	pn1	111111	0.37	0.31	...
po2	pn2	22222	0.11	0.15	...