Exercises

-- ===== Schema =====
CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  country TEXT NOT NULL
);
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id),
  total NUMERIC(10,2) NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ===== Exercise 1: total per customer =====
-- Return name + total spent, sorted descending.

SELECT c.name, COALESCE(SUM(o.total), 0) AS total
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total DESC;

-- ===== Exercise 2: top customers per country =====
-- For each country, return the top 3 customers by total spent.
-- (Window function!)

SELECT * FROM (
  SELECT
    c.country,
    c.name,
    COALESCE(SUM(o.total), 0) AS spent,
    DENSE_RANK() OVER (PARTITION BY c.country ORDER BY COALESCE(SUM(o.total), 0) DESC) AS rnk
  FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.country, c.id, c.name
) t WHERE rnk <= 3;

-- ===== Exercise 3: 30-day rolling sum =====
-- For each day in the last 30 days, return the day + the sum of orders that day.

SELECT
  d::date AS day,
  COALESCE(SUM(o.total), 0) AS total
FROM generate_series(now() - INTERVAL '29 days', now(), INTERVAL '1 day') d
LEFT JOIN orders o ON DATE(o.created_at) = d::date
GROUP BY d
ORDER BY day;

-- ===== Exercise 4: latest status per customer =====
-- For each customer, return the status of their LATEST order.

SELECT DISTINCT ON (customer_id)
  customer_id, status, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

-- DISTINCT ON is a Postgres extension; super-handy here.

-- ===== Exercise 5: JSON query =====
-- Add a 'data JSONB' column. Find customers where data->>'tier' = 'gold'.

ALTER TABLE customers ADD COLUMN data JSONB NOT NULL DEFAULT '{}';

UPDATE customers SET data = '{"tier":"gold"}' WHERE id IN (1, 2);

SELECT name FROM customers WHERE data->>'tier' = 'gold';

-- Index that path:
CREATE INDEX customers_data_tier ON customers ((data->>'tier'));

-- ===== Exercise 6: CTE + recursion =====
-- An 'employees' table with manager_id. Return all reports under id=1, with depth.

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id)
);

WITH RECURSIVE tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

-- ===== Patterns =====
-- - LEFT JOIN + COALESCE to keep customers with zero orders
-- - DENSE_RANK / ROW_NUMBER for top-N per group
-- - generate_series for date axes
-- - DISTINCT ON for latest-per-key
-- - JSONB with expression index for searchable paths
-- - WITH RECURSIVE for trees / graphs

-- ===== Pitfalls =====
-- - INNER JOIN drops customers with no orders
-- - GROUP BY missing non-aggregated columns
-- - Date subtraction returning INTERVAL instead of INT
-- - JSONB path queries without index -> seq scan

-- Fill in: SELECT ____ FROM users;