I am getting an 'ERROR: Aggregate window functions with an ORDER BY clause require a frame clause' message when enterring the following query on Redshift. Please help - I am trying to view the growth of members from day 1 til today. Thanks.
select date(timestampregistered), count(distinct(memberid)),
(SUM(count(distinct(memberid))) OVER (ORDER BY date(timestampregistered)))
AS total_users
from table
order by date(timestampregistered);
You have a couple of things going on. First you seem to be missing a GROUP BY clause for the proper operation of COUNT() by date.
Next you need to specify the range of "counts" for which you want to SUM(). Specifically you want to sum counts for previous dates up to and including the current row's date but not later dates.
select date(timestampregistered), count(distinct(memberid)),
(SUM(count(distinct(memberid))) OVER (ORDER BY date(timestampregistered) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS total_users
from table
group by date(timestampregistered)
order by date(timestampregistered);