generate_series equivalent in snowflake

March 29, 2022

I’m used to Postgres and when calculating MRR and ARR I would use some version of this:

date_trunc('day',transaction_date) + interval '1' month * generate_series(0,11)

I’ve been having trouble finding analogous syntax in Snowflake. Most of what I’m seeing something akin to a date_dimensions table but I need to be able to create virtual billing dates each month and apply some fraction of transaction to each date.

For example, if there’s a $1,000 annual subscription purchase made on 3/29, then I would create billing dates for the next 12 months (4/29,5/29,6/29 etc.) with a corresponding 1000/12 value.

I’ve seen documentation on generator and seq4() but having a hard time applying it to what I need.

Thank you.

>Solution :

Using GENERATOR and DATEADD:

SELECT DATEADD(MONTH, (ROW_NUMBER() OVER(ORDER BY seq8())-1),
               DATE_TRUNC('day',transaction_date))
FROM tab
,TABLE(GENERATOR(ROWCOUNT => 12));

Output: