Advanced SQL Queries PDF Downloads

-- Example: employees earning above their department average (tested)
-- A CTE computes each department's average, then a join compares each employee against it:

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

-- Example: Conditional Aggregation & Pivoting with CASE (tested)
-- Pivoting monthly orders into columns without database-specific PIVOT syntax:

SELECT 
  customer_id,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
  SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount,
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS total_orders
FROM orders
GROUP BY customer_id;

-- Example: Recursive CTE traversing employee manager hierarchy (tested)
-- Recursively walking from CEO down through team reporting chains:

WITH RECURSIVE emp_hierarchy AS (
  -- Anchor member: top-level managers
  SELECT employee_id, name, manager_id, 1 AS org_level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive member: direct reports
  SELECT e.employee_id, e.name, e.manager_id, h.org_level + 1
  FROM employees e
  JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM emp_hierarchy ORDER BY org_level, employee_id;