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