SQL+Fetch rows with latest date+Case when row_number()+Duplicates

I have a table with fields(id, date, product) with below sample data

id  date    Product current_Flag    Expected_flag
14834   2019-01-03 00:00:00 A   1   1
14834   2019-01-31 00:00:00 B   0   0
14834   2019-02-28 00:00:00 C   0   0
14834   2019-03-30 00:00:00 C   0   0
14834   2019-01-03 00:00:00 D   0   1

I’m trying the case when row_number() over (partition by id order by date) = 1 then 1 else 0 end as flag, But for a particular distinct Id with min date, it is getting flag as 1, but I need to flag as for a particular Id & min date and product as 1 flag as shown in Expected_flag

case when row_number() over (partition by id order by date) = 1 then 1 else 0 end as flag

Answer

You need dense_rank() instead :

case when dense_rank() over (partition by id order by date) = 1 then 1 else 0 end as Expected_flag