Задача 1: Найти дубликаты в таблице
Условие:
В таблице users есть столбец email. Найдите все email-адреса, которые встречаются более одного раза, и укажите, сколько раз каждый из них встречается.
Решение:
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
Объяснение:
- GROUP BY email группирует записи по уникальному email.
- COUNT(*) считает количество записей в каждой группе.
- HAVING COUNT(*) > 1 фильтрует только те группы, где дубликатов больше одного (в отличие от WHERE, который работает до группировки).
- ORDER BY cnt DESC — для удобства: самые частые дубликаты сверху.
Задача 2: Ранжирование без оконных функций (старый стиль)
Условие:
В таблице sales есть столбцы employee_id и amount.
Получите топ-3 продавца по сумме продаж (amount), не используя LIMIT.
Решение (через подзапрос):
SELECT s1.employee_id, s1.amount
FROM sales s1
WHERE (
SELECT COUNT(*)
FROM sales s2
WHERE s2.amount > s1.amount
) < 3
ORDER BY s1.amount DESC;
Объяснение:
Для каждой строки s1 считаем, сколько строк s2 имеют большую сумму.
Если таких строк меньше 3, значит, текущая строка входит в топ-3.
Задача 3: Обновление с использованием другой таблицы
Условие:
У вас есть две таблицы:
- products (id, name, price)
- price_updates (product_id, new_price)
Обновите цены в products на основе price_updates, только для тех товаров, которые есть в обеих таблицах.
Решение:
UPDATE products
SET price = price_updates.new_price
FROM price_updates
WHERE products.id = price_updates.product_id;
Объяснение:
В PostgreSQL синтаксис UPDATE ... FROM позволяет джойнить другую таблицу прямо в UPDATE.
Задача 4: Сравнение с предыдущим периодом (LAG + агрегация)
Условие:
Таблица employees (id, name, manager_id) представляет иерархию подчинения.
Найдите всех подчинённых менеджера с id = 1, включая вложенных (рекурсивно).
Решение:
Условие:
Таблица daily_sales (sale_date DATE, amount NUMERIC).
Для каждого дня рассчитайте:
- Сумму продаж за день,
- Сумму продаж за предыдущий день,
- Разницу между ними.
Решение:
SELECT
sale_date,
amount AS current_day,
LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_day,
amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS diff
FROM daily_sales
ORDER BY sale_date;
Объяснение:
- LAG(amount, 1) — берёт значение amount из предыдущей строки (по дате).
- OVER (ORDER BY sale_date) — задаёт порядок для оконной функции.
- В первой строке prev_day будет NULL — это нормально.