database - Need to group records based on matching reversal in sql

Question

I have a tricky scenario to aggregate the data.

Data in my source table is as follows.

CustomerId  Transaction Type    Transaction Amount
1               Payment              100
1               ReversePayment      -100
1               payment              100
1               ReversePayment      -100
1               Payment              100
1               Payment              100

Requirement is as follows:

• If the payment as a assoociated Reversepayment with matched amount, sum these two records.
• If the payment does not have an associated Reverse payment, consider it as orphan(dont sum it).

I want output to be like this.

  CustomerId    Transaction Type                Transaction Amount
   1                Payment,ReversePayment               0
   1                payment,ReversePayment               0
   1                payment                             100
   1               Payment                              100

In this scenario,

• First record which is payment has an associated reverse payment (2nd record), Hence the sum becomes 0
• Third record which is payment has an associated reverse payment (4th record), then the sum becomes 0
• Fifth and sixth does not have associated reversals. dont sum these records.

Second Example:

Data in the source as follows:

 CustomerId Transaction Type    Transaction Amount
 1              Payment              100
 1              ReversePayment      -100
 1              payment              300
 1              ReversePayment      -300
 1               Payment              400
 1               Payment              500

Expected Output

CustomerId      Transaction Type                Transaction Amount
 1              Payment,ReversePayment               0
 1              payment,ReversePayment               0
 1              payment                             400
 1              Payment                             500

Second example requirement: -As first and second records (payment and its associated reverse payment got matched) ,sum these two records, output is 0. - As third and fourth records (payment and its associated reverse payment got matched), sum these two records, output is 0. - Fifth and sixth does not have associated reversals. don't sum these records.

I got solutions in group, but data is not always guaranteed to have orphan records as 'payments'. Some times they are 'Payments' and some times they are 'ReversePayments'. Can some help me get ouptut like the below (using rank or rownumber functions ) so that i can group by using RRR column.

CustomerId  Transaction Type    Transaction Amount         RRR
 1              Payment              100                   1
 1              ReversePayment      -100                   1
 1              payment              100                   2
 1              ReversePayment      -100                   2
 1               Payment              100                   3
 1               Payment              100                   4


 CustomerId Transaction Type    Transaction Amount      RRR 
 1              Payment              100                 1
 1              ReversePayment      -100                 1
 1              payment              300                 2
 1              ReversePayment      -300                 2
 1               Payment              400                3
 1               Payment              500                4   

Answer 1

You can enumerate the different types and then aggregate:

select customerid,
       listagg(ttype, ',') within group (order by ttype) as types,
       sum(amount) as amount
from (select t.*,
             row_number() over (partition by customerid, ttype, amount order by customerid) as seqnum
      from t
     ) t
group by customerid, seqnum;