info.md

Goal

Dynamically generate list of consecutive dates.

Usecase

List of dates can be joined to sparse time series data. Having generated dates (fully covered your MIN(orderDate) TO MAX(orderDate) interval) left joined with (i.e.) Orders produce backfilled dates.

Strategy

This how-to utilise Snowflake Sequences (https://docs.snowflake.net/manuals/user-guide/querying-sequences.html). Idea is pretty simple: generate consecutive list of numbers, join (cartesian matrix) it with one date and based on result (two columns: list of numbers and list of one date value), apply DATE operation adding or subtracting specific number from fixed data. As a result, you'll get list of consecutive dates.

Notes

• fixed number ("100") CAN NOT BE replaced by SQL (i.e. SELECT COUNT(orders) FROM t1) - it has to be a number
• fixes date CAN BE replaced by SQL (i.e. SELECT MAX(date) FROM t1) - in that case, dates are generated from the last (youngest) order

snowflake_date_generator.sql

/* ================================ EXAMPLE #1 =================================
    Generate list of 100 consecutive dates (to the FUTURE), beginning by fixed date (2017-12-31)
    (initiate date IS part of generated dates)
*/
SELECT                  
    DATEADD(                                
            "DAY",                                  /* simple DATE math */
            "seq"."seq",                            /* adding generated numbers from fixed date */
            "OLDDAY"
            ) :: DATE AS "GENDATE"
FROM (
        SELECT '2017-12-31' AS "OLDDAY"             /* FIXED DATE */
     ) "t1"
LEFT JOIN (                                         
            SELECT seq2() AS "seq"                  /* SUBQUERY #1 */
            FROM TABLE(generator(rowcount => 100))  /* - produce 100 rows table with {0:99} numbers */
          ) "seq"                                   
ORDER BY 1;

/* ================================ EXAMPLE #2 =================================
    Generate list of 100 consecutive dates (to the FUTURE), beginning by fixed date (2017-12-31)
    (initiate date IS NOT part of generated dates)
*/
SELECT 
    DATEADD(
            "DAY", 
            "seq"."seq", 
            "OLDDAY"
            ) :: DATE AS "GENDATE"
FROM (
        SELECT '2017-12-31' AS "OLDDAY"
     ) "t1"
LEFT JOIN (
            SELECT seq2() + 1 AS "seq"
            FROM TABLE(generator(rowcount => 100))
          ) "seq"
ORDER BY 1;

/* ================================ EXAMPLE #3 =================================    
    Generate list of 100 consecutive dates (to the PAST), beginning by fixed date (2017-12-31)
    (initiate date IS NOT part of generated dates)
*/
SELECT 
    DATEADD(
            "DAY", 
            -"seq"."seq", 
            "OLDDAY"
            ) :: DATE AS "GENDATE"
FROM (
        SELECT '2017-12-31' AS "OLDDAY"
     ) "t1"
LEFT JOIN (
            SELECT seq2() + 1 AS "seq"
            FROM TABLE(generator(rowcount => 100))
          ) "seq"
ORDER BY 1;

@padak I believe it's better to use seq8 function in the example, as it works with generating "any" number of rows.