You need to use the INDEX() function for your purpose with the appropriate table calculation, in order to compute along [Purchase Date] and for each [Id Account].
Please have a look at the attached workbook made on Sample Superstore data which will show you an example.
In addition, regarding your Custom SQL tentative, keep in mind it's not recommended for performance and on the Oracle part you might need to get in touch with your DBA to verify your analytical function query plan.
As the cost of these functions is fairly high when not optimized.
Answer for Maxime.twbx 343.9 KB