SQL-запросы для тестирования данных

SQL-запросы для тестирования данных

Предусловия

• СУБД: PostgreSQL или MySQL (синтаксис немного отличается)
• Текущая дата: 2026-03-23
• Таблицы: users и orders уже существуют и содержат тестовые данные

---

1. Вывести всех пользователей, зарегистрированных за последний месяц

Задача

Получить список всех пользователей, которые создали аккаунт в течение последних 30 дней (с 2026-02-21 по 2026-03-23).

SQL-запрос (PostgreSQL/MySQL)

SELECT 
    id,
    name,
    email,
    created_at,
    status
FROM users
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND created_at <= NOW()
ORDER BY created_at DESC;

Альтернативный синтаксис (для разных СУБД)

PostgreSQL:

SELECT * FROM users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY created_at DESC;

MySQL:

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC;

SQLite (например, для мобильных тестов):

SELECT * FROM users
WHERE created_at >= datetime('now', '-30 days')
ORDER BY created_at DESC;

Проверка результата

Что должно быть в результате:

• Все пользователи с created_at в диапазоне [2026-02-21, 2026-03-23]
• Отсортированы по дате создания (новые сверху)
• Не должно быть пользователей старше 30 дней

Тестовые данные для проверки:

-- Правильно: должен быть в результате
INSERT INTO users VALUES (1, 'John Doe', 'john@example.com', '2026-03-15', 'active');

-- Правильно: граница (ровно 30 дней назад)
INSERT INTO users VALUES (2, 'Jane Smith', 'jane@example.com', '2026-02-21', 'active');

-- Неправильно: должен быть исключен
INSERT INTO users VALUES (3, 'Old User', 'old@example.com', '2026-02-20', 'active');

---

2. Найти пользователей, у которых нет заказов

Задача

Получить список пользователей, которые создали аккаунт, но ни разу не сделали заказ (нет записей в таблице orders).

SQL-запрос (основной вариант с LEFT JOIN)

SELECT 
    u.id,
    u.name,
    u.email,
    u.created_at,
    u.status,
    COUNT(o.id) as orders_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL
GROUP BY u.id, u.name, u.email, u.created_at, u.status
ORDER BY u.created_at DESC;

Альтернативный вариант (с NOT IN)

SELECT *
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

Альтернативный вариант (с NOT EXISTS)

SELECT u.*
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Сравнение методов

Проверка результата

Тестовые данные:

-- Пользователь БЕЗ заказов (должен быть в результате)
INSERT INTO users VALUES (10, 'NoOrders User', 'noorder@example.com', '2026-01-15', 'active');

-- Пользователь С заказами (НЕ должен быть в результате)
INSERT INTO users VALUES (11, 'WithOrders User', 'withorder@example.com', '2026-01-10', 'active');
INSERT INTO orders VALUES (101, 11, 150.00, '2026-03-20', 'completed');

Проверка:

• Результат должен содержать user_id=10
• Результат НЕ должен содержать user_id=11
• orders_count должен быть 0 для всех записей

---

3. Вывести топ-5 пользователей по сумме заказов

Задача

Найти 5 пользователей с наибольшей общей суммой заказов.

SQL-запрос

SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount,
    AVG(o.amount) as avg_order_amount,
    MAX(o.order_date) as last_order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING SUM(o.amount) > 0
ORDER BY total_amount DESC
LIMIT 5;

Альтернативный вариант (с CTE — Common Table Expression)

