Your source data is ambiguous when you have date-only columns with duplicate dates for the same account ('2024-07-12', 'Acc1', 'ABB') and ('2024-07-12', 'Acc1', 'ABA'). This could be interpreted either as ABA -> ABB -> ABA -> ABB or ABA -> ABA -> ABB -> ABB. SQL Server does not have an implicit row order other than that which is explicitly defined.

If we ignore that ambiguity, your question is a variation of a gaps-and-islands problem. In this case, the gaps are defined as changes to the Product value for a given AccountID across ascending EffectiveDate values. These can be identified using the LAG() fonction to compare product values to identify changes. For most gaps-and-islands problems, we would then group and combine the rows forming each island between the gaps, but in this case, we can just ignore the rows where Product does not change.

It then becomes a matter of using the LEAD() function to look ahead at the remaining rows to fill in the ProductTo and ToDate values, and a windowed COUNT(*) to get the desired TotalNumberOfSwitchesAtTimeOfSwitch values.

The resulting query would be something like:

WITH Changes AS (
    SELECT *
    FROM (
        SELECT
            *,
            LAG(Product)
                OVER(PARTITION BY AccountID ORDER BY EffectiveDate)
                AS LagProduct
        FROM AccountBalanceAndProduct
    ) A
    WHERE LagProduct IS NULL OR LagProduct <> Product
)
SELECT
    AccountID,
    Product AS ProductFrom,
    EffectiveDate AS FromDate,
    LEAD(Product)
        OVER(PARTITION BY AccountID ORDER BY EffectiveDate)
        AS ProductTo,
    LEAD(EffectiveDate)
        OVER(PARTITION BY AccountID ORDER BY EffectiveDate)
        AS ToDate,
    COUNT(*)
        OVER(PARTITION BY AccountID ORDER BY EffectiveDate
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS TotalNumberOfSwitchesAtTimeOfSwitch
FROM Changes
ORDER BY AccountID, EffectiveDate

Results (after excluding the ambiguous 2024-07-12 row):

Note that the above also includes the final change with starting but no ending values. It also has several other differences from your posted expected results, but I believe the above accurately reflects the input data. Please review your expected results. If the above is not what you are looking for, please expand on your description of your intended logic.

See this db<>fiddle for a demo.

Only a partial answer, but it should point you in the right direction.

What I would do is use LAG() instead of Row_Number() to define the window, so that I can filter for rows where the function result does not match the current value:

WITH PriorProducts As
(
    SELECT ID, EffectiveDate, AccountID, Product,
        LAG(Product) OVER (PARTITION BY AccountID ORDER BY EffectiveDate, ID) ProductFrom,
        LAG(EffectiveDate) OVER (PARTITION BY AccountID ORDER BY EffectiveDate, ID) PriorProductEndDate
    FROM AccountBalanceAndProduct
)
SELECT ID, AccountID
    , Product ProductTo
    , EffectiveDate DateStarted
    , ProductFrom
    , PriorProductEndDate
FROM PriorProducts
WHERE ProductFrom <> Product
    AND ProductFrom IS NOT NULL

This isn’t a perfect set of what you want (it doesn’t handle the full dates of set… rather it shows rows for the boundaries). But it should get you there a little easier.

Otherwise, what you’re doing is called gaps and islands, and there’s lots of help online for ways to do this.

This is near to approach, shown by @TN.

I would like to consider in more detail the case when there are two rows on the same date. In this case, let’s try to minimize the number of transitions from product to product. If one of the lines of this day matches the value in the previous day, put this line first in the list. See rows with id 3 and 4.

For rows (4,Acc1,2024-07-12,ABA) and (3,Acc1,2024-07-12,ABB) we take first row (4,Acc1,2024-07-12,ABA), because previous row is with product ABA

See example

with intraRange as(
  select * 
    ,lag(Product,1+rn-rnk,Product)over(partition by AccountId order by EffectiveDate,Id) prevProduct0
    ,case when lag(Product,1+rn-rnk,Product)
          over(partition by AccountId order by EffectiveDate,Id) =Product 
     then 0 
     else 1 
     end intraNum
  from (
    select ID,AccountID, EffectiveDate, Product
      ,row_number()over(partition by AccountId order by EffectiveDate,Id)rn
      ,rank()over(partition by AccountId order by EffectiveDate)rnk
    from AccountBalanceAndProduct
   )t
)
,tgaps as (
select * 
    ,case when lag(Product,1,Product)
          over(partition by AccountId order by EffectiveDate,intraNum) =Product 
     then 0 
     else 1 
     end newGR
from intraRange
)
,tgr as(
select ID, AccountID, EffectiveDate,intraNum,Product
  ,sum(newgr)over(partition by AccountId order by EffectiveDate,intraNum)+1 grn
  ,rn,rnk,newGR
from tgaps
)
,tgr_pn as(
select AccountId,grn,min(Product) Product
  ,min(EffectiveDate) minDate,max(EffectiveDate) maxDate
  ,min(rn)rnMin,max(rn)rnMax
  ,lead(min(EffectiveDate),1,max(EffectiveDate))over(partition by AccountId order by grn)nextDate
  ,lead(min(Product),1,min(Product))over(partition by AccountId order by grn)nextProduct
from tgr
group by AccountId,grn
)
select AccountId,grn
  ,Product FromProduct, minDate FromDate
  ,nextProduct ToProduct,nextDate ToDate
nextProduct
from tgr_pn
order by accountId,grn;

Fiddle