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
Advertisement
Answer
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