6 hours ago

Rohit • 30

Hyderabad

Question: Regional Sales Performance and Customer Lifetime Value Analysis

Difficulty: Medium

Problem Statement: You are given a retail database containing information about customers, products, categories, orders, and order items.

Task: Write a query that generates an advanced regional sales performance analysis with customer lifetime value metrics, market penetration insights, and profitability patterns. Only include customers who have made at least one completed order and display all monetary values with exactly 2 decimal places, sorted by total region revenue (highest first), then by customer lifetime value tier, then by customer name for consistent ordering.

Key Metrics to Calculate:

• Customer Lifetime Value Tier: Classify customers as "Platinum" (>= 75,000), "Gold" (>= 25,000), "Silver" (>= 5,000), or "Bronze" (< 5,000) based on total spending.

• Average Order Value: Total customer spending divided by number of orders (with 2 decimal precision).

• Customer Acquisition Cost Ratio: Calculate as FLOOR((days_since_registration * 2.5) + (total_orders * 10) - (total_spending / 100)).

• Market Share: Percentage of city's total revenue attributed to this customer (customer_revenue / city_total_revenue * 100).

• Category Diversification: Number of distinct categories the customer has purchased from.

• Loyalty Score: Calculate as FLOOR((total_orders * 8) + (category_diversification * 15) - (avg_days_between_orders / 3)) where avg_days_between_orders is purchase_span / (total_orders - 1).

• Profitability Index: (Total spending - (total_orders * 25)) / total_spending * 100 (assuming 25 operational cost per order).

Note: Use a fixed reference date of '2025-08-01' for days_since_registration calculations to ensure consistent results across all database systems.