CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  country TEXT
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date DATE,
  status TEXT,
  amount DECIMAL(10, 2)
);

SELECT
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount,
  AVG(amount) AS average_amount
FROM orders
WHERE status = 'paid';

SELECT
  c.customer_id,
  c.name,
  SUM(o.amount) AS total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY total_amount DESC;

SELECT
  c.customer_id,
  c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

WITH customer_sales AS (
  SELECT
    customer_id,
    SUM(amount) AS total_amount
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT
  c.name,
  s.total_amount
FROM customer_sales AS s
INNER JOIN customers AS c
  ON s.customer_id = c.customer_id
ORDER BY s.total_amount DESC
LIMIT 10;