Better way to find records meeting two different aggregate criteria?

I need to find customers that have a sum of credit transactions greater than or equal to a threshold amount and a sum of debit transactions less than or equal to the inverse of that same threshold amount. I also want to return the transactions whose aggregate transaction amount satisfies the credit and debit thresholds. I have come up with two solutions, but I think I might be making things more complicated than necessary.

Here is some simplified sample data…

TransactionID  CustomerID  TransactionDate  TransactionType  TransactionAmount
-------------  ----------  ---------------  ---------------  -----------------
1              1           2020-10-01       Credit           10.25
2              1           2020-10-02       Credit           11.50
3              1           2020-10-02       Debit            -13.25
4              2           2020-10-01       Credit           14.22
5              2           2020-10-02       Debit            -50.75
6              2           2020-10-04       Credit           12.85
7              2           2020-10-07       Debit            -4.53
8              3           2020-10-02       Credit           12.85

With a threshold amount of $20 CustomerID 2 is the only customer that meets that threshold for both credits and debits. So here is my expected result…

TransactionID  CustomerID  TransactionDate  TransactionType  TransactionAmount
-------------  ----------  ---------------  ---------------  -----------------
4              2           2020-10-01       Credit           14.22
6              2           2020-10-04       Credit           12.85
5              2           2020-10-02       Debit            -50.75
7              2           2020-10-07       Debit            -4.53

I have come up with two solutions. I should mention that due to the software in which I ultimately need to embed this query I a cannot use temp tables or table variables.

Solution #1

SELECT
         txn.TransactionID
       , txn.CustomerID
       , txn.TransactionDate
       , txn.TransactionType
       , txn.TransactionAmount
FROM     Transactions txn
WHERE    EXISTS (
                    SELECT
                             txn1.CustomerID
                           , SUM(txn1.TransactionAmount) AS [TransactionAmoutTotal]
                    FROM     Transactions txn1
                    WHERE    txn1.TransactionType = 'Credit'
                         AND txn1.CustomerID      = txn.CustomerID
                    GROUP BY txn1.CustomerID
                    HAVING   SUM(txn1.TransactionAmount) >= @ThresholdAmount
                )
     AND EXISTS (
                    SELECT
                             txn2.CustomerID
                           , SUM(txn2.TransactionAmount) AS [TransactionAmoutTotal]
                    FROM     Transactions txn2
                    WHERE    txn2.TransactionType = 'Debit'
                         AND txn2.CustomerID      = txn.CustomerID
                    GROUP BY txn2.CustomerID
                    HAVING   SUM(txn2.TransactionAmount) <= (@ThresholdAmount * -1)
                )
ORDER BY txn.CustomerID
       , txn.TransactionType
       , txn.TransactionDate;

Solution #2

WITH txn_cte (TransactionID, CustomerID, TransactionDate, TransactionType, TransactionAmount, TransactionAmountTotal) AS
(
    SELECT
          txn.TransactionID
        , txn.CustomerID
        , txn.TransactionDate
        , txn.TransactionType
        , txn.TransactionAmount
        , txn.TransactionAmountTotal
    FROM  (
              SELECT
                   TransactionID
                 , CustomerID
                 , TransactionDate
                 , TransactionType
                 , TransactionAmount
                 , SUM(TransactionAmount) OVER (PARTITION BY CustomerID, TransactionType) AS [TransactionAmountTotal]
              FROM Transactions
          ) txn
    WHERE ABS(txn.TransactionAmountTotal) >= @ThresholdAmount
)
SELECT
         txn_cte.TransactionID
       , txn_cte.CustomerID
       , txn_cte.TransactionDate
       , txn_cte.TransactionType
       , txn_cte.TransactionAmount
FROM     txn_cte
WHERE    EXISTS (
                    SELECT txn_cte1.TransactionID
                    FROM   txn_cte txn_cte1
                    WHERE  txn_cte1.TransactionType        = 'Credit'
                       AND txn_cte1.CustomerID             = txn_cte.CustomerID
                )
     AND EXISTS (
                    SELECT txn_cte2.TransactionID
                    FROM   txn_cte txn_cte2
                    WHERE  txn_cte2.TransactionType        = 'Debit'
                       AND txn_cte2.CustomerID             = txn_cte.CustomerID
                )
ORDER BY txn_cte.CustomerID
       , txn_cte.TransactionType
       , txn_cte.TransactionDate;

Here is the dbfiddle of my sample data inserts and two solutions. Is there a better way to do this given I cannot use temp tables or table variables?

Answer

You can use window functions:

select transactionid, customerid, transactiondate, transactiontype, transactionamount
from (
    select t.*,
        sum(case when transactiontype = 'Credit' then transactionamount else 0 end) 
            over(partition by customerid) sum_credit,
        sum(case when transactiontype = 'Debit' then transactionamount else 0 end) 
            over(partition by customerid) sum_debit
    from transactions t
) t
where sum_credit > @ThresholdAmount and sum_debit < - @ThresholdAmount
order by customerid, transactiontype, transactiondate

Within the subquery, the conditional sums compute the total debit and credit of each customer. You can then use this information for filtering in the outer query.

Advertisement

Answer