SQL solution to assign a group number to a set of repeated values

Question

I have a table with columns for Customer, Order, Fruit, and Quantity, sorted by Customer and Order.

I need to assign the example Desired Group Number values sequentially, starting at 1, assigning the same value until the Fruit changes and then increment 1 for the next Fruit, resetting back to one when Customer changes.

I've racked my brain to think of a solution using LAG, RANK or ROW_NUMBER window functions but remain stumped. Suggestions?!?!

Customer Order Fruit   Qty  DESIRED GROUP NUMBER
-------- ----- -----   ---  --------------------
A        1     Apple.   5     1
A        2     Apple.   4     1
A        3     Apple.   6     1
A        4     Banana.  4     2
A        5     Orange.  1     3
A        6     Orange.  9     3
A        7     Apple.   9     4
A        8     Apple.   3     4
B        1     Banana.  6     1
B        2     Banana.  5     1
B        3     Apple.   4     2

Answer 1

It can be achieved with windowed SUM and LAG:

WITH cte_lag AS
(
  SELECT t.*, LAG(t.Fruit) OVER(PARTITION BY Customer ORDER BY "order") AS fruit_prev
  FROM t
) 
SELECT *,SUM( CASE WHEN fruit = COALESCE(fruit_prev,fruit) THEN 0 ELSE 1 END) 
            OVER(PARTITION BY Customer  ORDER BY "order") + 1 AS grp
FROM cte_lag
ORDER  BY customer, "order";

db<>fiddle demo