Basic JOIN with COUNT - Customer Order Counts

basic

Count orders per customer, including customers with zero orders

-- Wrong: INNER JOIN excludes customers without orders

SELECT c.customer_name, COUNT(o.order_id) as order_count

FROM customers c

INNER JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id, c.customer_name;

-- Result: Only customers with orders

-- Correct: LEFT JOIN includes all customers

SELECT c.customer_name, COUNT(o.order_id) as order_count

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id, c.customer_name

ORDER BY order_count DESC;

Shows all customers with their order counts, including customers with 0 orders

LEFT JOIN preserves all customers. COUNT(o.order_id) ignores NULL values from customers without orders.

All

Multiple Aggregations with HAVING - Sales Analysis

intermediate

Calculate sales metrics by product category, filter for high-performing categories

-- Multiple aggregations with filtering

SELECT

cat.category_name,

COUNT(oi.order_item_id) as items_sold,

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

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

COUNT(DISTINCT o.order_id) as unique_orders

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 >= '2024-01-01' OR o.order_date IS NULL

GROUP BY cat.category_id, cat.category_name

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

ORDER BY total_revenue DESC NULLS LAST;

Shows category performance metrics for high-revenue categories and categories with no sales

Multiple LEFT JOINs with various aggregations. HAVING filters on aggregated values. NULL handling for categories without sales.

All

Complex Aggregation - Employee Performance Dashboard