DataCorp Analytics Emergency - Activity 06 🚨

docker-compose up -d
docker exec -it w4-postgres psql -U postgres datacorp_db

-- Departments: Budget and location info
departments (department_id, name, budget, location)

-- Employees: Full org chart
employees (employee_id, name, department, job_title, salary, hire_date, manager_id, performance_rating)

-- Sales: Q1-Q2 2024 records
sales (sale_id, employee_id, amount, sale_date, region)
  └── region: 'Northeast', 'West', 'Midwest', 'South'

-- Scalar Subquery: Employees above company average
SELECT name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated Subquery: Above department average
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
);

-- CTE: Department salary analysis
WITH dept_stats AS (
    SELECT department,
           AVG(salary) as avg_salary,
           MAX(salary) as max_salary,
           COUNT(*) as headcount
    FROM employees
    GROUP BY department
)
SELECT * FROM dept_stats
ORDER BY avg_salary DESC;

-- Multiple CTEs: Top performer per department
WITH dept_avg AS (
    SELECT department, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
),
top_performers AS (
    SELECT e.name, e.department, e.salary,
           ROW_NUMBER() OVER (PARTITION BY e.department ORDER BY e.salary DESC) as rank
    FROM employees e
)
SELECT tp.name, tp.department, tp.salary, da.avg_salary
FROM top_performers tp
JOIN dept_avg da ON tp.department = da.department
WHERE tp.rank = 1;

-- EXISTS vs IN comparison
-- Using EXISTS (stops at first match - faster for large tables)
SELECT e.name, e.department
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM sales s
    WHERE s.employee_id = e.employee_id
    AND s.amount > 50000
);

WITH cte_name AS (
    -- Your query here
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;