Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002

    Question Unanswered: Total payment volume per transaction per customer


    I have the following table:


    Txn_id Txn_date Txn_amount_$ User_id
    001 06/27/2007 3.00 1010

    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

    Thanks a lot for your help!


  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    Did you mean something like this?
    SELECT txn_id, user_id, SUM(txn_amount) total_payment_volume
    FROM your_table
    WHERE date_column >= TRUNC(SYSDATE) - 30
    GROUP BY txn_id, user_id;

  3. #3
    Join Date
    May 2002
    Not exactly
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts