SQL Cardinality — Applied EXISTS, LATERAL, Recursive CTEs

-- ✗ NOT IN: orders.customer_id contains NULL, so the result is always empty (trap)
-- WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)

SELECT
  c.customer_id,
  c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id  -- Correlated: confirm that this customer has no order
)
ORDER BY c.customer_id;

/*
  Execution order:
  1. FROM customers AS c           → Evaluate customers one row at a time
  2. WHERE NOT EXISTS (...)        → Keep only customers with zero orders
  3. SELECT c.customer_id, c.name  → Project the 2 columns
  4. ORDER BY c.customer_id        → Sort and output
  */

SELECT
  c.name,
  COUNT(*) FILTER (WHERE o.status = 'completed') AS completed_count,  -- Conditional count by status
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_count,
  COUNT(*) FILTER (WHERE o.status = 'pending')   AS pending_count,
  COALESCE(SUM(o.amount) FILTER (WHERE o.status = 'completed'), 0) AS completed_amount  -- Completed amount total (0 when none)
FROM customers AS c
INNER JOIN orders AS o  -- Expands as 1:N
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

/*
  Execution order:
  1. FROM customers AS c     → Read customers
  2. INNER JOIN orders AS o  → Join (expands as 1:N)
  3. GROUP BY c.customer_id  → Form groups
  4. COUNT/SUM FILTER (...)  → Aggregate by status (pivot)
  5. SELECT                  → Project columns
  6. ORDER BY c.customer_id  → Sort and output
  */

SELECT
  c.category_name,
  t.product_name,
  t.price,
  t.rn
FROM categories AS c
CROSS JOIN LATERAL (
  SELECT
    p.product_name,
    p.price,
    ROW_NUMBER() OVER (ORDER BY p.price DESC) AS rn
  FROM products AS p
  WHERE p.category_id = c.category_id   -- Correlated: limit to products in the outer category
  ORDER BY p.price DESC
  LIMIT 2                          -- Keep the top 2 in each category (1:N → 1:2)
) AS t
ORDER BY c.category_id, t.rn;

/*
  Execution order:
  1. FROM categories AS c          → Take the 2 categories one at a time
  2. CROSS JOIN LATERAL (...)      → Run the subquery for each category c
  3. Join 2 categories with 2 each → 4 total rows (controlled 1:N)
  4. SELECT projects 4 columns
  5. ORDER BY c.category_id, t.rn  → Output by category and ascending rank
  */

WITH RECURSIVE org AS (
  -- Anchor: start from the top-level employee with no manager (President)
  SELECT
    emp_id, name, manager_id,
    1 AS level,
    name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- Recursive term: add direct reports of the previous org result one level at a time
  SELECT
    e.emp_id, e.name, e.manager_id,
    o.level + 1,
    o.path || ' > ' || e.name
  FROM employees AS e
  INNER JOIN org AS o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, level, path
FROM org
ORDER BY level, emp_id;

/*
  Execution order (recursive flow):
  1. Run the anchor             → Generate the starting row (President)
  2. Recursive term, iteration 1 → Expand children
  3. Recursive term, iteration 2 → Expand children
  4. Recursive term, iteration 3 → Expand children
  5. Recursive term, iteration 4 → Stop with no new rows
  6. SELECT ... ORDER BY        → Sort and output
  */