SQL Практика



SQL Практика 2
ответы Java

Практика 2 — Агрегации, JOIN, Подзапросы, CTE
Постарайся решить задачи самостоятельно, не подсматривай в ответы
Агрегатные функции
Задача 1: Посчитать количество пользователей
SELECT COUNT(*) FROM users;
Задача 2: Найти среднюю цену товаров
SELECT AVG(price) FROM products;
Задача 3: Найти минимальную и максимальную цену товаров
SELECT MIN(price), MAX(price) FROM products;
Задача 4: Посчитать общую сумму всех заказов
SELECT SUM(amount) FROM orders;
Задача 5: Посчитать количество пользователей в каждом городе
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
ORDER BY user_count DESC;
Задача 6: Найти среднюю цену товаров по категориям
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;
Задача 7: Найти категории с более чем 10 товарами
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;
Задача 8: Посчитать сумму заказов по статусам
SELECT status, SUM(amount) AS total, COUNT(*) AS order_count
FROM orders
GROUP BY status;
Задача 9: Посчитать количество заказов по месяцам
SELECT
   DATE_TRUNC('month', order_date) AS month,
   COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
Задача 10: Посчитать количество заказов по статусам в столбцы (Pivot)
SELECT
   COUNT(*) AS total,
   SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
   SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
   SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
   SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

-- Или через FILTER (PostgreSQL):
SELECT
   COUNT(*) AS total,
   COUNT(*) FILTER (WHERE status = 'pending') AS pending,
   COUNT(*) FILTER (WHERE status = 'completed') AS completed
FROM orders;
Задача 11: Медиана зарплат
-- PostgreSQL:
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees;
JOIN
Задача 12: Вывести заказы с именами пользователей
SELECT o.id, o.order_date, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;
Задача 13: Найти всех пользователей и их заказы (включая тех, у кого нет заказов)
SELECT u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
Задача 14: Вывести позиции заказа с названиями товаров
SELECT oi.order_id, p.name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p ON oi.product_id = p.id;
Задача 15: Полная информация о заказе (пользователь + товары)
SELECT
   u.name AS customer,
   o.id AS order_id,
   o.order_date,
   p.name AS product,
   oi.quantity,
   oi.unit_price,
   oi.quantity * oi.unit_price AS item_total
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
ORDER BY o.id, p.name;
Задача 16: Вывести все комбинации категорий товаров и городов пользователей (CROSS JOIN)
Нужно получить все возможные пары (категория товара, город пользователя) — даже если таких заказов не было. Это полезно, например, для отчёта: «в каких городах какие категории не продаются».
SELECT
    p.category,
    u.city
FROM
    products p
    LEFT JOIN order_items oi ON oi.product_id = p.id
    LEFT JOIN orders o ON oi.order_id = o.id
    LEFT JOIN users u ON u.id = o.user_id
GROUP BY
    p.category,
    u.city
ORDER BY
    p.category,
    u.city;
Задача 17: Сотрудники, которые зарабатывают больше своего менеджера
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
Задача 18: Вывести для каждого пользователя: количество заказов, общую сумму и средний чек
Включить пользователей без заказов (у них должны быть нули). Отсортировать по общей сумме убыванию.
SELECT
   u.name,
   COUNT(o.id) AS order_count,
   COALESCE(SUM(o.amount), 0) AS total_spent,
   COALESCE(AVG(o.amount), 0) AS avg_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;
Задача 19: Найти пользователей, которые покупали товары категории «Электроника»
SELECT DISTINCT u.id, u.name, u.email
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE p.category = 'Электроника';
Задача 20: Статистика продаж: пользователь → количество уникальных товаров
SELECT
   u.name,
   COUNT(DISTINCT o.id) AS order_count,
   COUNT(DISTINCT oi.product_id) AS unique_products,
   SUM(oi.quantity) AS total_items
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.name
ORDER BY unique_products DESC;
Anti-Join и Semi-Join
Задача 21: Клиенты без заказов (Anti-Join)
-- Способ 1: LEFT JOIN + IS NULL
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Способ 2: NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Способ 3: NOT IN (осторожно с NULL!)
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
Задача 22: Товары, которые заказывали (Semi-Join)
-- Способ 1: EXISTS
SELECT * FROM products p
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

-- Способ 2: IN
SELECT * FROM products
WHERE id IN (SELECT product_id FROM order_items);
Задача 23: Найти товары, которые никто не заказывал
-- ❌ ОПАСНО (если есть NULL — вернёт 0 строк!):
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);

