Identify the last occurrence of a column value for each customer and select only those rows that are between that and last

Question

ID	Event	Amount
1	A	100
1	B	105
1	C	115
1	A	160
1	B	100
1	C	180
1	A	100
1	D	250
2	A	100
2	B	105
2	C	115
2	A	160
2	R	100
3	B	180
3	C	100
3	D	250
4	A	180
4	C	100
4	D	250
4	B	180
4	C	100
4	Y	250

I would like to start with the last occurrence of C in Event field for each ID and filter out all the rows before the last occurrence of C (for each ID).

So, the desired SQL query will yield the following result:

I used ROW_NUMBER() window function with a Where clause to capture only the rows where the last C occurred but cannot proceed from here even after much trying. Is it possible?

[The color-coded one should be helpful.]

Answer 1

The date column is the missing piece, given that you can use it in a correlated subquery for filtering required rows:

select * 
from t
where date >= (
  select Max(date) 
  from t t2 
  where t2.event='C' and t2.id = t.id
);