Use CTE to Find Cumulative Daily Payments
Use CTE to Find Cumulative Daily Payments
Payments Table:
Query Explanation:
-
"Calculate daily total payments" → we use a CTE named
DailyPaymentsthat groups data byPaymentDateand usesSUM(Amount)to get total payments for each day. -
"Cumulative total" → we use
SUM(DailyTotal) OVER (ORDER BY PaymentDate)to calculate the running total of payments day by day in ascending order. -
The SELECT statement gets each day's total and its cumulative total using the CTE.
-
GROUP BY is used in the CTE to aggregate payment amounts by each date.
SQL Query:
WITH DailyPayments AS (
SELECT
PaymentDate,
SUM(Amount) AS DailyTotal
FROM
Payments
GROUP BY
PaymentDate
)
Output:
