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
;
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.