Магия SQL: как извлечь инсайты из базы данных гадалок Наваш

Цель статьи – показать пошагово‚ как построить запросы‚ позволяющие получить полезные инсайты из базы данных‚ где фиксируются бесплатные онлайн‑консультации гадалок‚ обслуживающих жителей вымышленного города Навашино. Мы разберём типовую схему таблиц‚ базовые и продвинутые техники SQL‚ а также рекомендации по оптимизации.

Типовая структура базы данных

Для примера возьмём следующую схему (в реальном проекте названия могут отличаться).

Таблица	Колонки	Краткое назначение
consultations	id – PK psychic_id – FK → psychics.id client_id – FK → clients.id city_id – FK → cities.id started_at – datetime начала ended_at – datetime окончания is_free – boolean (TRUE – бесплатная) rating – оценка клиента (1‑5)	Записи о каждой проведённой консультации
psychics	id – PK name – имя гадалки specialty – область (любовь‚ работа‚ здоровье и т.п.) experience_years – стаж в годах	Справочник гадалок
clients	id – PK name – имя клиента age – возраст gender – пол registered_at – дата регистрации в системе	Пользователи‚ получающие консультацию
cities	id – PK name – название города (в нашем случае «Навашино») region – регион/область	Справочник географии

Все даты хранятся в типе DATETIME‚ а булевы флаги – в BOOLEAN (или TINYINT(1) в MySQL).

Основные задачи анализа

Сколько бесплатных консультаций проведено за выбранный период?
Какая гадалка проводит больше всего бесплатных сеансов?
Средняя продолжительность бесплатных консультаций.
Коэффициент конверсии: сколько бесплатных сеансов превратились в платные в течение месяца?
Когорный анализ: как меняется активность новых клиентов после первой бесплатной консультации.

Далее покажем‚ как решить каждую задачу с помощью SQL.

Базовые запросы

Общее количество бесплатных консультаций за месяц

SELECT
 COUNT(*) AS free_consultations_cnt
FROM
 consultations
WHERE
 is_free = TRUE
 AND started_at >= '2026-07-01' -- начало месяца
 AND started_at < '2026-08-01'; -- конец месяца (не включительно)

Топ‑5 гадалок по количеству бесплатных сеансов

SELECT
 p.id‚
 p.name‚
 COUNT(c.id) AS free_cnt
FROM
 consultations c
 JOIN psychics p ON c.psychic_id = p.id
WHERE
 c.is_free = TRUE
 AND c.started_at BETWEEN '2026-01-01' AND '2026-06-30'
GROUP BY
 p.id‚ p.name
ORDER BY
 free_cnt DESC
LIMIT 5;

Средняя продолжительность бесплатных консультаций (в минутах)

SELECT
 AVG(TIMESTAMPDIFF(MINUTE‚ c.started_at‚ c.ended_at)) AS avg_duration_min
FROM
 consultations c
WHERE
 c.is_free = TRUE;

Если ваша СУБД поддерживает функцию EXTRACT(EPOCH FROM …)‚ можно использовать её вместо TIMESTAMPDIFF.

Запросы с несколькими таблицами и условиями

Количество бесплатных консультаций по специализации гадалки

SELECT
 p.specialty‚
 COUNT(c.id) AS free_cnt
FROM
 consultations c
 JOIN psychics p ON c.psychic_id = p.id
WHERE
 c.is_free = TRUE
 AND c.started_at >= DATE_SUB(CURDATE‚ INTERVAL 30 DAY)
GROUP BY
 p.specialty
ORDER BY
 free_cnt DESC;

Средний рейтинг бесплатных консультаций в разных возрастных группах клиентов

SELECT
 CASE
 WHEN cl.age < 20 THEN 'до 20'
 WHEN cl.age BETWEEN 20 AND 29 THEN '20‑29'
 WHEN cl.age BETWEEN 30 AND 39 THEN '30‑39'
 WHEN cl.age BETWEEN 40 AND 49 THEN '40‑49'
 ELSE '50+'
 END AS age_group‚
 AVG(c.rating) AS avg_rating
FROM
 consultations c
 JOIN clients cl ON c.client_id = cl.id
WHERE
 c.is_free = TRUE
 AND c.rating IS NOT NULL
GROUP BY
 age_group
ORDER BY
 age_group;

Продвинутый анализ

Конверсия бесплатных → платных сеансов

Считаем‚ сколько уникальных клиентов‚ получивших бесплатную консультацию‚ в течение 30 дней после неё сделали хотя бы одну платную.

