A SQL Approach for Identifying High-Spending Customers

In many businesses, understanding customer spending patterns can provide valuable insights into consumer behavior. Imagine a company that tracks customer orders and their total spending. Our task is to identify customers who have spent more than the average amount in the last month.

Objective

The goal is to write a SQL query to find customers who spent more than the average total spending of all customers in the previous month.

Database Structure

The database contains two tables:

1. orders:

   • order_id (INT, primary key)

   • customer_id (INT, foreign key)

   • order_date (DATE): The date of the order.

   • total_amount (DECIMAL): The total amount spent on the order.

2. customers:

   • customer_id (INT, primary key)

   • first_name (VARCHAR)

   • last_name (VARCHAR)

Sample Data

-- Creating the 'customers' table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255)
);

-- Inserting sample data into 'customers'
INSERT INTO customers (customer_id, first_name, last_name) VALUES
(1001, 'John', 'Doe'),
(1002, 'Jane', 'Smith'),
(1003, 'Bob', 'Johnson');

-- Creating the 'orders' table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL
);

-- Inserting sample data into 'orders'
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 1001, '2024-08-10', 150.00),
(2, 1002, '2024-08-12', 200.00),
(3, 1001, '2024-08-15', 100.00),
(4, 1003, '2024-08-18', 300.00),
(5, 1002, '2024-08-22', 120.00),
(6, 1001, '2024-08-25', 50.00);

Expected Output

The result should list the names of customers who have spent more than the average amount:

first_name | last_name
-----------------------
Jane       | Smith

Approach

1. Calculate Total Spending: Determine the total amount spent by each customer in the last month.

2. Compute Average Spending: Find the average spending across all customers during the same period.

3. Compare and Retrieve: Identify customers whose spending exceeds the average.

Solution

Here’s how you can achieve this with a SQL query:

WITH TotalSpent AS (
    SELECT 
        customer_id,
        SUM(total_amount) AS total_spent
    FROM 
        orders
    WHERE order_date BETWEEN
      DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01') 
      AND
      LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH)
    GROUP BY 
        customer_id
),

-- Step 2: Calculate the average total spending of all customers in the last month.
AverageSpending AS (
    SELECT 
        AVG(total_spent) AS avg_spent
    FROM 
        TotalSpent
)

-- Step 3: Retrieve customer names who spent more than the average.
SELECT 
    c.first_name, 
    c.last_name
FROM 
    customers c
JOIN 
    TotalSpent ts ON c.customer_id = ts.customer_id
CROSS JOIN 
    AverageSpending a
WHERE 
    ts.total_spent > a.avg_spent;

Explanation

1. TotalSpending CTE: Calculates the total amount spent by each customer within the last month.

2. AverageSpending CTE: Computes the average spending from the results of TotalSpending.

3. Final Query: Joins the customers table with TotalSpent to fetch customer details and filters out those who spent more than the calculated average.

This approach efficiently identifies high-spending customers and can be easily adapted for different periods or criteria.