guanxuc/sales_analytics

Sales Analytics Schema

Tables

customers

• customer_id (PRIMARY KEY)
• name
• email
• 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;