-- ✅ БЕЗОПАСНО вариант 1 — фильтровать NULL:
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM order_items WHERE product_id IS NOT NULL);

-- ✅ БЕЗОПАСНО вариант 2 — NOT EXISTS (рекомендуется):
SELECT * FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

-- ✅ БЕЗОПАСНО вариант 3 — LEFT JOIN:
SELECT p.*
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;
Задача 24: Пользователи, у которых ВСЕ заказы выполнены
-- Логика: НЕ СУЩЕСТВУЕТ заказов НЕ со статусом 'completed'
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)  -- есть заказы
 AND NOT EXISTS (
   SELECT 1 FROM orders o
   WHERE o.user_id = u.id AND o.status != 'completed'
);

-- Альтернатива через GROUP BY + HAVING:
SELECT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING COUNT(*) = COUNT(CASE WHEN o.status = 'completed' THEN 1 END);
Подзапросы
Задача 25: Найти заказы с суммой выше средней
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
Задача 26: Найти самый дорогой товар
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);
Задача 27: Найти последний заказ каждого пользователя
Для каждого пользователя вывести его самый свежий заказ (все поля). Если у пользователя несколько заказов в один день — вывести один.
-- Способ 1: Коррелированный подзапрос
SELECT * FROM orders o1
WHERE o1.id = (
   SELECT o2.id
   FROM orders o2
   WHERE o2.user_id = o1.user_id
   ORDER BY o2.order_date DESC, o2.id DESC
   LIMIT 1
);

-- Способ 2: DISTINCT ON (PostgreSQL, гарантирует одну строку на user_id)
SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, order_date DESC;

