I need to calculate a total payment volume for 30 days for each transaction from the data it occured and each user. Txn_id and user_id define a primary key on table T1.
For example, if transaction happened on June 27, 2007, I need to see TPV of that customer for 30 days back up until June 27th inclusively along with respective transaction id and user id.
There are multiple transactions per day, but I'll deal with it later.
My report needs to look like this
Txn_id User_id date TPV_30days
001 1010 06/27/2007 450.00
This was my initial code. But it does not return what I need. It returns just a transaction amount of each transaction which satisfies date condition. I need a transaction id, user id and this user 30 day TPV as of date of this transaction. I somehow need to mix three different grains on the same level.