Creating a row number column based on another columns value

I have the following data:

it’s a calendar that has date and year/week number there week is our internal calendar. what i want to do is sort by the most recent week number and have that be rel_week_index=1, and then the previous week be rel_week_index=2, etc. I got this to work by doing ROW_NUMBER() OVER(PARTITION BY YEAR ORDER BY YEAR DESC), however, once you get to the previous year, this rel_week_index column starts over at 1, which isn’t what i want.

Ideally year_week=202111 would be rel_week_index=1, year_week=202110 would be rel_week_index=2 and so on.

Answer

If I understood you correctly this is the query to calculate row number based on year and week combination in descending order:

ROW_NUMBER() OVER( ORDER BY YEAR_week DESC)