Part 60: Real-World Sales Analytics Dashboard Query Project

Let's synthesize all aggregation, grouping, and filtering concepts into an Executive Sales Dashboard.

The Query

SELECT 
  COALESCE(c.region, 'All Regions') AS region,
  COALESCE(p.category, 'All Categories') AS category,
  COUNT(o.id) AS total_orders,
  SUM(o.total_amount) AS gross_revenue,
  ROUND(AVG(o.total_amount), 2) AS average_order_value,
  MIN(o.order_date) AS earliest_order,
  MAX(o.order_date) AS latest_order
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed'
  AND o.order_date >= '2024-01-01'
GROUP BY ROLLUP (c.region, p.category)
HAVING SUM(o.total_amount) > 1000.00
ORDER BY c.region NULLS LAST, gross_revenue DESC;

Key Takeaways

• Produces executive summaries with hierarchical subtotals, grand totals, and KPI metrics.

In Part 61, we enter Module 7 to master Joining Tables: Relational Queries!