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

Question

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!

Your description is not clear. Please show the results you want and explain the logic *explicitly* ("result of two counts" is. vague). Also tag with the database you are using. — Gordon Linoff, Dec 06 '20 at 12:34

Gordon Linoff · Accepted Answer · 2020-12-06T12:41:30.620

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

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

1 Answers1