# 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

```sql

-- 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:

```markdown
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:

```sql
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.