Error in query to Redshift

Question

I need to count the number of users in every month. I have written query.

SELECT COUNT(distinct user_id) 
from (
select TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') as MyMonth
from generate_series(1,60,1) AS s(a)
) months 
 LEFT OUTER JOIN database.users
ON months.MyMonth=to_char(created_at, 'YYYY-MM')
GROUP BY months.MyMonth

But when I try to execute it, the mistake appears: "Specified types or functions (one per INFO message) not supported on Redshift tables." Whether it is possible to rewrite somehow this query or specify please where a mistake?

Answer 1

The generate_series() command is not fully supported on Amazon Redshift.

It can be used for queries that run solely on the Leader node, but cannot be used in queries that involve tables because they involve cluster nodes. The only workaround is to create a large table with lots of values and join to it to get a range of numbers. (A bit of work up front, but then it works fine.)

You'll find many similar Questions on StackOverflow related to generate_series().