Window function query in MySQL

We have columns Date, CID, Budget. We want to create a column Budget_rank which gives us result as: Observe that the resultant table looks sorted on date first and then whichever same Budget values were there, they got the same rank

Expected Output:

Date	CID	Budget	Budget_rank
2022-05-24	123	12500	1
2022-05-23	123	12500	1
2022-05-16	123	12500	1
2022-05-15	123	9800	2
2022-05-13	123	9800	2
2022-05-12	123	8400	3
2022-05-08	123	8400	3
2022-05-04	123	15600	4
2022-05-02	123	15600	4

Can anyone please assist with the SQL query that may help us generate this column budget_rank.?

What I tried is:

SELECT 
  Date, 
  CID, 
  Budget, 
  DENSE_RANK() OVER (ORDER BY Budget)
FROM table;

But this dint gave us the expected output!

I need to make sure that the Budget_rank column is generated, exactly in the same fashion as shown in the table above. Any help with the query or direction will really help. TIA.

Solution

First use LAG() window function to get the previous Budget of each row and create a boolean flag of 1 or 0 depending on whether the previous Budget is different than the current Budget and then sum over the flags to create the column Budget_rank:

SELECT Date, CID, Budget,
       SUM(flag) OVER (PARTITION BY CID ORDER BY Date DESC) Budget_rank
FROM (
  SELECT *, Budget <> LAG(Budget, 1, -1) OVER (PARTITION BY CID ORDER BY Date DESC) flag
  FROM tablename
) t
ORDER BY Date DESC;

See the demo.