Analyzing Customer Order Trends with CTEs

Imagine you're working for an e-commerce company and need to understand customer purchasing behavior. Specifically, you want to identify customers who have placed multiple orders within a specific timeframe and understand their total spending. This challenge will guide you through using Common Table Expressions (CTEs) to efficiently process and analyze this data. CTEs are incredibly useful for breaking down complex queries into smaller, more manageable, and readable logical units, making your SQL code more maintainable.

Problem Description

Your task is to write an SQL query that identifies customers who have placed at least two orders in the last 30 days. For each such customer, you need to calculate their total spending during this period and the number of orders they placed.

Key Requirements:

1. Filter Orders by Date: Only consider orders placed within the last 30 days from the current date.
2. Identify Frequent Customers: Group orders by customer and count the number of orders per customer within the 30-day window.
3. Calculate Total Spending: For customers who placed at least two orders in the timeframe, sum their spending for all orders within that same timeframe.
4. Output: The final output should include the customer_id, the total_orders_in_period, and the total_spent_in_period for each identified customer.

Expected Behavior: The query should return a list of customers meeting the criteria. Customers who placed zero or one order in the last 30 days should not be included in the result.

Edge Cases:

• No orders within the last 30 days.
• Customers with only one order within the last 30 days.

Examples

Example 1:

Input Tables: orders table:

Assume "current date" for filtering is 2023-11-21.

Output:

Explanation:

• Customer 1: Orders on 2023-11-01, 2023-11-10, and 2023-11-20 are within the last 30 days. Total orders = 3. Total spent = 120.00 + 80.75 + 65.00 = 265.75.
• Customer 2: Orders on 2023-10-25 and 2023-11-15 are within the last 30 days. Total orders = 2. Total spent = 75.50 + 90.00 = 165.50.
• Customer 3: Only one order on 2023-11-05 within the last 30 days, so they are excluded.

Example 2:

Input Tables: orders table:

Assume "current date" for filtering is 2023-11-21.

Output:

Explanation:

• Customer 5: Both orders are outside the last 30 days.
• Customer 6: Only one order within the last 30 days.
• No customers meet the criteria of placing at least two orders in the last 30 days.

Constraints

• The orders table will contain at least one row.
• The order_date column will be of a date or timestamp data type.
• The amount column will be a numeric data type.
• The "current date" used for filtering will be a specific, fixed date for evaluation purposes.

Notes

• You will need to use functions to get the current date and to subtract days from it, which might vary slightly in syntax depending on your specific SQL dialect (e.g., CURRENT_DATE, GETDATE(), NOW(), DATE('now', '-30 days')). For this challenge, assume a standard CURRENT_DATE and a date subtraction operation like CURRENT_DATE - INTERVAL '30' DAY.
• This challenge is designed to be solved using CTEs. Aim to create at least one CTE to help structure your query.