-- Aliases are REQUIRED — without them MySQL cannot distinguish the two table references
SELECT
  e.employee_id,
  e.full_name        AS employee_name,
  m.full_name        AS manager_name
FROM employees AS e           -- 'e' represents the employee
JOIN employees AS m           -- 'm' represents the manager
  ON e.manager_id = m.employee_id
ORDER BY m.full_name, e.full_name;

CREATE TABLE employees (
  employee_id   INT          PRIMARY KEY AUTO_INCREMENT,
  full_name     VARCHAR(100) NOT NULL,
  job_title     VARCHAR(100),
  department    VARCHAR(50),
  manager_id    INT          NULL,           -- NULL for the top of the hierarchy
  salary        DECIMAL(10,2),
  FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, full_name, job_title, department, manager_id, salary)
VALUES
  (1,  'Diana Prince',   'CEO',               'Executive',  NULL, 250000),
  (2,  'Bruce Wayne',    'CTO',               'Technology',    1, 180000),
  (3,  'Clark Kent',     'CFO',               'Finance',        1, 175000),
  (4,  'Barry Allen',    'Engineering Lead',  'Technology',    2, 140000),
  (5,  'Hal Jordan',     'Engineering Lead',  'Technology',    2, 138000),
  (6,  'Arthur Curry',   'Senior Engineer',   'Technology',    4, 115000),
  (7,  'Victor Stone',   'Senior Engineer',   'Technology',    4, 112000),
  (8,  'Oliver Queen',   'Finance Analyst',   'Finance',        3,  95000);

-- All employees with their direct manager's name
-- CEO (Diana) has no manager, so use LEFT JOIN to include her row
SELECT
  e.employee_id,
  e.full_name       AS employee,
  e.job_title,
  e.department,
  COALESCE(m.full_name, '(No Manager)') AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_id
ORDER BY e.department, e.full_name;

-- Three-level chain: VP → Manager → Individual Contributor
SELECT
  vp.full_name         AS vp,
  mgr.full_name        AS manager,
  ic.full_name         AS contributor,
  ic.salary
FROM employees AS ic
JOIN employees AS mgr ON ic.manager_id    = mgr.employee_id
JOIN employees AS vp  ON mgr.manager_id   = vp.employee_id
ORDER BY vp.full_name, mgr.full_name, ic.full_name;

-- Find all subordinates of Bruce Wayne (2 levels: direct + indirect reports)
SELECT
  mgr.full_name    AS manager,
  direct.full_name AS direct_report,
  COALESCE(indirect.full_name, '(none)') AS indirect_report
FROM employees AS mgr
JOIN employees AS direct   ON direct.manager_id   = mgr.employee_id
LEFT JOIN employees AS indirect ON indirect.manager_id = direct.employee_id
WHERE mgr.employee_id = 2  -- Bruce Wayne
ORDER BY direct.full_name, indirect.full_name;

-- Recursive CTE: all reports under Bruce Wayne at any depth
WITH RECURSIVE org AS (
  -- Anchor: start with Bruce Wayne
  SELECT employee_id, full_name, manager_id, job_title, 1 AS depth,
         full_name AS path
  FROM employees
  WHERE employee_id = 2

  UNION ALL

  -- Recursive: add each person whose manager is already in org
  SELECT e.employee_id, e.full_name, e.manager_id, e.job_title,
         org.depth + 1,
         CONCAT(org.path, ' -> ', e.full_name)
  FROM employees e
  JOIN org ON e.manager_id = org.employee_id
)
SELECT full_name, job_title, depth, path
FROM org
ORDER BY depth, full_name;

-- Employees who earn more than their direct manager
SELECT
  e.full_name     AS employee,
  e.salary        AS employee_salary,
  m.full_name     AS manager,
  m.salary        AS manager_salary,
  e.salary - m.salary AS premium
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary
ORDER BY premium DESC;

-- Peer salary comparison: all pairs in the same department
-- a.id < b.id avoids symmetric duplicates and self-pairs
SELECT
  a.full_name     AS emp_a,
  a.salary        AS salary_a,
  b.full_name     AS emp_b,
  b.salary        AS salary_b,
  ABS(a.salary - b.salary) AS gap
FROM employees AS a
JOIN employees AS b
  ON  a.department  = b.department
  AND a.employee_id < b.employee_id
ORDER BY a.department, gap DESC;

-- Customers registered twice with the same email
SELECT
  a.customer_id AS id_1,
  b.customer_id AS id_2,
  a.email,
  a.created_at  AS registered_first,
  b.created_at  AS registered_second
FROM customers AS a
JOIN customers AS b
  ON  a.email       = b.email
  AND a.customer_id < b.customer_id   -- prevent (row1, row2) AND (row2, row1)
ORDER BY a.email;

-- Products with the same name appearing in multiple categories
SELECT
  a.product_id AS id_1,
  b.product_id AS id_2,
  a.name,
  a.category_id AS cat_1,
  b.category_id AS cat_2
