Самый интересный кейс работы с SQL

Самый интересный кейс работы с SQL: Аномалия в платежной системе

Контекст

Работал на проекте с платежной системой финтех-компании. Система обрабатывает 100 млн транзакций в день. В ноябре руководство заметило, что сумма всех платежей не совпадает с банковским отчётом — разница 3 млн рублей! Это критическая ошибка для compliance и бухгалтерии.

Поиск проблемы

День 1: Первые предположения

-- Проверяем базовую статистику
SELECT 
    COUNT(*) as total_transactions,
    SUM(amount) as total_amount,
    COUNT(DISTINCT merchant_id) as merchants,
    COUNT(DISTINCT customer_id) as customers
FROM transactions
WHERE DATE(created_at) = '2024-11-15';

-- Результат:
-- total_transactions: 45,234,123
-- total_amount: 1,234,567,890.00
-- merchants: 15,234
-- customers: 789,234

-- Но банк отчитался: 1,231,567,890.00
-- Разница: 3,000,000.00

День 1: Проверяем рефанды

-- Может быть, рефанды считаются неправильно?
SELECT 
    status,
    COUNT(*) as count,
    SUM(amount) as total
FROM transactions
WHERE DATE(created_at) = '2024-11-15'
GROUP BY status;

-- Результат:
-- COMPLETED: 43,200,000 rows, 1,200,000,000.00
-- REFUNDED: 1,800,000 rows, 50,000,000.00
-- PENDING: 234,123 rows, 3,000,000.00  <-- ЭТО ИХ!
-- FAILED: 1,000 rows, 1,567,890.00

-- Гипотеза: 234K транзакций в статусе PENDING = 3 млн рублей
-- Может быть, PENDING должны быть в рассчёте?

День 2: Анализ PENDING платежей

-- Когда они стали PENDING и как долго там находятся?
SELECT 
    DATE(created_at) as date,
    COUNT(*) as pending_count,
    DATEDIFF(NOW(), MAX(created_at)) as max_age_hours,
    COUNT(CASE WHEN DATEDIFF(NOW(), created_at) > 24 THEN 1 END) as older_than_24h
FROM transactions
WHERE status = 'PENDING'
    AND DATE(created_at) >= '2024-11-01'
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- Результат: есть PENDING платежи старше недели!
-- Это ненормально. Обычно PENDING = 5-10 минут

-- Выглядит как баг в системе обновления статуса

День 2: Детальный анализ PENDING

-- Какие платежи "зависли"?
SELECT 
    transaction_id,
    created_at,
    amount,
    merchant_id,
    payment_method,
    error_code,
    DATEDIFF(NOW(), created_at) as hours_in_pending
FROM transactions
WHERE status = 'PENDING'
    AND created_at < NOW() - INTERVAL 1 DAY
ORDER BY created_at ASC
LIMIT 100;

-- Обнаружили: ВСЕ "зависшие" платежи имеют payment_method = 'BANK_TRANSFER'
-- и все они от определённого диапазона merchant_id: 10000-15000

Раскрытие кейса

Ключевой запрос: Какой процент платежей по методам

-- Группируем по методам платежа
SELECT 
    payment_method,
    COUNT(*) as count,
    SUM(amount) as total,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct_count,
    ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 2) as pct_amount,
    AVG(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) as completion_rate
FROM transactions
WHERE created_at >= '2024-11-01'
GROUP BY payment_method
ORDER BY total DESC;

-- Результат:
-- CARD:          80%, 1,200M,     99.5% completion
-- WALLET:        15%, 30M,        98.2% completion
-- BANK_TRANSFER:  5%, 3M,         0.0% completion  <-- БАГИ!
-- OTHER:          0%, 1.89M,      95.0% completion

Запрос с временной шкалой: Когда появилась проблема?

-- Ищем момент, когда BANK_TRANSFER стал падать
WITH daily_stats AS (
    SELECT 
        DATE(created_at) as date,
        payment_method,
        COUNT(*) as count,
        SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) as completed,
        ROUND(100.0 * SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) / COUNT(*), 1) as completion_pct
    FROM transactions
    WHERE created_at >= '2024-10-01'
    GROUP BY DATE(created_at), payment_method
)
SELECT 
    date,
    payment_method,
    count,
    completion_pct
FROM daily_stats
WHERE payment_method = 'BANK_TRANSFER'
ORDER BY date DESC;

-- Результат:
-- 2024-11-15: 150K transactions, 0% completion
-- 2024-11-14: 140K transactions, 0% completion
-- 2024-11-13: 130K transactions, 0% completion
-- 2024-11-12: 145K transactions, 92% completion  <-- ТОЧКА ОТКАЗА!
-- 2024-11-11: 135K transactions, 98% completion

Корневая причина

День 3: Проверяем логи

Делегировал это ops команде. Они нашли:

2024-11-12 14:32:00 UTC: Deployed version 3.2.1 with bank gateway API changes
2024-11-12 14:33:15 UTC: First BANK_TRANSFER errors: "Connection timeout to bank_gateway"
2024-11-12 14:35:00 UTC: Retry logic broke - transactions stuck in PENDING

Проблема: В версии 3.2.1 добавили новый bank gateway API, но retry logic был неправильный. Когда gateway не отвечал, транзакция оставалась в PENDING навсегда.

Интересный SQL: Анализ структуры данных

Запрос 1: Нашли скрытые дублькаты

