select customer_id, row_number()over(partition by customer id order by date) as rn from table

How to get same rn when Item Id is the same?

Below did not work: #1 select customer_id, row_number()over(partition by customer id, Item Id order by date) as rn from table

CodePudding user response：

We can try to use DENSE_RANK instead of row_number window function

If the optional PARTITION BY clause is present, the rankings are reset for each group of rows. Rows with equal values for the ranking criteria receive the same rank

select customer_id, DENSE_RANK() over(partition by customer id order by date) as rn 
from table