SQL calculate percentage between two columns

Question

I would like to calculate the percentage between opened and delivered items by month. I have the following table:

     date       | delivered | opened
  01/04/2021           1        1
  01/04/2021           1
  01/04/2021           1
  08/05/2021           1        1
  08/05/2021           1        1
  10/03/2021           1
  10/03/2021           1        1

The percentage would then be added like this:

 date_month | delivered | opened | percentage_opened
  4             1          1            0.33
  4             1                       0.33
  4             1                       0.33
  5             1          1            1
  5             1          1            1
  3             1                       0.5
  3             1          1            0.5

I have tried the following, but get an error reading 'Internal error: system tried to run table creation for virtual table'.

select
    opened, 
    delivered,
    month(date) as date_month,
    sum(opened)/sum(delivered) over(partition by month(date)) as percentage_opened
from table
;

Answer 1

You are close but you need two analytic functions. You should also include the year:

select opened,  delivered, month(date) as date_month,
       (sum(opened) over (partition by year(date), month(date)) * 1.0 /
        sum(delivered) over(partition by year(date), month(date))
       ) as ratio_opened
from table;

Some databases do integer division, so I threw in * 1.0 just in case yours does.