Advanced Customer Loyalty Program Tiering

This challenge requires you to implement a sophisticated customer loyalty program tiering system within SQL. You'll need to process customer transaction data, calculate loyalty metrics, and assign customers to different tiers based on a set of evolving rules. This is a common scenario in retail and e-commerce, where understanding and rewarding customer loyalty drives engagement and revenue.

Problem Description

You are tasked with building a SQL-based system to manage customer loyalty tiers. The system should:

1. Process Customer Transactions: Ingest historical transaction data to calculate key customer metrics.
2. Define Loyalty Tiers: Implement logic to assign customers to one of several defined loyalty tiers (e.g., Bronze, Silver, Gold, Platinum). The criteria for these tiers are dynamic and depend on multiple factors.
3. Handle Tier Transitions: Ensure that customers are correctly moved up or down tiers based on their updated metrics.
4. Calculate Tier Benefits (Implicit): While not directly calculating benefits, the tier assignment is the foundation for future benefit calculations.

Key Requirements:

• Customer Identification: Each customer has a unique customer_id.
• Transaction Data: You will be provided with a table containing customer transactions, including transaction_id, customer_id, transaction_date, and amount.
• Tier Definitions: The loyalty tiers are defined by specific criteria:
  • Bronze: Customers who have made at least one purchase in the last 12 months.
  • Silver: Customers who have spent a cumulative total of at least $1,000 in the last 12 months AND have made at least 3 purchases in the last 12 months.
  • Gold: Customers who have spent a cumulative total of at least $5,000 in the last 12 months AND have made at least 10 purchases in the last 12 months.
  • Platinum: Customers who have spent a cumulative total of at least $10,000 in the last 12 months AND have made at least 20 purchases in the last 12 months.
• Time Window: All calculations for spending and purchase count should be based on transactions within the last 12 months from the current date (or a specified reference date).
• Tier Prioritization: If a customer meets the criteria for multiple tiers, they should be assigned to the highest applicable tier. For example, a customer meeting Gold criteria also meets Silver criteria. They should be assigned to Gold.
• Output: The final output should be a table listing each customer_id and their assigned loyalty_tier.

Important Edge Cases:

• New Customers: Customers with no transactions should not be assigned a tier (or can be implicitly considered "None").
• Inactive Customers: Customers who haven't made a purchase in over 12 months should not qualify for Bronze or higher.
• Ambiguous Dates: Ensure date comparisons are handled correctly, especially around the 12-month mark.

Examples

Let's assume the current date is 2023-10-27.

Example 1: Simple Tier Assignment

Input Tables:

Customers

Transactions

Output:

Explanation:

• Customer 101: Made 2 purchases totalling $125.00 within the last 12 months. Meets Bronze criteria.

• Customer 102: Made 1 purchase totalling $1200.00 within the last 12 months. Meets Silver criteria ($1200 >= $1000, 1 purchase < 3). Correction: Customer 102 only made 1 purchase, so they do not meet Silver criteria. They do meet Bronze criteria. Let's re-evaluate based on the correct logic:

  • Customer 101: 2 purchases ($125 total) in last 12 months. Bronze.
  • Customer 102: 1 purchase ($1200 total) in last 12 months. Meets Bronze. Does not meet Silver (needs 3 purchases). Bronze.
  • Customer 103: 3 purchases totalling $1200.00 within the last 12 months. Meets Silver criteria ($1200 >= $1000, 3 purchases >= 3). Also meets Gold criteria ($1200 < $5000). Correction: Customer 103's transaction from 2022-11-01 is OUTSIDE the last 12 months.
    • Transactions in last 12 months for 103:
      • 2023-03-05: $300
      • 2023-04-10: $400
      • 2023-05-20: $500
    • Total spent by 103 in last 12 months: $300 + $400 + $500 = $1200
    • Number of purchases by 103 in last 12 months: 3
    • Customer 103 meets Silver criteria. Does not meet Gold ($1200 < $5000). Silver.
  • Customer 104: Made 1 purchase totalling $100.00, but this was in 2021-12-01, which is outside the last 12 months. Does not meet any criteria. NULL.

  Revised Output for Example 1:

  customer_id	loyalty_tier
  101	Bronze
  102	Bronze
  103	Silver
  104	NULL

Example 2: Tier Transition and Prioritization

Assume current date is 2023-10-27.

Input Tables:

Customers

Transactions

Output:

Explanation:

• Customer 201:

  • Transactions in last 12 months (from 2022-10-28 to 2023-10-27): All transactions from #9 to #20 are within this window.
  • Total spent: $6000 + 5000 + 1000 + 500 + 200 + 300 + 400 + 200 + 100 + 150 + 300 + 100 = $14250.00
  • Number of purchases: 12
  • Meets Platinum criteria ($14250 >= $10000 and 12 >= 20 is FALSE). Correction: Customer 201 has 12 purchases. They need 20 for Platinum.
    • Re-evaluating Customer 201:
      • Total spent: $14250.00
      • Number of purchases: 12
      • Meets Gold criteria ($14250 >= $5000 and 12 >= 10).
      • Meets Platinum criteria ($14250 >= $10000, but 12 < 20).
      • Therefore, Customer 201 should be Gold.

• Customer 202:

  • Transactions in last 12 months (from 2022-10-28 to 2023-10-27): All transactions from #21 to #32 are within this window.
  • Total spent: $3000 * 12 = $36000.00
  • Number of purchases: 12
  • Meets Platinum criteria ($36000 >= $10000 and 12 >= 20 is FALSE). Correction: Customer 202 has 12 purchases. They need 20 for Platinum.
    • Re-evaluating Customer 202:
      • Total spent: $36000.00
      • Number of purchases: 12
      • Meets Gold criteria ($36000 >= $5000 and 12 >= 10).
      • Meets Platinum criteria ($36000 >= $10000, but 12 < 20).
      • Therefore, Customer 202 should be Gold.

  Revised Output for Example 2:

  customer_id	loyalty_tier
  201	Gold
  202	Gold

Example 3: Edge Case - No Recent Transactions

Assume current date is 2023-10-27.

Input Tables:

Customers

Transactions

Output:

Explanation:

• Customer 301: The only transaction occurred in 2021, which is well outside the last 12 months. They do not meet the criteria for any tier.

Constraints

• The Transactions table can contain up to 10,000,000 rows.
• The Customers table can contain up to 1,000,000 rows.
• amount will be a non-negative decimal value.
• transaction_date will be a valid date format.
• The solution should execute within 60 seconds on a typical database system.

Notes

• You will likely need to use common table expressions (CTEs) or subqueries to first aggregate customer data and then apply the tiering logic.
• Pay close attention to the date filtering for the "last 12 months". You'll need to dynamically calculate this period based on the current date.
• The order of CASE statements or IF conditions will be crucial for correctly prioritizing higher tiers.
• Consider how you will handle customers who exist in the Customers table but have no corresponding entries in the Transactions table.