How to enumerate weeks over years in SQL/SNOWFLAKE

user_17669368 0
Viewed 93

I have been toying with making a data model housing a calendar in SQL.

What I am stuck right now is to have the weeks enumerated (as a way to order them for visualization purposes later on, like "easily coding pick last 4 weeks").

This has proven easy in Excel, but I want a way to build it from a query as the calendar is to be recreated each time the data model runs (create/replace the table).

What I have tried (but fails after the year changes) so far is this (I do not know how to pick the previous record from a field I am currently creating):

CASE WHEN week_of_year <> coalesce(lag(WEEKOFYEAR("DATE"),1) over (order by "DATE"),1) 
    THEN lag(WEEKOFYEAR("DATE"),1) over (order by "DATE") +1 
    ELSE lag(WEEKOFYEAR("DATE"),1) over (order by "DATE")

The desired GROUPED output is this (said grouped as the calendar should have data for each day):

YEAR WEEK_OF_YEAR week_order
2000 49 50
2000 50 51
2000 51 52
2000 52 53
2001 1 54
2001 1 55
2001 2 56
2001 3 57
dbt snowflake-cloud-data-platform sql
2 Answers

if you are want a value that increments over the years and weeks, across data that has values for all combinations then should work:

SELECT year, 
    week_of_year, 
    row_number() over (order by year, week_of_year) as week_order
user_439920

What about

row_number() over (order by YEAR, WEEK_OF_YEAR) as week_order
user_46919200
Related