SQL Практика



SQL Практика 3 - задачи
Практика 3 — Оконные функции, DML, Индексы, EXPLAIN
Постарайся решить задачи самостоятельно, не подсматривай в ответы
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    budget NUMERIC(12, 2)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT REFERENCES departments(id),
    manager_id INT REFERENCES employees(id),
    salary NUMERIC(10, 2),
    hire_date DATE,
    status VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    employee_id INT REFERENCES employees(id),
    order_date TIMESTAMP DEFAULT NOW(),
    amount NUMERIC(10, 2),
    status VARCHAR(20)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price NUMERIC(10, 2),
    stock INT
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT,
    unit_price NUMERIC(10, 2)
);
Оконные функции — Ранжирование
Задача 1: Найти вторую по величине зарплату
Задача 2: N-я по величине зарплата
Задача 3: Топ-3 сотрудника по зарплате в каждом отделе
Задача 4: Ранжирование продаж сотрудников
Оконные функции — Накопительные значения
Задача 5: Накопительная сумма заказов по дням
Задача 6: Накопительная сумма по каждому клиенту
Задача 7: Процент от общего по отделам
Оконные функции — LAG/LEAD
Задача 8: Сравнение с предыдущим заказом
Задача 9: Процентный рост по сравнению с предыдущим
Задача 10: Дни между заказами клиента
Оконные функции — Скользящее среднее
Задача 11: Скользящее среднее за 3 дня
⚠️ Важно: ROWS vs RANGE — критичное различие!
Задача 12: Streak — последовательные дни активности
Задача 13: Найти gaps в последовательности
DML операции
Задача 14: Добавить нового пользователя
Задача 15: Добавить несколько товаров одним запросом
Задача 16: Увеличить цены на 10% в категории «Электроника»
Задача 17: UPDATE с JOIN — увеличить зарплату сотрудников из отдела IT на 10%
Задача 18: DELETE с подзапросом — удалить заказы заблокированных пользователей
Задача 19: UPSERT — вставка или обновление
Задача 20: Мягкое удаление (soft delete)
Задача 21: Почему индекс не используется?
Задача 22: Оптимизация LIKE '%text%'
Задача 23: Какой индекс создать?
Условие
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending' ORDER BY order_date DESC;
Задача 24: Оптимизировать запрос с функцией на колонке
Условие
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
Задача 25: Оптимизировать пагинацию с большим OFFSET
Условие
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
DML операции
Задача 26: Интерпретировать план запроса
Условие
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

-- Результат:
-- Seq Scan on orders  (cost=0.00..1520.00 rows=50) (actual time=0.020..15.432 rows=47 loops=1)
--   Filter: (customer_id = 123)
--   Rows Removed by Filter: 9953
Задача 27: Почему Index Scan, а не Index Only Scan?
Условие
EXPLAIN ANALYZE
SELECT customer_id, amount FROM orders WHERE customer_id = 123;

-- Index Scan using idx_orders_customer on orders
--   Heap Fetches: 47
Задача 28: Найти проблему в Nested Loop
Условие
-- Nested Loop  (actual time=0.050..45000.000 rows=1000 loops=1)
--   ->  Seq Scan on orders o  (actual time=0.010..10.000 rows=1000 loops=1)
--   ->  Index Scan on users u  (actual time=0.040..44.000 rows=1 loops=1000)
Задача 29: Диагностика Sort с disk spill
Условие
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY amount DESC LIMIT 100;

-- Sort Method: external merge  Disk: 15000kB
Задача 30: Bitmap Index Scan vs Index Scan
Условие
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

-- Bitmap Heap Scan on orders
--   Recheck Cond: (status = ANY (...))
--   Heap Blocks: exact=2000
--   ->  Bitmap Index Scan on idx_orders_status
Генерация данных
Задача 31: Создать календарь на месяц с продажами
Задача 32: Найти дни без заказов за последний месяц
Шпаргалка

Задача

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

Топ-N в группе

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

N-я по величине

DENSE_RANK() или OFFSET N-1

Накопительная сумма

SUM() OVER (ORDER BY ...)

Сравнение с предыдущим

LAG() / LEAD()

Процент от общего

COUNT() / SUM(COUNT()) OVER ()

Streak

date - ROW_NUMBER() группирует

Функция в WHERE

Функциональный индекс

Большой OFFSET

Keyset pagination

LIKE '%text%'

pg_trgm + GIN

Скользящее среднее

ROWS BETWEEN N PRECEDING (+ generate_series!)

Заполнение пропусков

generate_series + LEFT JOIN

Частые ошибки на собеседованиях
  1. Чем ROWS отличается от RANGE в оконных функциях?
  2. Как найти топ-N записей в каждой группе?
  3. Почему Index Scan, а не Index Only Scan?
  4. Как оптимизировать LIKE '%pattern%'?
  5. Что такое Heap Fetches в EXPLAIN?