FROM products AS a
JOIN products AS b
  ON  a.name       = b.name
  AND a.product_id < b.product_id
  AND a.category_id <> b.category_id;

-- Orders within the same customer placed within 7 days of each other
SELECT
  a.customer_id,
  a.order_id   AS first_order,
  a.created_at AS first_date,
  b.order_id   AS next_order,
  b.created_at AS next_date,
  DATEDIFF(b.created_at, a.created_at) AS days_between
FROM orders AS a
JOIN orders AS b
  ON  a.customer_id = b.customer_id
  AND b.created_at  > a.created_at
  AND b.created_at  <= DATE_ADD(a.created_at, INTERVAL 7 DAY)
ORDER BY a.customer_id, a.created_at;

-- MySQL 8.0+ alternative using LAG() window function (cleaner):
SELECT
  customer_id, order_id, created_at,
  LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_order_date,
  DATEDIFF(created_at, LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at)) AS days_since_last
FROM orders;

CREATE TABLE parts (
  part_id     INT PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(100) NOT NULL,
  parent_id   INT NULL,
  quantity    INT DEFAULT 1,
  FOREIGN KEY (parent_id) REFERENCES parts(part_id)
);

INSERT INTO parts VALUES
  (1, 'Bicycle',        NULL, 1),
  (2, 'Frame',          1,    1),
  (3, 'Wheel Assembly', 1,    2),
  (4, 'Tire',           3,    1),
  (5, 'Rim',            3,    1),
  (6, 'Spokes',         5,   32);

-- List each part with its direct parent
SELECT
  child.name     AS part,
  parent.name    AS component_of,
  child.quantity
FROM parts AS child
LEFT JOIN parts AS parent ON child.parent_id = parent.part_id
ORDER BY parent.name, child.name;

-- Recursive CTE: all components of a bicycle at every level
WITH RECURSIVE bom AS (
  SELECT part_id, name, parent_id, quantity, 0 AS level
  FROM parts WHERE part_id = 1
  UNION ALL
  SELECT p.part_id, p.name, p.parent_id, p.quantity, bom.level + 1
  FROM parts p JOIN bom ON p.parent_id = bom.part_id
)
SELECT CONCAT(REPEAT('  ', level), name) AS component, quantity FROM bom;

-- Products that appear together in the same order
-- Self-join order_items to find co-purchased pairs
SELECT
  a.product_id AS product_1,
  b.product_id AS product_2,
  COUNT(*)     AS times_bought_together
FROM order_items AS a
JOIN order_items AS b
  ON  a.order_id   = b.order_id
  AND a.product_id < b.product_id   -- avoid duplicates and self-pairs
GROUP BY a.product_id, b.product_id
HAVING COUNT(*) >= 5                -- only meaningful co-purchase pairs
ORDER BY times_bought_together DESC
LIMIT 20;

-- Find employee ID gaps (IDs that exist without a next consecutive ID)
SELECT a.employee_id AS gap_start, MIN(b.employee_id) - 1 AS gap_end
FROM employees AS a
JOIN employees AS b ON b.employee_id > a.employee_id
WHERE NOT EXISTS (
  SELECT 1 FROM employees c
  WHERE c.employee_id = a.employee_id + 1
)
GROUP BY a.employee_id
ORDER BY a.employee_id;

-- Simpler: find pairs where there is no row between them
SELECT a.employee_id, b.employee_id AS next_id,
       b.employee_id - a.employee_id - 1 AS gap_size
FROM employees AS a
JOIN employees AS b ON b.employee_id = (
  SELECT MIN(e.employee_id) FROM employees e
  WHERE e.employee_id > a.employee_id
)
WHERE b.employee_id - a.employee_id > 1;

-- Build the full reporting chain path for every employee
WITH RECURSIVE path AS (
  -- Anchor: top-level employees (no manager)
  SELECT employee_id, full_name, manager_id,
         full_name AS chain,
         0 AS depth
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: append employee name to the chain
  SELECT e.employee_id, e.full_name, e.manager_id,
         CONCAT(p.chain, ' > ', e.full_name),
         p.depth + 1
  FROM employees e
  JOIN path p ON e.manager_id = p.employee_id
)
SELECT employee_id, full_name, depth, chain AS reporting_path
FROM path
ORDER BY chain;

-- Index the join column (manager_id) on the employees table
CREATE INDEX idx_manager_id ON employees (manager_id);

-- EXPLAIN verifies index usage
EXPLAIN
SELECT e.full_name AS employee, m.full_name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_idG
-- type for 'm': eq_ref (uses PRIMARY KEY on employee_id)
-- type for 'e': ALL or ref depending on query predicates

-- For finding duplicates, index the column you join on
CREATE INDEX idx_email ON customers (email);

EXPLAIN
SELECT a.customer_id, b.customer_id, a.email
FROM customers AS a
JOIN customers AS b ON a.email = b.email AND a.customer_id < b.customer_idG
-- type: ref (uses idx_email for the inner table)