Search code examples
sqlsnowflake-cloud-data-platformwindow-functions

window function identifying the 1st, 2nd, etc. day of week of the month

I have a utility calendar table which looks like this:

enter image description here

DATE DAY_OF_WEEK DAY_OF_MONTH MONTH YEAR
2023-01-01 Sunday 1 1 2023
2023-01-02 Monday 2 1 2023
2023-01-03 Tuesday 3 1 2023
2023-01-04 Wednesday 4 1 2023
2023-01-05 Thursday 5 1 2023
2023-01-06 Friday 6 1 2023
2023-01-07 Saturday 7 1 2023
2023-01-08 Sunday 8 1 2023
2023-01-09 Monday 9 1 2023
2023-01-10 Tuesday 10 1 2023
2023-01-11 Wednesday 11 1 2023
2023-01-12 Thursday 12 1 2023
2023-01-13 Friday 13 1 2023
2023-01-14 Saturday 14 1 2023
2023-01-15 Sunday 15 1 2023

I am trying to add a column which signifies the first weekday of its kind in a month. In this column, rows 1-7 should say 1, rows 8-14 should say 2, and so on until February 2023, and the pattern resets.

It seems to me that a window function is necessary here, however RANK() just counts the days of the month when partitioned by month, or just counts the days in the week when partitioned by an extracted field which is the week of the year. I don't see how the other window functions could be used to generate the correct results, either.

Solution

  • Try to use the dense_rank function as the following:

    select *,
  dense_rank() over (partition by year, month, day_of_week order by date) as rnk
from table_name
order by date

    Or use ((day_of_month-1) /7)+1. (based on a comment from @NickW)

    demo