[SQL] Snowflake: SQL DENSE_RANK() not working as expected. Would there be an alternative?

I am trying to rank my table based on sku and rolling_inbound but there are quite a few other columns in the table which I believe is why I'm not seeing what I need.

For example:- When I run the code:

SELECT
week
sku,
units_sold,
inbound,
rolling_inbound,
DENSE_RANK() OVER(PARTITION BY sku, rolling_inbound ORDER BY week) AS sku_rank
FROM table



This is the result I am getting:

week sku units_sold inbound rolling_inbound sku_rank
2024-01-01 a 10 30 30 1
2024-01-07 a 5 0 30 2
2024-01-14 a 7 0 30 3
2024-01-21 a 5 15 15 4
2024-01-28 a 3 0 15 5

But what I am trying to get is the following:

week sku units_sold inbound rolling_inbound sku_rank
2024-01-01 a 10 30 30 1
2024-01-07 a 5 0 30 1
2024-01-14 a 7 0 30 1
2024-01-21 a 5 15 15 2
2024-01-28 a 3 0 15 2

I'm not entirely sure why this is happening, I think it is because the different non-unique rows.

Would there be a solution for this ? or an alternative that will allow me to rank as needed ?

Any help would be much appreciated. Thanks

Continue reading...