0

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
;
Omega
  • 750
  • 1
  • 8
  • 24

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This works in theory, just not in my case because I have to work with virtual schemas which don't support division of two window functions. Is there a workaround by any chance? – Omega May 12 '21 at 12:50
  • @Omega . . . Can you use a subquery and divide in the outer query? – Gordon Linoff May 12 '21 at 13:06