#!/home/rosy/bin/sql-run --show-script --color=always

-- Let's explore how JOINs work using customers and orders

-- First, see what customers look like
SELECT id, name, region FROM customers LIMIT 3;

-- Now see some orders
SELECT id, customer_id, total_amount FROM orders LIMIT 3;

-- JOIN connects them: each order matched to its customer
SELECT c.name, o.total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
LIMIT 5;

-- Aggregate: total spent per customer
SELECT c.name, COUNT(*) as orders, SUM(o.total_amount) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY total_spent DESC
LIMIT 5;
