To solve this problem, we need to retrieve a list of distinct customers who have made a purchase (of any product) within the last 30 days. We'll use joins to connect the relevant tables and filter orders based on the date range.

Assumptions About Tables:

• Customers: Contains customer details (e.g., customer_id, customer_name).
• Orders: Links customers to their orders (e.g., order_id, customer_id, order_date).
• OrderDetails: Connects orders to the products purchased (e.g., order_id, product_id).
• Products: Contains product information (though not strictly necessary here, included to follow typical e-commerce schema joins).

SQL Query:

SELECT DISTINCT 
    c.customer_id, 
    c.customer_name
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id
INNER JOIN 
    orderdetails od ON o.order_id = od.order_id
INNER JOIN 
    products p ON od.product_id = p.product_id
WHERE 
    o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Explanation:

1. Joins:

   • INNER JOIN orders: Links customers to their orders using customer_id.
   • INNER JOIN orderdetails: Connects orders to the products they contain using order_id.
   • INNER JOIN products: Ensures we only consider valid products (optional but adds completeness).

2. Filter:

   • o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY): Selects orders placed in the last 30 days (using CURDATE() for today's date and DATE_SUB to subtract 30 days).

3. Distinct:

   • DISTINCT ensures each customer is listed only once, even if they have multiple orders/products in the period.

This query efficiently returns the required list of customers who have purchased any product in the last 30 days. Adjust table/column names if your schema uses different identifiers (e.g., order_date might be purchase_date).

作者声明:本文包含人工智能生成内容。