SQL-промты, которые превратили меня из писца JOIN'ов в аналитика: 12 сценариев с реальными примерами

SQL-промты, которые превратили меня из писца JOIN'ов в аналитика: 12 сценариев с реальными примерами

Пятница, 18:40. Стейкхолдер пишет: «В отчёте по выручке что-то не то, вчера было на 12% больше». Я открываю дашборд, смотрю на цифры, потом на SQL — 340 строк, пять CTE (Common Table Expressions, обобщённые табличные выражения — конструкция WITH name AS (...)), три вложенных подзапроса и JOIN на таблицу-справочник без фильтра по дате. Дальше — три часа переписывания вручную. Раньше. Сейчас — 15 минут и один хорошо сформулированный промт.

Эта статья не про «магию ИИ». Это про конкретный набор промтов, которые я использую каждый день в работе с PostgreSQL и BigQuery: от генерации нетривиальных JOIN'ов до объяснения плана запроса, от поиска аномалий в данных до написания комментария к отчёту, который поймёт Product Manager без технического бэкграунда. Все промты проверены на реальных задачах. Код — рабочий, синтаксис — актуальный (PostgreSQL 16, BigQuery Standard SQL).

Почему это работает лучше, чем «спросить у ChatGPT что-нибудь про SQL»: язык моделей — это естественный язык, но SQL — язык декларативный. Если вы не дали схемы таблиц, типов колонок и примеров данных, модель угадывает. Даёте контекст — получаете рабочий запрос. Ниже — 12 промтов, где контекст уже встроен в формулировку.

1. Генерация сложного JOIN'а по описанию схемы

Задача: соединить 4+ таблицы с разной гранулярностью (например, заказы на уровне позиции и клиенты на уровне компании), не пропустив фильтр по дате и не задвоив выручку.

Промт:

Ты — senior data analyst. Дана схема:
- orders (order_id, customer_id, order_date, status)
- order_items (order_id, product_id, quantity, price)
- customers (customer_id, company_name, segment, created_at)
- products (product_id, category, cost)

Напиши SQL (PostgreSQL 16), который считает выручку по сегментам клиентов за последние 90 дней.
Требования:
1. Только заказы со status = 'completed'.
2. Выручка = quantity * price, НЕ задваивать из-за JOIN'ов.
3. Клиенты без заказов не должны попадать в результат.
4. Добавь комментарии к каждому CTE.
5. Объясни, почему выбрал именно такой порядок JOIN'ов.

Почему работает: вы явно задаёте гранулярность и риск задвоения — модель строит CTE-цепочку с агрегацией на нужном уровне, а не пишет JOIN «в лоб». Классическая ошибка ручного подхода — соединить orders и order_items и сразу посчитать SUM(price), забыв, что в orders одна строка на заказ, а в order_items — на позицию.

2. Оконные функции: cohort retention без боли

Задача: построить cohort-анализ удержания — по месяцу первой покупки клиента посчитать, сколько из них вернулись в каждый следующий месяц.

Промт:

Напиши запрос для cohort retention на PostgreSQL.
Таблица events (user_id, event_date, event_type).
Cohort = месяц первой покупки (event_type = 'purchase').
Для каждого cohort и каждого месяца жизни (month_number) посчитай:
- размер когорты (users_in_cohort)
- число вернувшихся (retained_users)
- retention_rate = retained_users / users_in_cohort

Используй MIN() OVER (PARTITION BY user_id) для определения когорты,
DATE_TRUNC('month', ...) для группировки.
Объясни каждую оконную функцию построчно.

Почему работает: формулировка «объясни построчно» заставляет модель не просто выдать код, а показать логику. Это критично для оконных функций — PARTITION BY и ORDER BY внутри OVER() меняют результат радикально, и без объяснения легко скопировать запрос с неправильной рамкой окна.

Официальная документация PostgreSQL по оконным функциям: https://www.postgresql.org/docs/current/tutorial-window.html

3. Дедупликация: найти и убрать дубликаты

Задача: в таблице events есть дубликаты (один и тот же пользователь, событие, но разный ingested_at). Нужно оставить последнюю запись.

Промт:

В таблице events (event_id, user_id, event_type, event_date, ingested_at)
есть дубликаты по (user_id, event_type, event_date).
Напиши два запроса:
1. Диагностика: сколько дубликатов, примеры групп.
2. Очистка: оставить запись с максимальным ingested_at.

