Top customer by spend for each month of 2025

WITH monthly_spend AS (
    -- Step 1: total spend per customer per month
    SELECT
        date_trunc('month', o.created_at)::date AS month,
        o.customer_id,
        SUM(o.amount)                           AS total_spent
    FROM orders o
    WHERE o.created_at >= '2025-01-01'
      AND o.created_at <  '2026-01-01'
    GROUP BY date_trunc('month', o.created_at), o.customer_id
),
ranked AS (
    -- Step 2: rank customers within each month
    SELECT
        month,
        customer_id,
        total_spent,
        RANK() OVER (
            PARTITION BY month
            ORDER BY total_spent DESC
        ) AS rnk
    FROM monthly_spend
)
-- Step 3: keep the #1 customer(s) and attach names
SELECT
    r.month,
    c.name,
    r.total_spent
FROM ranked r
JOIN customers c ON c.id = r.customer_id
WHERE r.rnk = 1
ORDER BY r.month;

(Syntax is PostgreSQL. For MySQL, replace the month expression with DATE_FORMAT(o.created_at, '%Y-%m-01'). The rest works as is on MySQL 8+, SQL Server, etc.)

How the window function works

RANK() OVER (PARTITION BY month ORDER BY total_spent DESC)

• OVER (...) makes this a window function. Unlike GROUP BY, it doesn't collapse rows. Each row keeps its identity and gets an extra computed column.
• PARTITION BY month splits the rows into independent groups, one per month. The ranking restarts at 1 in each month.
• ORDER BY total_spent DESC orders rows within each partition, so the biggest spender gets rank 1.
• RANK() assigns the position. If two customers tie for the highest spend, both get rank 1 (and the next gets rank 3), so both appear in the result.

Choosing the ranking function

If you want exactly one customer per month, use ROW_NUMBER() with a deterministic tie-breaker:

ROW_NUMBER() OVER (PARTITION BY month ORDER BY total_spent DESC, customer_id)

Notes

• Date filter: I used >= '2025-01-01' AND < '2026-01-01' rather than BETWEEN ... '2025-12-31', because the latter would miss orders on Dec 31 after midnight if created_at is a timestamp. This form can also use an index on created_at.
• Why two steps: window functions are evaluated after GROUP BY, and you can't filter on them in WHERE in the same query level. That's why the rank is computed in a CTE and filtered in the outer query.
• Months with no orders won't appear. To include all 12 months, drive the query from generate_series('2025-01-01'::date, '2025-12-01', '1 month') and LEFT JOIN the ranked results where rnk = 1.
• PostgreSQL shortcut: SELECT DISTINCT ON (month) ... ORDER BY month, total_spent DESC gives one row per month without an explicit window function.

Here is the SQL query to solve this problem. Note that this solution uses DATE_TRUNC (common in PostgreSQL, Snowflake, and BigQuery). If you are using MySQL, you can replace DATE_TRUNC('month', o.created_at) with DATE_FORMAT(o.created_at, '%Y-%m-01').

SQL Query

WITH MonthlyCustomerSpend AS (
    -- Step 1: Calculate total spend per customer for each month in 2025
    SELECT 
        DATE_TRUNC('month', o.created_at) AS order_month,
        c.id AS customer_id,
        c.name AS customer_name,
        SUM(o.amount) AS total_spent
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    WHERE o.created_at >= '2025-01-01' 
      AND o.created_at < '2026-01-01'
    GROUP BY 
        DATE_TRUNC('month', o.created_at), 
        c.id, 
        c.name
),
RankedSpending AS (
    -- Step 2: Rank customers by their total spend within each month
    SELECT 
        order_month,
        customer_name,
        total_spent,
        RANK() OVER (
            PARTITION BY order_month 
            ORDER BY total_spent DESC
        ) as spend_rank
    FROM MonthlyCustomerSpend
)
-- Step 3: Filter for the top spender(s) of each month
SELECT 
    order_month,
    customer_name,
    total_spent
FROM RankedSpending
WHERE spend_rank = 1
ORDER BY order_month;

Explanation of the Window Function

In the second Common Table Expression (RankedSpending), we use the RANK() window function:

RANK() OVER (
    PARTITION BY order_month 
    ORDER BY total_spent DESC
)

