# SQL few running total at different stops

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
```

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
```
6 People found this is helpful