Joins and Aggregates

-- Sample schema
-- employees: id, name, department_id, salary, manager_id
-- departments: id, name, location, budget

-- INNER JOIN — only rows that match in BOTH tables
SELECT e.name, e.salary, d.name AS dept_name
FROM   employees   e
JOIN   departments d ON e.department_id = d.id;
-- Employees without a department are excluded
-- Departments with no employees are excluded

-- LEFT JOIN (LEFT OUTER JOIN) — all rows from LEFT table
SELECT e.name, d.name AS dept_name
FROM   employees   e
LEFT JOIN departments d ON e.department_id = d.id;
-- Employees without a department: dept_name is NULL
-- All employees are included

-- RIGHT JOIN — all rows from RIGHT table (rarely used; flip tables for LEFT JOIN)
SELECT e.name, d.name AS dept_name
FROM   employees   e
RIGHT JOIN departments d ON e.department_id = d.id;
-- All departments included, even with no employees

-- FULL OUTER JOIN — all rows from both tables
SELECT e.name, d.name AS dept_name
FROM   employees   e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- CROSS JOIN — every combination (Cartesian product)
SELECT e.name, p.project_name
FROM   employees e CROSS JOIN projects p;  -- n × m rows

-- Self-join — join a table to itself
SELECT e.name AS employee, m.name AS manager
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Aggregate functions operate on groups of rows

-- Without GROUP BY: aggregate over entire result set
SELECT
    COUNT(*)                    AS total_employees,  -- count all rows
    COUNT(manager_id)           AS have_manager,     -- count non-null values
    COUNT(DISTINCT department_id) AS num_departments,
    SUM(salary)                 AS payroll,
    AVG(salary)                 AS avg_salary,
    MIN(salary)                 AS lowest,
    MAX(salary)                 AS highest,
    ROUND(AVG(salary), 2)       AS avg_rounded
FROM employees;

-- GROUP BY — one output row per group
SELECT
    department,
    COUNT(*)           AS headcount,
    AVG(salary)        AS avg_salary,
    SUM(salary)        AS dept_payroll,
    MIN(hire_date)     AS earliest_hire
FROM   employees
GROUP  BY department;

-- HAVING — filter groups (runs AFTER GROUP BY)
SELECT
    department,
    COUNT(*)    AS headcount,
    AVG(salary) AS avg_salary
FROM   employees
GROUP  BY department
HAVING COUNT(*) > 5          -- only departments with more than 5 people
AND    AVG(salary) > 70000;  -- and average salary above 70k

-- ORDER in GROUP BY query: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
SELECT department, COUNT(*) AS n
FROM   employees
WHERE  active = TRUE          -- filters rows BEFORE grouping
GROUP  BY department
HAVING COUNT(*) > 3           -- filters groups AFTER grouping
ORDER  BY n DESC;

-- Window function: aggregate over a "window" of rows without collapsing them
-- Syntax: function() OVER (PARTITION BY ... ORDER BY ...)

SELECT
    name,
    department,
    salary,
    -- Rank within department (gaps for ties)
    RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    -- Dense rank (no gaps)
    DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    -- Row number (arbitrary tiebreaker)
    ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    -- Average salary in the same department (all rows kept)
    AVG(salary)   OVER (PARTITION BY department)                      AS dept_avg,
    -- Running total across all employees ordered by salary
    SUM(salary)   OVER (ORDER BY salary)                              AS running_total,
    -- Lag and lead: access previous/next row's value
    LAG(salary, 1)  OVER (ORDER BY hire_date) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary,
    -- NTILE: divide into N groups (percentiles)
    NTILE(4)      OVER (ORDER BY salary)                              AS quartile
FROM employees;

-- Find top earner per department using window function
SELECT name, department, salary
FROM (
    SELECT name, department, salary,
           RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
) ranked
WHERE rk = 1;