Cumulative count not converting correctly into SQL

1

Hi,

when I create a cumulative count plot via the user interface all works well. However, once I convert it into SQL the behaviour changes, and the table returned doesn’t have the cumultive count over time but rather the count (say of new users) week by week.

Is this a known issue?

Thanks in advance.

2

Hi,
it looks like this issue is known already and it seems there is no workaround:

cheers, Eva

1 Like
3

Thanks Eva

4

It is not so hard to write it in SQL!
here is an example:

select
convert(varchar(10), start_date, 105) as day,
count(1)
from sessions
group by convert(varchar(10), start_date, 105);

 day    | count

------------+-------
02-02-2020 | 3
03-02-2020 | 3
04-02-2020 | 4

Next, we'll write a [SQL Server common table expression (CTE)] and use a window function to keep track of the cumulative sum/running total:

with data as (
select
convert(varchar(10), start_date, 105) as day,
count(1) as number_of_sessions
from sessions
group by convert(varchar(10), start_date, 105)
)

select
day,
sum(number_of_sessions) over (order by day asc rows between unbounded preceding and current row)
from data;

 day    | sum

------------+-------
02-02-2020 | 3
03-02-2020 | 6
04-02-2020 | 10

ref: https://popsql.com/learn-sql/sql-server/how-to-calculate-cumulative-sum-running-total-in-sql-server