Advanced Joins

-- Employees table has a manager_id column pointing to another employee's id
-- Self join to get each employee's manager name
SELECT e.first_name || ' ' || e.last_name   AS employee,
       e.job_title,
       m.first_name || ' ' || m.last_name   AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY manager NULLS LAST, employee;

-- Find all employees who earn more than their manager
SELECT e.first_name || ' ' || e.last_name  AS employee,
       e.salary                             AS employee_salary,
       m.first_name || ' ' || m.last_name  AS manager,
       m.salary                             AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary
ORDER BY e.salary DESC;

-- Generate all combinations of product categories and months
-- Useful for building a complete grid with no gaps for reporting
SELECT c.category, m.month_name
FROM (SELECT DISTINCT category FROM products) c
CROSS JOIN (
  VALUES ('January'), ('February'), ('March'),
         ('April'),   ('May'),      ('June')
) AS m(month_name)
ORDER BY c.category, m.month_name;

-- How many combinations does this produce?
SELECT COUNT(DISTINCT category) * 6 AS total_combinations
FROM products;

-- Join orders to a promotions table matching customer AND date range
-- Only apply the promotion if the order falls within the promotion window
SELECT o.id AS order_id,
       o.customer_id,
       o.order_date,
       o.total_amount,
       pr.name         AS promotion,
       pr.discount_pct
FROM orders o
LEFT JOIN promotions pr
  ON o.customer_id = pr.customer_id         -- customer must match
  AND o.order_date BETWEEN pr.start_date    -- and order must fall in window
                       AND pr.end_date
WHERE o.status = 'delivered'
ORDER BY o.order_date DESC
LIMIT 5;

-- Assign a commission tier to each employee based on their salary range
-- salary_tiers is a lookup table: tier_name, min_salary, max_salary
SELECT e.first_name, e.last_name, e.salary,
       t.tier_name,
       t.commission_pct
FROM employees e
JOIN salary_tiers t
  ON e.salary BETWEEN t.min_salary AND t.max_salary
ORDER BY e.salary DESC;

-- Find all orders where total_amount falls within a discount bracket
SELECT o.id, o.total_amount, d.discount_label, d.discount_pct
FROM orders o
JOIN discount_brackets d
  ON o.total_amount >= d.min_order_value
  AND o.total_amount < d.max_order_value
WHERE o.status = 'pending'
ORDER BY o.total_amount DESC
LIMIT 5;

-- A customer has 5 orders. Joining customers to orders gives 5 rows per customer.
-- SUM(o.total_amount) is fine here — we want total spent per customer
SELECT c.first_name, COUNT(o.id) 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, c.first_name
LIMIT 3;

-- But this is a trap — joining orders to order_items inflates order total
-- Each order appears once per line item in order_items
-- SUM(total_amount) without DISTINCT would count the order total for every line item
SELECT o.id,
       o.total_amount                       AS declared_total,
       SUM(oi.quantity * oi.unit_price)     AS calculated_total   -- correct
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.total_amount
ORDER BY o.id
LIMIT 3;

-- Top 5 best-selling products by units sold
SELECT p.name,
       p.category,
       SUM(oi.quantity)                     AS units_sold,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category
ORDER BY units_sold DESC
LIMIT 5;

-- Revenue by customer with their name — joined aggregation
SELECT c.first_name || ' ' || c.last_name AS customer,
       c.state,
       COUNT(DISTINCT o.id)                AS orders,
       ROUND(SUM(o.total_amount), 2)        AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status != 'cancelled'
GROUP BY c.id, c.first_name, c.last_name, c.state
ORDER BY total_spent DESC
LIMIT 5;