Common Table Expressions (CTEs)

-- Basic CTE: calculate customer order totals, then filter
WITH customer_totals AS (
  SELECT customer_id,
         COUNT(*)                    AS order_count,
         ROUND(SUM(total_amount), 2) AS total_spent
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY customer_id
)
SELECT customer_id, order_count, total_spent
FROM customer_totals
WHERE total_spent > 300
ORDER BY total_spent DESC;

-- Same query as a subquery — compare readability
SELECT customer_id, order_count, total_spent
FROM (
  SELECT customer_id,
         COUNT(*)                    AS order_count,
         ROUND(SUM(total_amount), 2) AS total_spent
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 300
ORDER BY total_spent DESC;

-- CTE + JOIN: enrich customer totals with actual customer names
WITH customer_totals AS (
  SELECT customer_id,
         COUNT(*)                    AS order_count,
         ROUND(SUM(total_amount), 2) AS total_spent
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY customer_id
)
SELECT c.first_name || ' ' || c.last_name AS customer,
       c.state,
       ct.order_count,
       ct.total_spent
FROM customer_totals ct
JOIN customers c ON ct.customer_id = c.id
ORDER BY ct.total_spent DESC
LIMIT 5;

-- Step 1: revenue per product
-- Step 2: rank products within their category
-- Step 3: return only the top product per category
WITH product_revenue AS (
  SELECT p.id,
         p.name,
         p.category,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  GROUP BY p.id, p.name, p.category
),
category_top AS (
  SELECT *,
         RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_in_category
  FROM product_revenue
)
SELECT name, category, ROUND(revenue, 2) AS revenue
FROM category_top
WHERE rank_in_category = 1
ORDER BY revenue DESC;

-- Use a CTE to identify customers to promote, then update their tier
WITH high_spenders AS (
  SELECT customer_id
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY customer_id
  HAVING SUM(total_amount) > 500
)
UPDATE customers
SET tier = 'gold'
WHERE id IN (SELECT customer_id FROM high_spenders);

-- Use a CTE to find old cancelled orders, then delete them
WITH old_cancelled AS (
  SELECT id FROM orders
  WHERE status = 'cancelled'
    AND order_date < CURRENT_DATE - INTERVAL '1 year'
)
DELETE FROM orders
WHERE id IN (SELECT id FROM old_cancelled);

-- Use a CTE to build insert data from existing data
WITH new_vip AS (
  SELECT id, first_name, last_name, email
  FROM customers
  WHERE tier = 'gold'
)
INSERT INTO vip_customers (id, first_name, last_name, email)
SELECT id, first_name, last_name, email
FROM new_vip
ON CONFLICT (id) DO NOTHING;

-- Traverse the employee management hierarchy from the top down
WITH RECURSIVE org_chart AS (
  -- Anchor: start with the CEO (no manager)
  SELECT id,
         first_name || ' ' || last_name AS name,
         job_title,
         manager_id,
         0 AS level,
         first_name || ' ' || last_name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member: find each employee's direct reports
  SELECT e.id,
         e.first_name || ' ' || e.last_name,
         e.job_title,
         e.manager_id,
         oc.level + 1,
         oc.path || ' > ' || e.first_name || ' ' || e.last_name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level,
       REPEAT('  ', level) || name AS employee,   -- indent by level
       job_title,
       path
FROM org_chart
ORDER BY path;