WITH free_clients AS (
 SELECT
 client_id‚
 MIN(started_at) AS first_free_at
 FROM
 consultations
 WHERE
 is_free = TRUE
 GROUP BY
 client_id
)‚
paid_after_free AS (
 SELECT
 f.client_id
 FROM
 free_clients f
 JOIN consultations c
 ON c.client_id = f.client_id
 AND c.is_free = FALSE
 AND c.started_at BETWEEN f.first_free_at
 AND DATE_ADD(f.first_free_at‚ INTERVAL 30 DAY)
 GROUP BY
 f.client_id
)
SELECT
 COUNT() AS converted_clients‚
 (COUNT() / (SELECT COUNT() FROM free_clients)) -00 AS conversion_percent
FROM
 paid_after_free;

Когорный анализ: удержание клиентов после первой бесплатной консультации

WITH first_free AS (
 SELECT
 client_id‚
 MIN(started_at) AS first_date
 FROM
 consultations
 WHERE
 is_free = TRUE
 GROUP BY
 client_id
)
SELECT
 DATE_FORMAT(first_date‚ '%Y-%m') AS cohort_month‚
 SUM(CASE WHEN DATEDIFF(c.started_at‚ f.first_date) BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS month_0_30‚
 SUM(CASE WHEN DATEDIFF(c.started_at‚ f.first_date) BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS month_31_60‚
 SUM(CASE WHEN DATEDIFF(c.started_at‚ f.first_date) BETWEEN 61 AND 90 THEN 1 ELSE 0 END) AS month_61_90
FROM
 first_free f
 LEFT JOIN consultations c
 ON c.client_id = f.client_id
 AND c.is_free = FALSE -- учитываем только платные последующие сеансы
GROUP BY
 cohort_month
ORDER BY
 cohort_month;

Полученная таблица позволяет построить «воронку удержания» в любой BI‑системе.

Использование оконных функций: среднее количество бесплатных сеансов за последние 7 дней

SELECT
 c.started_at‚
 p.name‚
 COUNT(*) OVER (
 PARTITION BY p.id
 ORDER BY c.started_at
 RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
 ) AS free_last_7d
FROM
 consultations c
 JOIN psychics p ON c.psychic_id = p.id
WHERE
 c.is_free = TRUE
 AND c.started_at >= '2026-07-01';

Оптимизация запросов

Индексы. Для быстрых фильтров по дате и флагу is_free создайте составной индекс:
```
CREATE INDEX idx_consultations_free_date
 ON consultations (is_free‚ started_at);
 
```
Индексы на внешних ключах. Поля psychic_id‚ client_id‚ city_id обычно уже индексированы‚ но проверьте.
Избегайте SELECT * – выбирайте только нужные колонки‚ это уменьшает объём передаваемых данных.
План выполнения. В MySQL используйте EXPLAIN‚ в PostgreSQL – EXPLAIN ANALYZE‚ чтобы увидеть‚ где «узкие места».
Партирование. Если таблица consultations растёт до сотен миллионов записей‚ рассмотрите партиционирование по дате (RANGE).

Как проверить запросы в «песочнице»

Для практики можно воспользоваться бесплатными онлайн‑тренажёрами:

DB‑Fiddle – поддерживает MySQL‚ PostgreSQL‚ SQLite.
SQL Fiddle – аналогичный сервис.
DuckDB – лёгкая СУБД‚ которую можно запускать прямо в браузере.

Загрузите в неё небольшую тестовую выборку (пример ниже) и отрабатывайте запросы.

Пример тестовых данных (CSV)

-- consultations.csv
id‚psychic_id‚client_id‚city_id‚started_at‚ended_at‚is_free‚rating
1‚10‚100‚1‚2026-07-01 10:00:00‚2026-07-01 10:30:00‚TRUE‚5
2‚11‚101‚1‚2026-07-02 12:15:00‚2026-07-02 12:45:00‚TRUE‚4
3‚10‚102‚1‚2026-07-03 09:00:00‚2026-07-03 09:20:00‚FALSE‚5
...

После импорта в выбранный движок вы сможете сразу запускать все приведённые выше запросы.

Анализ бесплатных консультаций гадалок в Навашино требует умения работать с несколькими таблицами‚ применять агрегаты‚ оконные функции и CTE. В статье мы:

Описали типовую схему данных.
Продемонстрировали базовые запросы для подсчётов и группировок.
Показали‚ как построить более сложные аналитические запросы (конверсия‚ когорты‚ скользящие окна).
Дали рекомендации по индексации и оптимизации.
Предложили ресурсы для практики без установки локального ПО.

Освоив эти техники‚ вы сможете быстро получать ответы на бизнес‑вопросы‚ такие как «какие гадалки привлекают больше новых клиентов»‚ «какова эффективность бесплатных сеансов в привлечении платных»‚ и «какие демографические группы наиболее активны». Всё это – фундаментальные навыки Junior Data Analyst и любого специалиста‚ работающего с данными.

Удачной аналитики!