Cumulative total by week - postgresql

Question

Using postgresql, version 9.5.8

Below I've got a working query, which gives me the pct of ready accounts of all accounts. This table is then split by week, giving me the amount of accounts created that week, which are subsequently ready.

Query below:

SELECT 
               date_trunc('week', al.created_at) as week_created,
               count(case when ra.status='ready' then 1 end) as total_accounts,
               count(case when ra.status='ready' AND ra.tests > 0 then 1 end) as accounts_ready,
               concat(round(count(case when ra.status='ready' AND ra.tests > 0 then 1 end) :: decimal /count(case when ra.status='ready' then 1 end) :: decimal * 100.0), '%') as pct_accounts_ready

    FROM "ready_accounts" ra
    JOIN "accounts_list" al
    ON al.id=ra.id
    GROUP BY week_created
    ORDER BY week_created DESC;

Results set looks like so:

Week Created --------- Total Account ---- Accounts Ready ---- Pct Accounts Ready

Monday 14 Aug ----  50 ----------------39 ---------------- 78%
Monday 7 Aug  ----  20 ----------------10 ---------------- 20%

The trouble is, the results I'm getting aren't cumulative, they're just for the week, which is meaningless for what I want to achieve.

I would like a result set which shows instead:

Monday 14 Aug ---  70 ------------------- 49 ---------------- 70%
Monday 7 Aug  ---  20 ------------------- 10 ---------------- 20%

Sample Input Data:

Sample data would look like so: ready accounts table:

ra.id   ra.status   ra.tests
123     ready       1
124     not_ready   2
125     not_ready   0
126     ready       1
127     ready       0
128     ready       0
129     ready       1

accounts list table:

al.id   al.created_at

123     Monday 14 August
124     Monday 7 August
125     Monday 14 August
126     Monday 14 August
127     Monday 7 August
128     Monday 14 August
129     Monday 31 July

I've tried a number of solutions but am getting stuck. Any examples of solutions would be really helpful!

Thank you in advance. I'm pretty new to this, so any explanation would be useful!

Answer 1

Use your query without the last column in a derived table (a subquery in FROM clause) and use sum() as a window function. Calculate the percentages in an outer wrapper query:

select 
    week_created,
    total_accounts,
    accounts_ready,
    concat((accounts_ready/ total_accounts* 100)::int, '%') as pct_accounts_ready
from (
    select
        week_created,
        sum(total_accounts) over w as total_accounts,
        sum(accounts_ready) over w as accounts_ready
    from (
        select 
            date_trunc('week', al.created_at) as week_created,
            count(case when ra.status='ready' then 1 end) as total_accounts,
            count(case when ra.status='ready' and ra.tests > 0 then 1 end) as accounts_ready
        from "ready_accounts" ra
        join "accounts_list" al
        on al.id=ra.id
        group by week_created
        ) s
    window w as (order by week_created)
    ) s
order by week_created desc;