Partition using multiple fields and get the same grouping number in each partition

Question

I have a dataset that looks like this:

Car type	date	car
Honda	May 28, 2022	Car 1
Honda	May 28, 2022	Car 1
Honda	August 11, 2022	Car 2
Honda	August 11, 2022	Car 2
BMW	May 28, 2022	Car 1
BMW	May 28, 2022	Car 1
BMW	August 11, 2022	Car 2
BMW	August 11, 2022	Car 2

I want to create an additional column "expected" using the row_number(), rank() functions like this:

Car type	date	car	expected
Honda	May 28, 2022	Car 1	1
Honda	May 28, 2022	Car 1	1
Honda	August 11, 2022	Car 2	2
Honda	August 11, 2022	Car 2	2
BMW	May 28, 2022	Car 1	1
BMW	May 28, 2022	Car 1	1
BMW	August 11, 2022	Car 2	2
BMW	August 11, 2022	Car 2	2

I tried the following query. But it doesn't seem to be giving me the correct results

Select car type, date, car, ROW_NUMBER() OVER (PARTITION BY CarType, Car ORDER BY Date ASC) AS RW from table

Answer 1

SELECT CarType, 
       Date, 
       Car, 
       DENSE_RANK() OVER (PARTITION BY CarType 
                          ORDER BY Car) AS RW 
from table