Windowing functions in recursive CTE

Today we will see an interesting case of incompatibility between MS SQL Server 2017 and PostgreSQL 9.6 (and different versions as well). Let’s start with this code:

We emulate a recursive CTE. We have two columns in source dataset, we want to sum first column for rows partitioned by second column. This gives a very expected result:

Now let’s use recursive CTE in MS SQL:

And result is:

However, PostgreSQL gives correct values:

Beware! Also, see this great post explaining row-based approach and set-based approach for implementing CTE.