Multiple Aggregates & Complex Grouping: Interview

Why use multiple aggregates in one query instead of separate queries?

Multiple aggregates in one query are faster (single pass), ensure data consistency (same snapshot), and reduce network overhead. Database computes all metrics together efficiently.

How does multi-column grouping change the result set?

Multi-column grouping creates finer-grained groups based on unique combinations. GROUP BY department, job_title creates one row per (department, job_title) pair, typically more rows but more detailed analysis.

Create comprehensive sales report by region and category with multiple metrics

SELECT

region,

category,

COUNT(*) AS total_orders,

SUM(order_total) AS total_revenue,

AVG(order_total) AS avg_order_value,

MIN(order_total) AS min_order,

MAX(order_total) AS max_order

FROM orders

WHERE order_date >= '2024-01-01'

GROUP BY region, category

HAVING COUNT(*) >= 10

ORDER BY total_revenue DESC;

Multi-dimensional analysis with WHERE filtering, complex grouping, multiple aggregates, HAVING threshold, and meaningful ordering.