Assign the row sequence for each customer from the row where amount is greater than zero

Question

I want to assign the row sequence for each customer from the row where amount is greater than zero. sequencing always should start from the row where amount is zero. Here is sample data with expected output (expected_sequence).

customer	trans_date	Amount	expected_sequence
1	2023-01-01	$0	null
1	2023-02-01	$100	1
1	2023-03-02	$200	2
2	2023-01-01	$100	1
2	2023-02-01	$0	2
2	2023-03-02	$150	3
3	2023-01-01	$0	null
3	2023-02-01	$0	null
3	2023-03-02	$150	1
4	2023-01-01	$0	null
4	2023-02-01	$0	null
4	2023-03-02	$0	null

Answer 1

You could use a subquery to fetch the date of first transaction per customer and start sequencing from that date on.

WITH    --  S a m p l e   D a t a :
    tbl (CUSTOMER, TRANS_DATE, AMOUNT) AS
        (   Select 1, DATE '2023-01-01', 0 From Dual Union All
            Select 1, DATE '2023-02-01', 100 From Dual Union All
            Select 1, DATE '2023-03-02', 200 From Dual Union All
            
            Select 2, DATE '2023-01-01', 100 From Dual Union All
            Select 2, DATE '2023-02-01', 0 From Dual Union All
            Select 2, DATE '2023-03-02', 150 From Dual Union All
            
            Select 3, DATE '2023-01-01', 0 From Dual Union All
            Select 3, DATE '2023-02-01', 0 From Dual Union All
            Select 3, DATE '2023-03-02', 150 From Dual Union All
            
            Select 4, DATE '2023-01-01', 0 From Dual Union All
            Select 4, DATE '2023-02-01', 0 From Dual Union All
            Select 4, DATE '2023-03-02', 0 From Dual 
        )

--    M a i n   S Q L :
Select    t.CUSTOMER, t.TRANS_DATE, AMOUNT,
          Sum(Case When t.TRANS_DATE >= f.FIRST_TRANS_DATE Then 1 End) 
                Over(Partition By t.CUSTOMER Order By t.CUSTOMER, t.TRANS_DATE 
                     Rows Between Unbounded Preceding And Current Row) "SEQ"
From      tbl t
Left Join ( Select    CUSTOMER, Min(TRANS_DATE) "FIRST_TRANS_DATE"
            From      tbl
            Where     AMOUNT > 0
            Group By  CUSTOMER
          ) f ON(f.CUSTOMER = t.CUSTOMER)
Order By  t.CUSTOMER, t.TRANS_DATE
/*      R e s u  l t :
CUSTOMER    TRANS_DATE  AMOUNT      SEQ
--------  ----------  --------  -------
       1  2023-01-01         0     null
       1  2023-02-01       100        1
       1  2023-03-02       200        2
       2  2023-01-01       100        1
       2  2023-02-01         0        2
       2  2023-03-02       150        3
       3  2023-01-01         0     null
       3  2023-02-01         0     null
       3  2023-03-02       150        1
       4  2023-01-01         0     null
       4  2023-02-01         0     null
       4  2023-03-02         0     null         */