CODEWITHSUNDEEP
sqlrecursioncommon-table-expressionsnowflake-cloud-data-platform
Josh
Josh

Reputation: 1305

Create Running Sum in SQL

I am trying to calculate a running sum of one column partitioning with respect to another column, using SnowFlake SQL. Here is an example input table:

ID_1    DATE_1      FLAG
A      10/12/2016   1
A      10/31/2016   1
A      11/8/2016    1
B      2/1/2017     1
B      3/27/2017    1

I want to compute a running sum between FLAG and DATE_1 (in ascending order), partitioning over ID_1. Thus, the result in this example should be:

ID_1    DATE_1      FLAG_RUNNING_SUM
A      10/12/2016   1
A      10/31/2016   2
A      11/8/2016    3
B      2/1/2017     1
B      3/27/2017    2

I tried to do recursive Common Table Expressions (CTEs) for this (see here: https://docs.snowflake.net/manuals/sql-reference/constructs/with.html) but Snowflake does not support window functions in recursive CTEs, as per that document.

Does anyone know how I can do this, ideally cleanly?

I suspect that recursive joins/CTEs are somehow the answer, but I am open to non-recursive solutions as well. Whatever works.

Upvotes: 2

Views: 305

Answers (2)

MKP
MKP

Reputation: 196

You can use below query in snowflake as well:

select t.*,
        rank()  over (partition by id_1 order by date_1) as FLAG_RUNNING_SUM
from t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270051

Just use a window fucntion:

select t.*,
       sum(flag) over (partition by id_1 order by date_1) as FLAG_RUNNING_SUM
from t;

Upvotes: 1

Related Questions