Every business report combines joins and aggregations: customer order counts, product sales totals, employee performance metrics, department budgets. This is the foundation of analytics.

E-commerce Sales Dashboard

Business needs comprehensive sales dashboard showing product performance, customer segments, and revenue metrics across multiple dimensions for executive reporting.

Complex JOIN-aggregation queries combining products, orders, customers, and categories

-- Executive sales dashboard with multiple metrics

SELECT

cat.category_name,

COUNT(DISTINCT p.product_id) as products_in_category,

COUNT(DISTINCT o.customer_id) as unique_customers,

COUNT(o.order_id) as total_orders,

SUM(oi.quantity) as units_sold,

SUM(oi.quantity * oi.unit_price) as gross_revenue,

AVG(oi.quantity * oi.unit_price) as avg_order_item_value,

SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.customer_id) as revenue_per_customer,

COUNT(CASE WHEN o.total_amount > 500 THEN 1 END) as high_value_orders,

ROUND(

COUNT(CASE WHEN o.total_amount > 500 THEN 1 END) * 100.0 /

COUNT(o.order_id), 2

) as high_value_order_percentage

FROM categories cat

LEFT JOIN products p ON cat.category_id = p.category_id

LEFT JOIN order_items oi ON p.product_id = oi.product_id

LEFT JOIN orders o ON oi.order_id = o.order_id

WHERE o.order_date >= CURRENT_DATE - INTERVAL 90 DAY OR o.order_date IS NULL

GROUP BY cat.category_id, cat.category_name

HAVING SUM(oi.quantity * oi.unit_price) > 1000 OR SUM(oi.quantity * oi.unit_price) IS NULL

ORDER BY gross_revenue DESC NULLS LAST;

Enables data-driven decisions on product mix, inventory planning, and marketing focus. Identifies high-performing categories and customer behavior patterns.

All

Customer Lifetime Value Analysis

Marketing team needs to segment customers by value, purchase frequency, and engagement metrics to optimize retention campaigns and identify VIP customers.

Multi-dimensional customer aggregation with behavioral scoring

-- Customer segmentation with lifetime value metrics

SELECT

c.customer_id,

c.customer_name,

c.registration_date,

COUNT(o.order_id) as total_orders,

COALESCE(SUM(o.total_amount), 0) as lifetime_value,

COALESCE(AVG(o.total_amount), 0) as avg_order_value,