Для очистки используй ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ingested_at DESC).
Покажи вариант через CTE и вариант через DELETE USING.

Почему работает: вы просите два запроса — диагностику и фикс. Это спасает от классической ошибки «сразу удалить», когда непонятно, сколько данных затронуто. Паттерн ROW_NUMBER() ... WHERE rn = 1 — стандартный и безопасный способ дедупликации.

4. Оптимизация медленного запроса: EXPLAIN на человеческом

Задача: запрос работает 40 секунд на BigQuery, нужно понять почему.

Промт:

Вот план запроса (EXPLAIN ANALYZE) и сам запрос.
[вставить вывод EXPLAIN и SQL]

Объясни:
1. Где самое дорогое место в плане (Seq Scan, Nested Loop, Sort)?
2. Почему оптимизатор выбрал этот путь?
3. Какие индексы помогут (для PostgreSQL) или как переписать под BigQuery
   (partition pruning, clustering)?
4. Перепиши запрос и покажи ожидаемое ускорение.

Почему работает: EXPLAIN ANALYZE — это формальный язык, который модель читает лучше человека. В BigQuery ключевые термины — partition pruning и cluster — модель свяжет с вашим запросом и укажет, что фильтр по дате стоит вне подзапроса. Документация BigQuery по оптимизации: https://cloud.google.com/bigquery/docs/best-practices-performance-overview

5. Поиск аномалий в данных (тот самый баг за 15 минут)

Задача: выручка упала, непонятно где. Нужно быстро локализовать аномалию.

Промт:

Дана таблица daily_revenue (date, region, product_category, revenue).
Напиши запрос, который найдёт аномалии:
1. Дни, где revenue отклоняется от скользящего среднего за 7 дней больше чем на 30%.
2. Регионы, где падение началось в один и тот же день.
3. Категории, которые объясняют падение.

Используй оконные функции AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
Результат отсортируй по абсолютному отклонению.

Почему работает: скользящее среднее через ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — честный способ найти локальный сдвиг без внешних библиотек. В моём случае запрос показал, что падение шло из одной категории в одном регионе — оказалось, в ETL-пайплайне отвалился фильтр по status, и в отчёт попали отменённые заказы. 15 минут вместо трёх часов.

6. Объяснение чужого SQL «по-человечески»

Задача: разобрать легаси-запрос на 200 строк, который написал уволившийся аналитик.

Промт:

Объясни этот SQL так, чтобы понял аналитик, впервые видящий код.
Для каждого CTE:
- что делает
- какая гранулярность на выходе
- какие допущения заложены
Затем: где могут быть баги и как их проверить.

[вставить SQL]

Почему работает: просьба указать гранулярность на выходе каждого CTE — самый ценный пункт. 80% багов в аналитике — это несовпадение гранулярности при JOIN. Модель явно проговаривает: «orders — одна строка на заказ, order_items — на позицию».

7. Проверка гипотезы через SQL

Задача: PM говорит «мне кажется, клиенты из сегмента A покупают чаще после смены тарифа». Нужно проверить.

Промт:

Проверь гипотезу: клиенты сегмента 'A' после смены тарифа делают больше покупок.
Таблицы: subscriptions (customer_id, plan, changed_at), purchases (customer_id, purchased_at).
Напиши запрос, который:
1. Считает частоту покупок до и после смены тарифа (окно 30 дней).
2. Сравнивает с контрольной группой (кто не менял тариф).
3. Считает разницу и её статистическую значимость (t-тест приблизительно).

Почему работает: вы задаёте контрольную группу — без неё любое «стало больше» ничего не значит. Запрос с self-join по customer_id и оконными функциями даёт сравнимые периоды.

8. BigQuery: работа с вложенными структурами (ARRAY, STRUCT)

Задача: развернуть массив событий внутри строки.

Промт:

В BigQuery таблица sessions (session_id, user_id, events ARRAY<STRUCT<type STRING, ts TIMESTAMP>>).
Напиши запрос, который:
1. Разворачивает events через UNNEST.
2. Считает для каждой сессии число событий каждого типа.
3. Оставляет только сессии, где был 'purchase'.
Объясни разницу между CROSS JOIN UNNEST и LEFT JOIN UNNEST.

