SQL few running total at different stops-CodePudding

Note: I am bringing up a problem to which I have a solution, the purpose of the question is to see if there is a better one.

I have customers that make transactions. I want for each customer to know when did they reach 3K, 5K and 7K (or any other threshold). End result is:

I was wondering if I have a better way to do it, other then copying the same CTE each time. This is the code:

WITH customers AS
(
SELECT CustomerID
FROM someTable AS st
), runningTotals AS 
(
SELECT cs.CustomerID, trx.TransactionID, trx.transactionDate,
trx.amount,
SUM(trx.amount) OVER (PARTITION BY trx.customerID ORDER BY trx.transactionDate) AS runningTotal
FROM customers AS cs
INNER JOIN transactions as trx on trx.customerID = cs.CustomerID
), reached3K AS
(
SELECT rt.CustomerID, MIN(rt.transactionDate) AS reached3
FROM runningTotals AS rt
WHERE rt.runningTotal >=3000
GROUP BY rt.CustomerID
), reached5K AS
(
    SELECT rt.CustomerID, MIN(rt.transactionDate) AS reached3
    FROM runningTotals AS rt
    WHERE rt.runningTotal >=5000
    GROUP BY rt.CustomerID
), reached7K AS
(
    SELECT rt.CustomerID, MIN(rt.transactionDate) AS reached3
    FROM runningTotals AS rt
    WHERE rt.runningTotal >=7000
    GROUP BY rt.CustomerID
)
SELECT cs.CustomerID, r3.reached3, r5.reached5, r7.reached7
FROM customers as cs
INNER JOIN reached3K as r3 on r3.CustomerID = cs.CustomerID
INNER JOIN reached5K as r5 on r5.CustomerID = cs.CustomerID
INNER JOIN reached7K as r7 on r7.CustomerID = cs.CustomerID

CodePudding user response：

You cqan combine the 3 subquereis to one with condition aggregation

WITH customers AS
(
    SELECT CustomerID
    FROM someTable AS st
), runningTotals AS 
(
    SELECT cs.CustomerID, trx.TransactionID, trx.transactionDate,
    trx.amount,
    SUM(trx.amount) OVER (PARTITION BY trx.customerID ORDER BY trx.transactionDate) AS runningTotal
    FROM customers AS cs
    INNER JOIN transactions as trx on trx.customerID = cs.CustomerID
), reached3K AS
(
    SELECT rt.CustomerID
        , MIN(CASE WHEN rt.runningTotal >=3000 THEN rt.transactionDate ELSE '2099-12-31 23:59:59' END ) AS reached3
        , MIN(CASE WHEN rt.runningTotal >=5000 THEN rt.transactionDate ELSE '2099-12-31 23:59:59' END ) AS reached5
        , MIN(CASE WHEN rt.runningTotal >=7000 THEN rt.transactionDate ELSE '2099-12-31 23:59:59' END ) AS reached7
        FROM runningTotals AS rt
    WHERE rt.runningTotal >=3000
    GROUP BY rt.CustomerID
)
SELECT cs.CustomerID, r3.reached3, r5.reached5, r7.reached7
FROM customers as cs
INNER JOIN reached3K as r3 on r3.CustomerID = cs.CustomerID

CodePudding user response：

You could refactor using a conditional aggregation technique. Below is an untested example (since you didn't include DDL and sample data).

WITH runningTotals AS 
    (
    SELECT 
           st.CustomerID
         , trx.transactionDate
         , trx.amount
         , SUM(trx.amount) OVER (PARTITION BY trx.customerID ORDER BY trx.transactionDate) AS runningTotal
    FROM someTable AS st
    INNER JOIN transactions as trx on trx.customerID = st.CustomerID
    )
SELECT 
      rt.CustomerID
    , MIN(CASE WHEN rt.runningTotal >=3000 THEN rt.transactionDate END) AS DateReached3K
    , MIN(CASE WHEN rt.runningTotal >=5000 THEN rt.transactionDate END) AS DateReached5K
    , MIN(CASE WHEN rt.runningTotal >=7000 THEN rt.transactionDate END) AS DateReached7K
FROM runningTotals AS rt
GROUP BY rt.CustomerID
ORDER BY rt.CustomerID;