Search code examples
sqlanalytic-functions

How to combine an arithmetic sum with an analytic function in SQL for a running

I'm trying to calculate a running % from the result of two counts (as shown in the SQL excerpt below) from a table in normal SQL that looks like this:

Table Database

My thoughts are this:

    SELECT week(beginning),
(select count(enquired_at) from TABLE) / (select count(*) from TABLE) * 100 OVER (partition by beginning order by beginning desc)

I would like a result set that looks like this:

Result Set

How would a person take this arithmetic calculation and make it so that it calculates cumulatively from Week 1 of beginning onwards?

Thanks!

Solution

  • Based on your query, you seem to want a running count of the number of times enquired_at is not null. If so:

    select t.*,
       ( count(enquired_at) over (order by beginning) * 1.0 /
         count(*) over (order by beginning)
       ) as ratio
from t;

    Or more simply as:

    select t.*,
       avg(case when enquired_at is not null then 1.0 else 0 end) over (order by beginning) as ratio
from t

    EDIT:

    You can put this into an aggregation query:

    select week(beginning), 
       ( sum(count(enquired_at)) over (order by min(beginning)) * 1.0 /
         sum(count(*)) over (order by min(beginning))
       ) as ratio
from t
group by week(beginning);