Generate Series Alternative for Snowflake works but generates an incomplete list-CodePudding

I am looking into creating a list, where I see how many IDs were generated in a given hour. Because I want to display the hours from 8 to 21 in advance independent of the hour of the date, I used generate series and now need to find the equivalent for Snowfake. This is my query:

series as  (
    SELECT seq4() as Hour 
    FROM TABLE(GENERATOR(rowcount => 21)) 
    where Hour between 7 and 20 
    ORDER BY Hour), 
ID_table as (
    select extract(hour from date) as "Hour", 
    count(ID) as "Count" 
    from ID_table 
    group by 1) 
 select (Hour.Hour)   1 AS "Hour",
 id."Count", 
 from series as Hour
 left join ID_table as id on id."Hour" = Hour.Hour 
 order by Hour.Hour;

For some reason I only get the Hours 8 to 16, however, I want it to display the hours 8-21, what could be the issue?

CodePudding user response：

You should always consider that SEQ() functions do not guarantee gaps, so for generating the range, I suggest you to use ROW_NUMBER() function:

https://community.snowflake.com/s/article/Generate-gap-free-sequences-of-numbers-and-dates

Anyway, when I test it, I see it returns expected numbers:

SELECT seq4() as Hour FROM TABLE(GENERATOR(rowcount => 21));
-- returnns numbers from 0 to 20

SELECT seq4() as Hour FROM TABLE(GENERATOR(rowcount => 21)) where Hour between 7 and 20 order by hour;
-- returnns numbers from 7 to 20

with series as (SELECT seq4() as Hour FROM TABLE(GENERATOR(rowcount => 21)) where Hour between 7 and 20 ORDER BY Hour)
select (Hour.Hour)   1 AS "Hour"
from series as Hour;
-- returnns numbers from 8 to 21

Could it be something with the browser/UI?