WITH user_totals AS (
    SELECT 
        user_id,
        SUM(amount) as total_amount,
        COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.id,
    u.name,
    u.email,
    ut.order_count,
    ut.total_amount
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
ORDER BY ut.total_amount DESC
LIMIT 5;

Проверка результата

Тестовые данные:

-- Пользователь #1: всего 300
INSERT INTO orders VALUES (201, 20, 100.00, '2026-03-20', 'completed');
INSERT INTO orders VALUES (202, 20, 200.00, '2026-03-21', 'completed');

-- Пользователь #2: всего 150
INSERT INTO orders VALUES (203, 21, 150.00, '2026-03-22', 'completed');

-- Пользователь #3: всего 500
INSERT INTO orders VALUES (204, 22, 250.00, '2026-03-19', 'completed');
INSERT INTO orders VALUES (205, 22, 250.00, '2026-03-20', 'completed');

Ожидаемый результат:

1. User #22: 500.00 (2 заказа)
2. User #20: 300.00 (2 заказа)
3. User #21: 150.00 (1 заказ)

---

4. Найти заказы со статусом 'pending', созданные более 7 дней назад

Задача

Получить все незавершенные заказы (pending), которые висят уже более недели. Это важно для контроля качества: может быть проблема, если заказ не обрабатывается.

SQL-запрос

SELECT 
    o.id,
    o.user_id,
    u.name,
    u.email,
    o.amount,
    o.order_date,
    o.status,
    CURRENT_DATE - DATE(o.order_date) as days_pending
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.order_date <= NOW() - INTERVAL '7 days'
ORDER BY o.order_date ASC;

Альтернативный синтаксис (MySQL)

SELECT 
    o.*,
    u.name,
    u.email,
    DATEDIFF(NOW(), o.order_date) as days_pending
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.order_date <= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY o.order_date ASC;

Альтернативный синтаксис (PostgreSQL)

SELECT 
    o.*,
    u.name,
    u.email,
    EXTRACT(DAY FROM CURRENT_TIMESTAMP - o.order_date)::INT as days_pending
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.order_date <= CURRENT_TIMESTAMP - INTERVAL '7 days'
ORDER BY o.order_date ASC;

Проверка результата

Тестовые данные:

-- Заказ давно (более 7 дней) - ДОЛЖЕН быть в результате
INSERT INTO orders VALUES (301, 30, 100.00, '2026-03-10', 'pending');

-- Заказ ровно 7 дней назад - НЕ должен быть в результате
INSERT INTO orders VALUES (302, 31, 100.00, '2026-03-16', 'pending');

-- Заказ недавно (менее 7 дней) - НЕ должен быть в результате
INSERT INTO orders VALUES (303, 32, 100.00, '2026-03-22', 'pending');

-- Завершенный заказ (более 7 дней) - НЕ должен быть в результате
INSERT INTO orders VALUES (304, 33, 100.00, '2026-03-10', 'completed');

Ожидаемый результат: только order_id = 301

---

Дополнительные SQL-запросы для расширенного тестирования

Проверка целостности данных

-- Найти orphaned записи в orders (user_id который удален из users)
SELECT o.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

-- Найти NULL значения
SELECT * FROM users WHERE name IS NULL OR email IS NULL;
SELECT * FROM orders WHERE user_id IS NULL OR amount IS NULL;

-- Проверка дублей
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Проверка отрицательных сумм
SELECT * FROM orders WHERE amount < 0;

-- Проверка будущих дат
SELECT * FROM orders WHERE order_date > NOW();

Статистика для QA отчета

-- Общая статистика
SELECT 
    COUNT(DISTINCT u.id) as total_users,
    COUNT(DISTINCT o.id) as total_orders,
    SUM(o.amount) as total_revenue,
    AVG(o.amount) as avg_order_amount,
    MIN(o.amount) as min_order_amount,
    MAX(o.amount) as max_order_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Распределение по статусам
SELECT 
    status,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY status
ORDER BY order_count DESC;

---

Чек-лист при выполнении SQL-запросов

• Запрос синтаксически верен (нет синтаксических ошибок)
• Результат соответствует ожиданиям
• Проверены граничные случаи (NULL, пустые наборы, дубли)
• Запрос работает на текущей дате (2026-03-23)
• Производительность приемлема (нет full table scan если возможно)
• Индексы используются корректно
• Результаты отсортированы правильно
• Нет потери данных при JOIN операциях
• Пишут ли логи базы об ошибках
• Проверены разные значения NULL в GROUP BY