Advanced Customer Segmentation: Finding High-Value Prospects

This challenge requires you to write a SQL query that filters a customer database based on multiple criteria. You'll need to effectively combine AND and OR logical operators to identify a specific segment of high-value customers for a targeted marketing campaign. This is a common task in business intelligence and data analysis, where understanding customer behavior is crucial for effective outreach.

Problem Description

You are tasked with identifying customers from a Customers table who meet specific criteria for a new marketing campaign. The campaign targets customers who are either:

1. Loyal and Active: Customers who have made at least 5 purchases AND have a total spending amount greater than $500.
2. New and Promising: Customers who joined within the last 12 months AND have made at least 2 purchases.

You need to retrieve the CustomerID, FirstName, and LastName of all customers who satisfy either of these conditions.

Key Requirements:

• Filter customers based on purchase history (TotalPurchases) and total spending (TotalSpending).
• Filter customers based on their joining date (JoinDate).
• Combine these individual conditions using AND and OR logical operators to create a comprehensive filter.
• Return a specific set of customer information.

Expected Behavior:

The query should return a list of customers who meet either the "Loyal and Active" criteria OR the "New and Promising" criteria. Customers who meet both will also be included, as they satisfy at least one of the conditions.

Edge Cases:

• Customers with zero purchases should not be included unless they meet the "New and Promising" criteria with at least 2 purchases.
• Customers who joined very recently might have few purchases but could still qualify if they meet the "New and Promising" criteria.
• Consider customers who might have high spending but few purchases, or many purchases but low spending – they should only be included if they meet the specific thresholds.

Examples

Example 1:

Input Customers Table:

Output:

Explanation:

• Alice (101): TotalPurchases (7) >= 5 AND TotalSpending (750.00) > 500. Meets "Loyal and Active".
• Bob (102): JoinDate is within the last 12 months (assuming current date is late 2023) AND TotalPurchases (2) >= 2. Meets "New and Promising".
• Charlie (103): TotalPurchases (10) >= 5 AND TotalSpending (1200.00) > 500. Meets "Loyal and Active".
• Diana (104): Does not meet either condition. JoinDate is recent, but TotalPurchases (1) < 2.
• Ethan (105): Does not meet either condition. TotalPurchases (3) < 5 and TotalSpending (450.00) <= 500. Also JoinDate is not within the last 12 months.

Example 2:

Input Customers Table (assuming current date is 2024-01-01):

Output:

Explanation:

• Fiona (201): JoinDate is within the last 12 months (2023-02-10 is within 12 months of 2024-01-01) AND TotalPurchases (4) >= 2. Meets "New and Promising".
• George (202): TotalPurchases (6) >= 5, but TotalSpending (300.00) <= 500. Does not meet "Loyal and Active". JoinDate is too old for "New and Promising".
• Hannah (203): TotalPurchases (8) >= 5 AND TotalSpending (800.00) > 500. Meets "Loyal and Active".

Constraints

• The Customers table contains at least 100,000 records.
• CustomerID is a unique integer.
• TotalPurchases is a non-negative integer.
• TotalSpending is a non-negative decimal/float.
• JoinDate is a valid date format.
• The query should execute efficiently on a large dataset. Aim for a solution that avoids unnecessary full table scans where possible, though a single SELECT statement with appropriate WHERE clauses is expected.
• The current date for determining "last 12 months" should be considered during query writing. For testing, assume a specific "current date" if needed (e.g., CURRENT_DATE in SQL dialects, or a hardcoded date for pseudocode).

Notes

• Remember to correctly interpret the "last 12 months" condition. This often involves date comparison functions specific to your SQL dialect. For pseudocode, you can use abstract date comparison operators.
• Pay close attention to operator precedence. Parentheses can be crucial for ensuring your logic is evaluated as intended.
• Think about how to express "at least" and "greater than" in your conditions.
• This problem is designed to test your understanding of boolean logic in SQL WHERE clauses and how to construct complex filtering conditions.