| Data Analyst, Data Engineer | 7 Eleven | Leave a Comment

Write an SQL query to get the customer with the highest total order value for each year and month from the given Order and Customer tables

You are given two tables orders and customers and asked to get customer with the highest total value for each year and month . Order table and Customer table have order_id and customer_id as their primary keys respectively. The customer table’s order_id is a foreign key referencing the order table’s order_id. In case of a tie in the order values, return the customer with the lowest customer_id.

Sample Input

Customers table:

Customers
| customer_id | name              | created_at |
|-------------|-------------------|------------|
| 1           | Alice Johnson     | 2022-03-10 |
| 2           | Bob Smith         | 2022-05-18 |
| 3           | Carol White       | 2022-07-22 |
| 4           | David Brown       | 2022-09-05 |
| 5           | Emma Davis        | 2023-01-14 |
| 6           | Frank Miller      | 2023-03-28 |
| 7           | Grace Wilson      | 2023-06-11 |
| 8           | Henry Moore       | 2023-08-19 |
| 9           | Isabella Taylor   | 2023-10-03 |
| 10          | James Anderson    | 2024-01-25 |

Orders table:

Orders
| order_id | customer_id | order_date | order_value |
|----------|-------------|------------|-------------|
| 1        | 1           | 2024-01-05 | 8500        |
| 2        | 2           | 2024-01-12 | 8500        |
| 3        | 3           | 2024-01-20 | 12000       |
| 4        | 4           | 2024-01-28 | 4500        |
| 5        | 5           | 2024-02-03 | 9500        |
| 6        | 6           | 2024-02-10 | 9500        |
| 7        | 7           | 2024-02-14 | 7000        |
| 8        | 8           | 2024-02-22 | 15000       |
| 9        | 1           | 2024-02-28 | 3000        |
| 10       | 9           | 2024-03-05 | 11000       |
| 11       | 10          | 2024-03-08 | 6500        |
| 12       | 2           | 2024-03-15 | 11000       |
| 13       | 3           | 2024-03-22 | 8000        |
| 14       | 4           | 2024-03-28 | 5500        |
| 15       | 5           | 2024-04-04 | 13000       |
| 16       | 6           | 2024-04-11 | 4000        |
| 17       | 7           | 2024-04-18 | 13000       |
| 18       | 8           | 2024-04-25 | 9000        |
| 19       | 9           | 2024-05-02 | 7500        |
| 20       | 10          | 2024-05-09 | 7500        |
| 21       | 1           | 2024-05-15 | 10000       |
| 22       | 2           | 2024-05-22 | 6000        |
| 23       | 3           | 2024-05-28 | 10000       |
| 24       | 4           | 2024-06-05 | 14000       |
| 25       | 5           | 2024-06-12 | 8000        |

SQL Query to get customer with highest total order value

PostgreSQL
-- CTE 1 — monthly_totals → Aggregates each customer's total order value per year and month using SUM and GROUP BY.
WITH monthly_totals AS (
    SELECT
        customer_id,
        YEAR(order_date)  AS order_year,
        MONTH(order_date) AS order_month,
        SUM(order_value)  AS total_order_value
    FROM orders
    GROUP BY customer_id, YEAR(order_date), MONTH(order_date)
),

-- CTE 2 — ranked → Joins with customers table to get the name, then ranks each customer within their year-month using ROW_NUMBER. Tie is broken by customer_id ASC.
ranked AS (
    SELECT
        mt.customer_id,
        c.name,
        mt.order_year,
        mt.order_month,
        mt.total_order_value,
        ROW_NUMBER() OVER (
            PARTITION BY mt.order_year, mt.order_month
            ORDER BY mt.total_order_value DESC, mt.customer_id ASC
        ) AS rn
    FROM monthly_totals mt
    JOIN customers c ON mt.customer_id = c.customer_id
)

-- Final SELECT → Filters only rn = 1 — the top customer per month.
SELECT
    order_year,
    order_month,
    customer_id,
    name,
    total_order_value
FROM ranked
WHERE rn = 1
ORDER BY order_year, order_month;

Explanation:

  1. One customer may have multiple orders in the same month, so we first group by customer_id, year and month and sum up all their order values to get one total per customer per month.
  2. Then we rank customers within each month by their total order value. PARTITION BY resets the ranking for every new month. In case two customers have the same total, customer_id ASC ensures the lower customer_id gets rank 1
  3. Simply selecting the top ranked customer from each month and giving us exactly one row per year-month

Expected Output:

Output
| order_year | order_month | customer_id | name            | total_order_value |
|------------|-------------|-------------|-----------------|-------------------|
| 2024       | 1           | 1           | Alice Johnson   | 8500              |
| 2024       | 2           | 8           | Henry Moore     | 15000             |
| 2024       | 3           | 9           | Isabella Taylor | 11000             |
| 2024       | 4           | 5           | Emma Davis      | 13000             |
| 2024       | 5           | 1           | Alice Johnson   | 10000             |
| 2024       | 6           | 4           | David Brown     | 14000             |

-- Jan: Alice & Bob tied at 8500 → Alice wins (lower customer_id = 1)
-- Apr: Emma & Grace tied at 13000 → Emma wins (lower customer_id = 5)
-- May: Alice & Carol tied at 10000 → Alice wins (lower customer_id = 1)

Follow up questions from the interviewer

  • Why did you use two CTEs instead of one?
    • First CTE aggregates totals per month, second CTE ranks them and mixing both in one would make it messy and unreadable.
  • Why ROW_NUMBER instead of RANK here?
    • RANK would return both tied customers, ROW_NUMBER picks exactly one, which is what the question asks for.
  • Why SUM in the first CTE?
    • A customer can place multiple orders in the same month, so we need to add them up before ranking.
  • What does PARTITION BY do here?
    • It resets the row number for every new year-month combination, so ranking is independent per month.
  • Why customer_id ASC in the ORDER BY?
    • To handle ties, when two customers have the same total, the one with the lower customer_id gets rank 1.
  • Can you do this without a CTE?
    • Yes using nested subqueries, but CTE is cleaner, easier to read and debug.
  • What happens if a customer has no orders?
    • They won’t appear in the output since we are using JOIN. Use LEFT JOIN if you want to include them.

Leave a Reply

Scroll to Top