-- Способ 3: ROW_NUMBER (стандартный SQL)
SELECT * FROM (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
   FROM orders
) t WHERE rn = 1;
Задача 28: Найти пользователей, сделавших заказ
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);
Задача 29: Найти пары товаров, которые чаще всего покупают в одном заказе
Вывести топ-10 пар товаров, которые встречаются вместе в одном заказе. Для каждой пары показать названия товаров и сколько раз они были в одном заказе. Пара (A, B) и (B, A) — это одно и то же, дубликатов быть не должно.
SELECT
   p1.name AS product1,
   p2.name AS product2,
   COUNT(*) AS times_together
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.id
JOIN products p2 ON oi2.product_id = p2.id
GROUP BY p1.id, p1.name, p2.id, p2.name
ORDER BY times_together DESC
LIMIT 10;
CTE (Common Table Expressions)
Задача 30: Сотрудники с зарплатой выше средней в своём отделе
WITH dept_avg AS (
   SELECT department_id, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;
Задача 31: Клиенты с суммой заказов выше средней
WITH customer_totals AS (
   SELECT user_id, SUM(amount) AS total
   FROM orders
   GROUP BY user_id
)
SELECT u.name, ct.total
FROM users u
JOIN customer_totals ct ON u.id = ct.user_id
WHERE ct.total > (SELECT AVG(total) FROM customer_totals);
Задача 32: Замена коррелированного подзапроса на JOIN с CTE
-- Плохо (коррелированный, O(N²)):
SELECT * FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

-- Хорошо (JOIN, O(N)):
WITH dept_stats AS (
   SELECT department_id, AVG(salary) AS avg_sal
   FROM employees GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN dept_stats ds ON e.department_id = ds.department_id
WHERE e.salary > ds.avg_sal;
Задача 33: Рекурсивный CTE — все подчинённые менеджера
WITH RECURSIVE subordinates AS (
   SELECT id, name, manager_id, 0 AS level
   FROM employees
   WHERE id = 1  -- ID менеджера

   UNION ALL

   SELECT e.id, e.name, e.manager_id, s.level + 1
   FROM employees e
   JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates WHERE level > 0;
Задача 34: Иерархия с путём
WITH RECURSIVE tree AS (
   SELECT id, name, manager_id, ARRAY[name] AS path, 0 AS level
   FROM employees
   WHERE manager_id IS NULL

   UNION ALL

   SELECT e.id, e.name, e.manager_id, (t.path || e.name)::VARCHAR(100)[], t.level + 1
   FROM employees e
   JOIN tree t ON e.manager_id = t.id
)
SELECT id, name, level, array_to_string(path, ' → ') AS hierarchy
FROM tree;
GROUPING SETS, ROLLUP, CUBE
Задача 35: Отчёт с подитогами по категориям и месяцам
SELECT
   COALESCE(p.category, 'ИТОГО') AS category,
   COALESCE(TO_CHAR(o.order_date, 'YYYY-MM'), 'Все месяцы') AS month,
   COUNT(*) AS order_count,
   SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY ROLLUP (p.category, TO_CHAR(o.order_date, 'YYYY-MM'))
ORDER BY
   GROUPING(p.category),
   p.category,
   GROUPING(TO_CHAR(o.order_date, 'YYYY-MM')),
   month;
Задача 36: Сводная таблица продаж по городам и статусам
SELECT
   CASE WHEN GROUPING(u.city) = 1 THEN 'ВСЕ ГОРОДА' ELSE u.city END AS city,
   CASE WHEN GROUPING(o.status) = 1 THEN 'ВСЕ СТАТУСЫ' ELSE o.status END AS status,
   COUNT(*) AS orders,
   SUM(o.amount) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
GROUP BY CUBE (u.city, o.status)
ORDER BY
   GROUPING(u.city, o.status),
   u.city NULLS LAST,
   o.status NULLS LAST;
Комплексные задачи
Задача 37: Найти города с более чем 100 пользователями и средним возрастом > 25
SELECT city, COUNT(*) AS user_count, AVG(age) AS avg_age
FROM users
GROUP BY city
HAVING COUNT(*) > 100 AND AVG(age) > 25;
Задача 38: Топ-5 категорий по общей стоимости товаров на складе
SELECT category, SUM(price * stock) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC
LIMIT 5;
Задача 39: Найти пользователей, не делавших заказы более 90 дней
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '90 days'
   OR MAX(o.order_date) IS NULL;
Задача 40: Когортный анализ — retention по месяцам
WITH first_order AS (
   SELECT user_id, DATE_TRUNC('month', MIN(order_date)) AS cohort
   FROM orders GROUP BY user_id
)
SELECT
   f.cohort,
   DATE_TRUNC('month', o.order_date) AS order_month,
   COUNT(DISTINCT o.user_id) AS customers
FROM first_order f
JOIN orders o ON f.user_id = o.user_id
GROUP BY f.cohort, DATE_TRUNC('month', o.order_date)
ORDER BY f.cohort, order_month;
Задача 41: Дубликаты email — оставить первую запись
DELETE FROM users
WHERE id NOT IN (
   SELECT MIN(id) FROM users GROUP BY email
);

-- Или через CTE:
WITH duplicates AS (
   SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
   FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);
Задача 42: Статистика заказов по месяцам
SELECT
   DATE_TRUNC('month', order_date) AS month,
   COUNT(*) AS order_count,
   SUM(amount) AS total,
   AVG(amount) AS avg_order,
   COUNT(DISTINCT user_id) AS unique_customers
FROM orders
GROUP BY month
ORDER BY month;
Агрегация в массивы и строки
Задача 43: Список товаров в каждом заказе одной строкой
SELECT
   o.id AS order_id,
   o.order_date,
   o.amount,
   string_agg(p.name, ', ' ORDER BY p.name) AS products,
   array_agg(oi.quantity ORDER BY p.name) AS quantities
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY o.id, o.order_date, o.amount
ORDER BY o.order_date DESC
LIMIT 20;
Задача 44: JSON отчёт по категориям
SELECT
   category,
   COUNT(*) AS product_count,
   jsonb_agg(
       jsonb_build_object(
           'id', id,
           'name', name,
           'price', price,
           'stock', stock
       ) ORDER BY price DESC
   ) AS products_json
FROM products
GROUP BY category
ORDER BY product_count DESC;
Шпаргалка

Задача

Ключевой приём

Количество/сумма/среднее

COUNT / SUM / AVG

Группировка

GROUP BY + агрегаты

Фильтр групп

HAVING

Соединение

JOIN ON

Все из одной таблицы

LEFT JOIN

Anti-join

NOT EXISTS (безопаснее NOT IN!)

Semi-join

EXISTS или IN

Подзапрос

WHERE ... IN (SELECT ...)

Один на группу

DISTINCT ON или ROW_NUMBER

Подитоги

GROUPING SETS / ROLLUP / CUBE

Иерархия

WITH RECURSIVE

Массив из строк

array_agg(col ORDER BY ...)

Строка из значений

string_agg(col, ', ')

Частые ошибки на собеседованиях
  1. NOT IN с NULL — возвращает 0 строк если подзапрос содержит NULL
  2. COUNT (*) для EXISTS — неэффективно, используйте EXISTS
  3. JOIN создаёт дубликаты — агрегируйте ДО join или используйте COUNT (DISTINCT)
  4. Коррелированные подзапросы — медленные, заменяйте на JOIN с CTE
  5. GROUP BY забыт — все неагрегированные колонки должны быть в GROUP BY