E-Commerce SQL Question
Problem:
You are tasked with analyzing the top 3 customers based on their total spending in the last 6 months. For each customer, you need to provide the following details:
- Customer’s ID
- Customer’s Name (First and Last)
- Total Amount Spent
- Number of Orders Placed
- Total Quantity of Items Purchased
The result should only include customers who have placed at least 3 orders in the last 6 months.
Tables Involved:
customertableorderstableorder_itemstableproducttable (for getting prices)
Example
Relationship Between the
The two tables, Example
customer data:
| customer _id | first _name | last _name | phone _number | shipping _address | billing _address | created _at |
|---|---|---|---|---|---|---|
| 1 | John | Doe | 123-456-7890 | 123 Elm St, City, Country | 123 Elm St, City, Country | 2025-01-01 10:00:00 |
| 2 | Jane | Smith | 987-654-3210 | 456 Oak St, City, Country | 456 Oak St, City, Country | 2025-01-05 14:00:00 |
| product _id | product _name | description | price | stock _quantity | category | created _at |
|---|---|---|---|---|---|---|
| 1 | Laptop | 15-inch screen laptop | 799.99 | 50 | Electronics | 2025-01-01 10:00:00 |
| 2 | Headphones | Noise-canceling headphones | 199.99 | 100 | Electronics | 2025-01-03 12:00:00 |
| 3 | Coffee Maker | Automatic coffee machine | 59.99 | 200 | Kitchen | 2025-01-02 15:00:00 |
Relationship Between the customer and product Tables
The two tables, customer and product, are independent in this schema.
Example orders and order_items data:
orders table:
| order_id | customer_id | order_date | total_amount |
|---|---|---|---|
| 1 | 1 | 2025-01-01 10:30:00 | 999.98 |
| 2 | 2 | 2025-01-05 14:30:00 | 459.98 |
order_items table:
| order_item_id | order_id | product_id | quantity | item_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 799.99 |
| 2 | 1 | 2 | 1 | 199.99 |
| 3 | 2 | 3 | 2 | 59.99 |
WITH CustomerSpending AS (
-- Calculate the total spending, number of orders, and total quantity per customer
SELECT
o.customer_id,
c.first_name,
c.last_name,
SUM(oi.quantity * oi.item_price) AS total_spent,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(oi.quantity) AS total_quantity
FROM
orders o
JOIN
order_items oi ON o.order_id = oi.order_id
JOIN
customer c ON o.customer_id = c.customer_id
WHERE
o.order_date >= CURRENT_DATE - INTERVAL '6 MONTH' -- Orders placed in the last 6 months
GROUP BY
o.customer_id, c.first_name, c.last_name
HAVING
COUNT(DISTINCT o.order_id) >= 3 -- Only consider customers with at least 3 orders
)
-- Select the top 3 customers based on total spending
SELECT
customer_id,
first_name,
last_name,
total_spent,
total_orders,
total_quantity
FROM
CustomerSpending
ORDER BY
total_spent DESC -- Order by the highest spending
LIMIT 3; -- Top 3 customers
Explanation:
- CustomerSpending CTE:
- This Common Table Expression (CTE) calculates the total amount spent (
total_spent), number of orders (total_orders), and total quantity of items purchased (total_quantity) for each customer within the last 6 months. - The
WHEREclause filters orders within the last 6 months (CURRENT_DATE - INTERVAL '6 MONTH'). - The
HAVINGclause ensures that only customers who placed at least 3 orders are included in the result.
- This Common Table Expression (CTE) calculates the total amount spent (
- Main Query:
- After calculating the spending details in the CTE, the main query selects the top 3 customers based on their total spending (
total_spent). - The
ORDER BY total_spent DESCorders the customers by their total spending in descending order, andLIMIT 3fetches only the top 3 customers.
- After calculating the spending details in the CTE, the main query selects the top 3 customers based on their total spending (
Sample Output:
| customer _id | first _name | last _name | total _spent | total _orders | total _quantity |
|---|---|---|---|---|---|
| 5 | John | Doe | 2500.50 | 5 | 15 |
| 2 | Jane | Smith | 1890.75 | 4 | 12 |
| 3 | Alice | Johnson | 1750.20 | 3 | 10 |
