Cumulative Spend per Customer per Month in Oracle

1. Introduction

In many analytical and financial systems, you often need to calculate how much a customer has spent over time — either within a month (running total) or across months (year-to-date cumulative spend).

For example:

For each customer, calculate their total spend for each month.
Then compute the running (cumulative) spend up to that month.

This is a classic window function (analytic function) use case in Oracle.

2. Example Table

Let’s start with a simple dataset.

CREATE TABLE customer_spends (
    cust_id   NUMBER,
    spend_date DATE,
    amount     NUMBER
);

INSERT INTO customer_spends VALUES (101, TO_DATE('2025-01-05','YYYY-MM-DD'), 200);
INSERT INTO customer_spends VALUES (101, TO_DATE('2025-01-15','YYYY-MM-DD'), 300);
INSERT INTO customer_spends VALUES (101, TO_DATE('2025-02-03','YYYY-MM-DD'), 150);
INSERT INTO customer_spends VALUES (101, TO_DATE('2025-02-20','YYYY-MM-DD'), 350);
INSERT INTO customer_spends VALUES (101, TO_DATE('2025-03-10','YYYY-MM-DD'), 400);

INSERT INTO customer_spends VALUES (102, TO_DATE('2025-01-08','YYYY-MM-DD'), 500);
INSERT INTO customer_spends VALUES (102, TO_DATE('2025-02-12','YYYY-MM-DD'), 200);
INSERT INTO customer_spends VALUES (102, TO_DATE('2025-02-28','YYYY-MM-DD'), 300);
INSERT INTO customer_spends VALUES (102, TO_DATE('2025-03-05','YYYY-MM-DD'), 100);
COMMIT;

3. Monthly Spend per Customer

First, we compute monthly totals per customer using the TRUNC(date, 'MM') function to group by month.

SELECT
    cust_id,
    TRUNC(spend_date, 'MM') AS month_start,
    SUM(amount) AS monthly_spend
FROM customer_spends
GROUP BY cust_id, TRUNC(spend_date, 'MM')
ORDER BY cust_id, month_start;

Output:

CUST_ID	MONTH_START	MONTHLY_SPEND
101	01-JAN-25	500
101	01-FEB-25	500
101	01-MAR-25	400
102	01-JAN-25	500
102	01-FEB-25	500
102	01-MAR-25	100

This gives the total spend per customer per month.

4. Cumulative Spend per Customer (Across Months)

To calculate cumulative spend across months (e.g., year-to-date total), we can apply the analytic SUM() function with an appropriate window.

