alibaba Sales Analytics Schema
Tables
customers
- customer_id (PRIMARY KEY)
- name
- signup_date
- status (active/inactive)
- customer_tier (bronze/silver/gold/platinum)
orders
- order_id (PRIMARY KEY)
- customer_id (FOREIGN KEY -> customers)
- order_date
- status (pending/completed/cancelled/refunded)
- total_amount
- sales_region (north/south/east/west)
order_items
- item_id (PRIMARY KEY)
- order_id (FOREIGN KEY -> orders)
- product_id
- quantity
- unit_price
- discount_percent
Business Logic
Active customers: status = 'active' AND signup_date <= CURRENT_DATE - INTERVAL '90 days'
Revenue calculation: Only count orders with status = 'completed'. Use total_amount from orders table.
High-value orders: Orders with total_amount > 1000
Example Query
-- Get top 10 customers by revenue in the last quarter
SELECT c.customer_id, c.name, c.customer_tier, SUM(o.total_amount) as total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed' AND o.order_date >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY c.customer_id, c.name, c.customer_tier
ORDER BY total_revenue DESC LIMIT 10;