How to count by referring date in sql

Question

I have tables like following.

table

product   customer  surrender_date
A            a       2020/5/1
B            a       2020/6/1
C            b       2019/7/1
D            b       2020/8/1
E            b       2020/9/1

First I'd like to group by customer

product   customer  surrender_date
A            a       2020/5/1
B            a       2020/6/1

Second I'd like to rank by refferring to surrender_date from the newestone

My desired result is like following

product   customer  surrender_date  rank
A            a       2020/5/1        2
B            a       2020/6/1        1

Therefore My whole desired result is following.

product   customer  surrender_date  rank
A            a       2020/5/1        2
B            a       2020/6/1        1
C            b       2019/7/1        3
D            b       2020/8/1        2
E            b       2020/9/1        1

Are there any way to achieve this?

As I've never referred to date, If someone has opinion,please let me know.

score 2 · Accepted Answer · answered Aug 19 '20 at 08:41

You can use window functions:

select
    t.*,
    row_number() over(partition by customer order by surrender_date desc) rnk
from mytable

Notes:

I don't see what the question has to do with aggregation
depending on how you want to handle ties, you might be looking for rank() or dense_rank() instead of row_number()

How to count by referring date in sql

1 Answers1