Here is how it works:

1. PARTITION BY order_month: This divides the data into separate "buckets" or partitions for each month. The window function will perform its calculation independently for January, then reset for February, and so on.
2. ORDER BY total_spent DESC: Within each monthly bucket, the rows are sorted from the highest total_spent to the lowest.
3. RANK(): This assigns a number (1, 2, 3, etc.) to each customer based on the order defined above. The customer who spent the most in a given month gets a rank of 1.

Why RANK() instead of ROW_NUMBER()?

• RANK() handles ties gracefully. If two customers tie for the highest spend in March, RANK() will assign both of them a rank of 1, and both will appear in your final result.
• ROW_NUMBER() forces a unique number for every row. If there is a tie, it will arbitrarily pick one customer to be 1 and the other to be 2, meaning you would miss out on seeing one of your top spenders in the final output.

Here is a PostgreSQL version:

WITH months AS (
    SELECT generate_series(
        DATE '2025-01-01',
        DATE '2025-12-01',
        INTERVAL '1 month'
    )::date AS month_start
),
monthly_spend AS (
    SELECT
        date_trunc('month', o.created_at)::date AS month_start,
        o.customer_id,
        SUM(o.amount) AS total_spent
    FROM orders o
    WHERE o.created_at >= DATE '2025-01-01'
      AND o.created_at <  DATE '2026-01-01'
    GROUP BY
        date_trunc('month', o.created_at)::date,
        o.customer_id
),
ranked AS (
    SELECT
        ms.*,
        ROW_NUMBER() OVER (
            PARTITION BY ms.month_start
            ORDER BY ms.total_spent DESC, ms.customer_id
        ) AS rn
    FROM monthly_spend ms
)
SELECT
    to_char(m.month_start, 'YYYY-MM') AS month,
    c.id AS customer_id,
    c.name AS customer_name,
    r.total_spent
FROM months m
LEFT JOIN ranked r
    ON r.month_start = m.month_start
   AND r.rn = 1
LEFT JOIN customers c
    ON c.id = r.customer_id
ORDER BY m.month_start;

Explanation

• monthly_spend groups orders by month and customer, calculating each customer’s total spend for that month.
• ROW_NUMBER() is a window function used in the ranked CTE:

ROW_NUMBER() OVER (
    PARTITION BY ms.month_start
    ORDER BY ms.total_spent DESC, ms.customer_id
)

This assigns a ranking within each month:

• PARTITION BY ms.month_start means rankings restart for every month.
• ORDER BY ms.total_spent DESC puts the highest-spending customer first.
• ms.customer_id is used as a deterministic tie-breaker.
• The final query keeps only rn = 1, meaning the top-spending customer for each month.

The months CTE ensures that all 12 months of 2025 appear, even if a month has no orders. In that case, the customer and amount columns will be NULL.

If you want to return all customers tied for first place instead of breaking ties, replace ROW_NUMBER() with RANK() and filter on rank = 1.

WITH monthly_spend AS (
  SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    o.customer_id,
    c.name,
    SUM(o.amount) AS total_spent
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.created_at >= DATE '2025-01-01'
    AND o.created_at <  DATE '2026-01-01'
  GROUP BY DATE_TRUNC('month', o.created_at), o.customer_id, c.name
),
ranked AS (
  SELECT
    month,
    customer_id,
    name,
    total_spent,
    RANK() OVER (
      PARTITION BY month
      ORDER BY total_spent DESC
    ) AS rnk
  FROM monthly_spend
)
SELECT month, customer_id, name, total_spent
FROM ranked
WHERE rnk = 1
ORDER BY month;

What the window function does

RANK() OVER (PARTITION BY month ORDER BY total_spent DESC) ranks customers inside each month by total spend, highest first. PARTITION BY month restarts the ranking at each month so January’s #1 is independent of February’s. ORDER BY total_spent DESC makes the largest sum rank 1. RANK (not ROW_NUMBER) assigns the same rank to ties, so if two customers spent the same top amount in a month, both are returned. Filtering rnk = 1 keeps only those top spenders.

Months with no orders do not appear. If you need exactly one row per month even on ties, use ROW_NUMBER() instead of RANK() (the winner among ties is then arbitrary unless you add a tie-breaker such as customer_id).