Ranking in SQL Server with a column change

Below is the original table that I want to rank according to the column changes by Install_Status and Dates for each product.

Query:

;WITH CTE AS 
(
   SELECT  
       Product, Install_Status, Date_Inserted,
       DENSE_RANK() OVER (PARTITION BY Product ORDER BY rank) rank
   FROM
       (SELECT 
            Product, Install_Status, Date_Inserted,
            ROW_NUMBER() OVER (ORDER BY Product, Date_Inserted) -
               ROW_NUMBER() OVER (PARTITION BY Product, Install_Status ORDER BY Product, Date_Inserted ASC) AS rank
        FROM 
            [dbo].[Product_table]) t
)
SELECT * 
FROM CTE
ORDER BY Product, Date_Inserted;

This SQL query returns the following results:

However, I am not getting appropriate ranking for product A by date. My expected result is as below:

Answer

You seem to want to count the changes. For this use lag() and a cumulative sum:

select t.*,
       sum(case when prev_install_status = install_status then 0 else 1 end) over
           (partition by product order by date_inserted) as rank
from (select t.*,
             lag(install_status) over (partition by product order by date_inserted) as prev_install_status
      from t
     ) t;