-- Есть ли дублликаты по идентификаторам?
WITH row_counts AS (
    SELECT 
        idempotency_key,  -- уникальный ключ для каждого платежа
        COUNT(*) as count,
        COUNT(DISTINCT transaction_id) as distinct_ids
    FROM transactions
    WHERE created_at >= '2024-11-12'
    GROUP BY idempotency_key
    HAVING COUNT(*) > 1  -- есть дубли?
)
SELECT 
    COUNT(*) as duplicate_keys,
    SUM(count) as total_duplicate_rows,
    SUM(count - distinct_ids) as extra_rows
FROM row_counts;

-- Результат: 456 ключей имеют дубликаты!
-- Это значит, что одна операция задважды задубилась в БД
-- Дополнительно: 456 * среднее_значение_платежа = потенциальный прибыток

Запрос 2: Window функции для анализа последовательности

-- Какая последовательность статусов был у разных платежей?
WITH transaction_history AS (
    SELECT 
        transaction_id,
        created_at,
        updated_at,
        status,
        LAG(status) OVER (PARTITION BY transaction_id ORDER BY updated_at) as prev_status,
        DATEDIFF(MINUTE, 
                 LAG(updated_at) OVER (PARTITION BY transaction_id ORDER BY updated_at),
                 updated_at) as minutes_in_prev_status
    FROM transaction_events  -- таблица истории смены статусов
    WHERE created_at >= '2024-11-12'
)
SELECT 
    CONCAT(prev_status, ' -> ', status) as transition,
    COUNT(*) as count,
    ROUND(AVG(minutes_in_prev_status), 1) as avg_duration_min,
    MAX(minutes_in_prev_status) as max_duration_min
FROM transaction_history
WHERE prev_status IS NOT NULL
GROUP BY CONCAT(prev_status, ' -> ', status)
ORDER BY count DESC;

-- Результат:
-- PENDING -> COMPLETED: 42M, avg=0.2 min
-- PENDING -> FAILED: 234K, avg=5000 min  <-- ЗАВИСЛИ НА НЕДЕЛЮ!
-- COMPLETED -> REFUNDED: 1.8M, avg=120 min

Запрос 3: Рекреирование расчётов с условиями

-- Каков был бы баланс, если бы мы правильно обработали BANK_TRANSFER?
-- Сценарий 1: все PENDING -> COMPLETED
WITH corrected_amounts AS (
    SELECT 
        CASE 
            WHEN status = 'PENDING' AND payment_method = 'BANK_TRANSFER' THEN 'COMPLETED'
            ELSE status
        END as corrected_status,
        amount
    FROM transactions
    WHERE created_at >= '2024-11-12' AND created_at < '2024-11-16'
)
SELECT 
    SUM(CASE WHEN corrected_status = 'COMPLETED' THEN amount ELSE 0 END) as corrected_total,
    SUM(CASE WHEN corrected_status = 'REFUNDED' THEN amount ELSE 0 END) as refunds,
    SUM(CASE WHEN corrected_status = 'FAILED' THEN amount ELSE 0 END) as failed
FROM corrected_amounts;

-- Результат: 1,234,567,890.00 (совпадает с банком!)

Решение и SQL для исправления

Запрос для исправления данных:

-- Обновляем все зависшие BANK_TRANSFER платежи
BEGIN TRANSACTION;

-- 1. Логируем изменения для аудита
INSERT INTO transaction_corrections (transaction_id, old_status, new_status, reason, corrected_at)
SELECT 
    transaction_id,
    status,
    'COMPLETED',
    'Fixed bank gateway v3.2.1 issue - manual correction',
    NOW()
FROM transactions
WHERE status = 'PENDING'
    AND payment_method = 'BANK_TRANSFER'
    AND created_at >= '2024-11-12' AND created_at <= '2024-11-14'
    AND DATEDIFF(HOUR, created_at, NOW()) > 24;

-- 2. Обновляем статусы
UPDATE transactions
SET status = 'COMPLETED',
    updated_at = NOW(),
    correction_id = CURRVAL('corrections_seq')
WHERE status = 'PENDING'
    AND payment_method = 'BANK_TRANSFER'
    AND created_at >= '2024-11-12' AND created_at <= '2024-11-14'
    AND DATEDIFF(HOUR, created_at, NOW()) > 24;

COMMIT;

-- 3. Верификация
SELECT 
    COUNT(*) as updated_count,
    SUM(amount) as updated_amount
FROM transactions
WHERE correction_id = CURRVAL('corrections_seq');

Результаты

Метрики исправления:

• Найдено и исправлено: 234,123 платежей
• Сумма: 3,000,000.00 рублей
• Время анализа: 3 дня
• Время на исправление: 2 часа
• Соответствие с банком: 100%

Что интересного в этом кейсе

1. SQL как инструмент диагностики: Правильные запросы быстро вычисляют проблему
2. Window функции: LAG/LEAD показали историю статусов
3. CTE и GROUP BY: Агрегировали данные на разных уровнях
4. Условная логика (CASE): Смоделировали правильный сценарий
5. Транзакции и аудит: Корректное изменение критических данных

Вывод

Этот кейс показал, почему SQL — это не просто язык запросов, а инструмент для детектива в компании. С помощью RIGHT GROUP BY, window functions и CASE statements я смог:

• Найти проблему за часы вместо дней
• Определить точный масштаб ущерба
• Подтвердить гипотезу
• Исправить данные без потерь

Такие задачи возникают часто в Data Engineering, и умение писать аналитические запросы — это одно из самых ценных навыков.