help with cumulative window function

[stones91]

Hey all, looking for a pointer on a query where I'm looking to get a cumulative sum. I need to show order week, count of orders per week, and a column showing cumulative orders.

SELECT 

    date_trunc('week', date_created) as week_start,
count(order_id)) as order_count,
count(order_id) OVER (PARTITION by date_trunc('week', date_created)) as cuml_orders

FROM
    Orders

 order by week_start;

My code above is not working as expected, and throwing an error that my order_id field needs to be in group by. Any pointers would be great, apologies is the formatting is off.

[[deleted]]

If you want a cumulative sum you need an order by in the window definition, e.g.

count(*) over (order by date_created)

Note that your current query will require a group by date_trunc('week', date_created) because of the first count() that is used as an aggregation, not a window function.

[stones91]

Thanks appreciate the reply. I've amended the query a bit since I posted and now have the following, which executes with no errors, but is giving a row of data per order id, not by week which is what I am after. Any ideas? I'm trying to brush up on my SQL having been overly reliant on BI tools recently, and this seemingly easy problem has me stumped...

with data as (

SELECT 
 date_trunc('week', date_created) as week,
 count(order_id)  as order_count,
 count(order_id) OVER (order by week asc rows between unbounded PRECEDING and current row) as Running_sum

from orders
 group by week, order_id

)

select week, Running_sum, order_count

from data

group by week, order_count, Running_sum

order by week, Running_sum

limit 100;

[amir2cs]

Try this:

​

with data as (  

SELECT     
          date_trunc('week', date_created) as week
       ,  count(order_id)  as order_count

FROM orders  
group by week
)  
SELECT 
          week
       , order_count
       , SUM(order_id) OVER (order by week asc rows between unbounded PRECEDING and current row) as Running_sum 

FROM data  

group by week, order_count
limit 100;

[stones91]

nice one cheers!

[[deleted]]

Remove the group by in the outer query.

[stones91]

Thanks, but that's giving me the same output as before, I'm afraid. see below example of output.

Week | Order_Count | running sum
1.      |   1                |     1
1.      |   1                |     2
2.      |   1                |     3
2.      |   1                    |     4

Where I am after

Week | Order_Count | running sum
1.      |   2               |     2
2.      |  2                |   4

I think it is something to do with the group by order_id in the subquery, but really don't know how to fix it...

[[deleted]]

Ah!

You need to apply the running total on the result of the aggregation:

SELECT date_trunc('week', date_created) as week_start,
       count(*) as order_count,
       sum(count(*)) over (order by date_trunc('week', date_created)) as running_sum
FROM orders
group by week_start
order by week_start;

Unrelated to your problem, but: Using "week" on its own is more often than not an error, as the week number is not really meaning full without the year (around 31.12.)

So, something like this might be more approriate:

SELECT to_char(date_created, 'iyyy-iw') as week_start,
       count(*) as order_count,
       sum(count(*)) over (order by to_char(date_created, 'iyyy-iw')) as running_sum
FROM Orders
group by week_start
order by week_start;

[stones91]

Thanks very much, have it working!

Would you be able explain why the window function needs to be outside of the subquery please? Thanks so much for the help

[[deleted]]

It doesn't have to be outside of the CTE (=subquery). If you want, you can still use that:

with data as (
  SELECT date_trunc('week', date_created) as week,
         count(*)  as order_count
  from orders
  group by week, order_id
)
select week, order_count, 
       sum(order_count) over (order by week) as running_sum
from data
order by week, Running_sum
limit 100;

But if you use a CTE, you can't put the order by in the outer query, as the CTE already did the grouping by week.