generate_series equivalent in snowflake

Question

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.

Lukasz Szozda · Accepted Answer · 2022-03-29T17:32:10.233

1

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:

edited Mar 29 '22 at 17:32

answered Mar 29 '22 at 17:22

Lukasz Szozda

162,964
23
234
275

thanks @lukasz-szozda. i get an error when running that code. `SQL compilation error: syntax error line 3 at position 0 unexpected 'FROM'.` – Josh Fortunatus Mar 29 '22 at 17:29
@JoshFortunatus One paranthesis was not closed, it is fixed' – Lukasz Szozda Mar 29 '22 at 17:33
1

you're a legend. thank you so much. – Josh Fortunatus Mar 29 '22 at 17:47

generate_series equivalent in snowflake

1 Answers1

Linked