Почему работает: UNNEST — специфика BigQuery, где модель часто ошибается с CROSS vs LEFT. Явный вопрос про разницу заставляет её объяснить, что CROSS JOIN UNNEST выкидывает сессии без событий, а LEFT JOIN — нет. Документация: https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays

9. Генерация тестовых данных для проверки запроса

Задача: проверить запрос на краевых случаях, не трогая production.

Промт:

Сгенерируй INSERT-ы в PostgreSQL для таблицы orders (order_id, customer_id, order_date, amount, status), которые покроют краевые случаи:
- NULL в customer_id
- отрицательный amount
- status вне ожидаемых значений
- заказ ровно на границе периода
- дубликат order_id
Дай 15 строк с комментариями, какой кейс проверяет каждая.

Почему работает: вы получаете тестовый набор за минуту вместо часа ручного придумывания. Краевые случаи, которые модель перечисляет, обычно включают те, о которых вы не подумали (например, NULL в колонке, которая в вашем запросе участвует в JOIN).

10. Комментарий к отчёту для стейкхолдера

Задача: объяснить падение метрики Product Manager'у без технического бэкграунда.

Промт:

У меня есть результат запроса: выручка за неделю упала на 8%.
Разбивка: регион Север — падение 15%, Юг — рост 3%, Запад — без изменений.
Категория 'Electronics' упала на 22%, остальные выросли.

Напиши комментарий к отчёту (3–4 предложения) для Product Manager:
- без SQL-терминов
- с акцентом на бизнес-вывод
- с гипотезой, что проверить дальше

Почему работает: модель переводит технический результат в бизнес-язык. Формулировка «с гипотезой, что проверить дальше» превращает комментарий из констатации в следующий шаг. Это то, чего чаще всего не хватает в отчётах.

11. Рефакторинг: из подзапросов в CTE

Задача: переписать запрос с вложенными подзапросами на читаемые CTE.

Промт:

Перепиши этот SQL, заменив вложенные подзапросы на CTE.
Правила:
1. Каждый CTE — одна логическая операция.
2. Имена CTE — говорящие (не t1, t2).
3. Не меняй результат запроса.
4. Покажи до и после, отметь, что улучшилось в читаемости.

[вставить SQL]

Почему работает: CTE — стандарт читаемости в аналитике (поддерживается в PostgreSQL, BigQuery, Snowflake, Redshift). Просьба «не меняй результат» защищает от случайной смены семантики. Явное «имена — говорящие» убирает t1, t2, tmp из кода.

12. Перевод запроса между диалектами SQL

Задача: запрос написан под PostgreSQL, нужно запустить в BigQuery.

Промт:

Переведи запрос с PostgreSQL на BigQuery Standard SQL.
Особое внимание:
- DATE_TRUNC (разный синтаксис)
- строковые функции (CONCAT vs ||)
- работа с датами (INTERVAL)
- оконные функции (совместимы, но проверь)
Покажи таблицу: было / стало / причина изменения.

[вставить SQL]

Почему работает: таблица «было / стало / причина» — самый быстрый способ проверить перевод. Вы видите не просто новый код, а точки, где диалекты расходятся. Например, DATE_TRUNC('month', date) совместим, а EXTRACT(EPOCH FROM ...) в BigQuery пишется иначе.

Мини-шпаргалка: как формулировать промт под SQL

Что важно помнить

Во-первых, промт не заменяет понимание. Если вы не знаете, что такое гранулярность или оконная рамка, модель выдаст код, который вы не сможете проверить. Промты ускоряют рутину, но не отменяют базу.

Во-вторых, всегда проверяйте результат на маленьком срезе. Я обычно прошу модель сгенерировать тестовые данные (промт №9) и прогоняю запрос на них — это ловит большинство ошибок до production.

В-третьих, документация — ваш друг. Оконные функции PostgreSQL: https://www.postgresql.org/docs/current/tutorial-window.html. Оптимизация BigQuery: https://cloud.google.com/bigquery/docs/best-practices-performance-overview. CTE в стандарте SQL: https://www.postgresql.org/docs/current/queries-with.html.

Начните с одного промта — того, где вы чаще всего застреваете. У меня это был №5 (поиск аномалий) и №2 (cohort retention). Через неделю вы заметите, что ручных JOIN'ов стало меньше, а времени на проверку гипотез — больше. Попробуйте промт №6 на самом запутанном запросе в вашем проекте — и посмотрите, сколько допущений вы не замечали.