Гайд по SQL

SELECT name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;

-- Несколько условий с AND/OR
SELECT name, department, salary
FROM employees
WHERE salary >= 90000
  AND department IN ('Engineering', 'Sales')
ORDER BY department, salary DESC;

-- BETWEEN для диапазонов
SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31'
ORDER BY hire_date;

-- LIKE для поиска по паттерну
SELECT name
FROM employees
WHERE name LIKE 'А%';  -- начинается с 'А'

-- Уникальные отделы
SELECT DISTINCT department
FROM employees;

-- Топ-3 по зарплате
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-- Результат: 4 × 3 = 12 строк

-- Найти сотрудников и их менеджеров
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- Отделы с средней зарплатой > 90000
SELECT
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 90000
ORDER BY avg_salary DESC;

-- WHERE + HAVING вместе
SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE salary > 80000       -- фильтр строк ДО группировки
GROUP BY department
HAVING COUNT(*) >= 2       -- фильтр групп ПОСЛЕ группировки
ORDER BY avg_salary DESC;

-- Количество сотрудников по отделу и году найма
SELECT
    department,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    COUNT(*) AS hired
FROM employees
GROUP BY department, EXTRACT(YEAR FROM hire_date)
ORDER BY department, hire_year;

-- Сотрудники с зарплатой выше средней
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Сотрудники из отделов с > 5 людьми
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 5
);

-- Зарплата и средняя по отделу (коррелированный подзапрос)
SELECT
    name,
    salary,
    department,
    (SELECT AVG(salary)
     FROM employees 
     WHERE .department = .department) AS dept_avg
FROM employees ;

-- Отделы, в которых есть хотя бы один сотрудник с зарплатой > 100k
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
      AND e.salary > 100000
);

-- Отделы БЕЗ сотрудников
SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
);

-- Средняя зарплата по отделам + сравнение с общей средней
WITH dept_stats AS (
    SELECT
        department,
        AVG(salary) AS avg_salary,
        COUNT(*) AS emp_count
    FROM employees
    GROUP BY department
),
overall AS (
    SELECT AVG(salary) AS company_avg
    FROM employees
)
SELECT
    ds.department,
    ds.avg_salary,
    ds.emp_count,
    o.company_avg,
    ROUND(ds.avg_salary - o.company_avg) AS diff_from_avg
FROM dept_stats ds
CROSS JOIN overall o
ORDER BY ds.avg_salary DESC;

-- Иерархия сотрудников (менеджер → подчинённые)
WITH RECURSIVE org_tree AS (
    -- Базовый случай: CEO (нет менеджера)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивный шаг
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    INNER JOIN org_tree t ON e.manager_id = t.id
)
SELECT
    REPEAT('  ', level - 1) || name AS org_chart,
    level
FROM org_tree
ORDER BY level, name;

функция() OVER (
    PARTITION BY ...   -- разбиение на группы (необязательно)
    ORDER BY ...       -- сортировка внутри окна
    ROWS/RANGE BETWEEN ... AND ...  -- рамка окна (необязательно)
)

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

SELECT
    name,
    department,
    salary,
    LAG(salary, 1)  OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary,
    LEAD(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary,
    salary - LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC) AS diff
FROM employees;

SELECT
    name,
    department,
    salary,
    -- Средняя зарплата по отделу
    ROUND(AVG(salary) OVER (PARTITION BY department)) AS dept_avg,
    -- Нарастающий итог по отделу
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- Доля от общей зарплаты отдела
    ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

-- Скользящее среднее за 3 строки
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS 
FROM daily_revenue;

-- Нарастающий итог с начала до текущей строки
SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sum

-- Всё в партиции (общая сумма)
SUM(amount) OVER (
    PARTITION BY category
    -- без ORDER BY = вся партиция
) AS category_total