WITH monthly_totals AS (
    SELECT
        cust_id,
        TRUNC(spend_date, 'MM') AS month_start,
        SUM(amount) AS monthly_spend
    FROM customer_spends
    GROUP BY cust_id, TRUNC(spend_date, 'MM')
)
SELECT
    cust_id,
    month_start,
    monthly_spend,
    SUM(monthly_spend) OVER (
        PARTITION BY cust_id
        ORDER BY month_start
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend
FROM monthly_totals
ORDER BY cust_id, month_start;

Output:

CUST_ID	MONTH_START	MONTHLY_SPEND	CUMULATIVE_SPEND
101	01-JAN-25	500	500
101	01-FEB-25	500	1000
101	01-MAR-25	400	1400
102	01-JAN-25	500	500
102	01-FEB-25	500	1000
102	01-MAR-25	100	1100

Explanation:

The SUM() OVER() clause calculates a running total for each customer.
The window ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ensures that Oracle accumulates values from the first month to the current month.

5. Cumulative Spend per Customer per Month (Within the Month)

Sometimes, you want to calculate running totals within the same month — for example, to see how a customer’s spend increased throughout that month.

SELECT
    cust_id,
    TRUNC(spend_date, 'MM') AS month_start,
    spend_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY cust_id, TRUNC(spend_date, 'MM')
        ORDER BY spend_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_monthly_spend
FROM customer_spends
ORDER BY cust_id, spend_date;

Output:

CUST_ID	MONTH_START	SPEND_DATE	AMOUNT	CUMULATIVE_MONTHLY_SPEND
101	01-JAN-25	05-JAN-25	200	200
101	01-JAN-25	15-JAN-25	300	500
101	01-FEB-25	03-FEB-25	150	150
101	01-FEB-25	20-FEB-25	350	500
101	01-MAR-25	10-MAR-25	400	400
102	01-JAN-25	08-JAN-25	500	500
102	01-FEB-25	12-FEB-25	200	200
102	01-FEB-25	28-FEB-25	300	500
102	01-MAR-25	05-MAR-25	100	100

Explanation:

The query calculates cumulative spend within each month per customer.
The PARTITION BY cust_id, TRUNC(spend_date, 'MM') restarts the running total every month.

6. Cumulative Spend Across the Entire Year (Rolling Total)

To calculate a year-to-date cumulative spend, even when data spans multiple years, we can extend the logic by truncating to year and month together.

WITH monthly_totals AS (
    SELECT
        cust_id,
        TRUNC(spend_date, 'MM') AS month_start,
        SUM(amount) AS monthly_spend
    FROM customer_spends
    GROUP BY cust_id, TRUNC(spend_date, 'MM')
)
SELECT
    cust_id,
    month_start,
    monthly_spend,
    SUM(monthly_spend) OVER (
        PARTITION BY cust_id, TRUNC(month_start, 'YYYY')
        ORDER BY month_start
    ) AS ytd_cumulative_spend
FROM monthly_totals
ORDER BY cust_id, month_start;

Output (if data spans multiple years):

CUST_ID	MONTH_START	MONTHLY_SPEND	YTD_CUMULATIVE_SPEND
101	01-JAN-25	500	500
101	01-FEB-25	500	1000
101	01-MAR-25	400	1400

7. Handling Missing Months (Optional Enhancement)

If a customer has no spend in some months, you might still want to show the month with zero spend.

To do that, generate all months between the first and last transaction using a recursive CTE or CONNECT BY, and then left join to your totals.

WITH month_list AS (
    SELECT ADD_MONTHS(TRUNC(MIN(spend_date), 'MM'), LEVEL - 1) AS month_start
    FROM customer_spends
    CONNECT BY LEVEL <= MONTHS_BETWEEN(MAX(spend_date), MIN(spend_date)) + 1
),
cust_months AS (
    SELECT DISTINCT cust_id FROM customer_spends
),
all_combinations AS (
    SELECT c.cust_id, m.month_start
    FROM cust_months c
    CROSS JOIN month_list m
),
monthly_totals AS (
    SELECT
        cust_id,
        TRUNC(spend_date, 'MM') AS month_start,
        SUM(amount) AS monthly_spend
    FROM customer_spends
    GROUP BY cust_id, TRUNC(spend_date, 'MM')
)
SELECT
    a.cust_id,
    a.month_start,
    NVL(mt.monthly_spend, 0) AS monthly_spend,
    SUM(NVL(mt.monthly_spend, 0)) OVER (
        PARTITION BY a.cust_id
        ORDER BY a.month_start
    ) AS cumulative_spend
FROM all_combinations a
LEFT JOIN monthly_totals mt
ON a.cust_id = mt.cust_id AND a.month_start = mt.month_start
ORDER BY a.cust_id, a.month_start;

This version:

Ensures every month is shown for every customer.
Fills missing months with 0.
Computes cumulative spend continuously, even for months with no activity.

8. Key Functions Used

Function	Purpose
`TRUNC(date, 'MM')`	Truncates date to the first day of the month
`SUM() OVER (...)`	Computes running total using analytic window
`PARTITION BY`	Groups cumulative total per customer
`ORDER BY` (inside window)	Orders transactions by date/month for cumulative logic
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	Defines the cumulative window (optional in Oracle)
`NVL()`	Handles nulls for missing data
`CONNECT BY LEVEL`	Generates a sequence of months for filling missing data

9. Summary

Scenario	Query Approach	Description
Monthly total per customer	`GROUP BY TRUNC(date,'MM')`	Aggregate spend per month
Cumulative per month (within same month)	`SUM() OVER (PARTITION BY cust_id, month)`	Running total by date
Cumulative across months	`SUM() OVER (PARTITION BY cust_id ORDER BY month)`	Running total by month
Show missing months	Generate sequence using `CONNECT BY`	Fill missing months with 0 spend

10. Conclusion

In Oracle, analytic window functions make cumulative calculations simple and efficient.
For cumulative spend per customer per month:

Aggregate data by month.
Use SUM() OVER (PARTITION BY cust_id ORDER BY month) to calculate the cumulative running total.
Optionally, handle missing months using CONNECT BY or recursive CTE.