Профессия:
Решение: SQL JOIN для соединения таблиц
Основной запрос
SELECT p.id, p.name, pos.title AS pos_title FROM persons p INNER JOIN positions pos ON p.pos_id = pos.id;
Объяснение
SELECT столбцы:
FROM persons p: основная таблица с сокращением p
INNER JOIN positions pos: соединяем positions, выводим только совпадения
ON p.pos_id = pos.id: условие — id должности в persons совпадает с id в positions
Как работает запрос
Результат: id | name | pos_title 1 | Иван | Аналитик 2 | Мария | Менеджер
Другие типы JOIN
LEFT JOIN: все сотрудники, даже без должности (результат NULL)
RIGHT JOIN: все должности, даже без сотрудников (результат NULL)
Решение
Пошаговый план анализа снижения продаж на 15%
Снижение продаж на 15% может быть вызвано множеством факторов. Проведу систематический анализ для выявления корневых причин.
1. Требуемые данные
Основные таблицы:
Дополнительные источники:
2. Основные гипотезы для проверки
Гипотеза 1: Снижение трафика и конверсии
Решение
Задача требует добавления столбца с средней зарплатой по отделу используя оконные функции.
SQL-запрос
SELECT
depname,
empno,
salary,
ROUND(
AVG(salary) OVER (PARTITION BY depname)::NUMERIC,
2
) AS avg_salary_dept
FROM salaries
ORDER BY depname, empno;
Пошаговое объяснение
1. SELECT столбцы
depname — название отделаempno — номер сотрудникаsalary — зарплатаROUND(..., 2) — округляем среднее до 2 знаков2. AVG(salary) OVER (PARTITION BY depname)
PARTITION BY depname — считаем среднее отдельно для каждого отдела3. ::NUMERIC для корректного деления
4. ORDER BY depname, empno
Пример выполнения
Решение
Задача требует подсчета пользователей, которые ушли из сервиса (churn) — были активны в предыдущем месяце, но перестали логиниться в текущем месяце.
Подход к решению
Логика:
Вариант 1: С использованием самообъединения (Self-Join)
Решение
A/B тест: Анализ статистической значимости
Провожу полный статистический анализ А/В теста с использованием z-теста для сравнения пропорций.
1. Вычисление конверсии для каждой группы
Контрольная группа (A):
Тестовая группа (B):
Абсолютная разница (Lift):
Относительное улучшение:
Тестовая группа показывает на 15% лучше конверсию, чем контрольная.
2. Проверка статистической значимости (Z-тест)
Гипотезы:
Решение
Задача требует подсчета удержанных пользователей (retained users) — тех, кто авторизовался и в текущем, и в предыдущем месяце. Это классический метрический расчёт для оценки retention rate.
Подход к решению
Этап 1: Определяем уникальных пользователей по месяцам.
Этап 2: Для каждого месяца находим пересечение пользователей с предыдущим месяцем.
Этап 3: Подсчитываем количество удержанных пользователей.
Вариант 1: С использованием self-join (самообъединение)
WITH monthly_users AS (
SELECT
DATE_TRUNC('month', login_date) AS month,
user_id
FROM logins
GROUP BY DATE_TRUNC('month', login_date), user_id
)
SELECT
curr.month,
COUNT(DISTINCT curr.user_id) AS retained_users
FROM monthly_users curr
INNER JOIN monthly_users prev
ON curr.user_id = prev.user_id
AND curr.month = prev.month + INTERVAL '1 month'
GROUP BY curr.month
ORDER BY curr.month;
Вариант 2: С использованием оконных функций (более эффективный)
Решение
Задача требует расчёта ежемесячного процентного изменения MAU (Monthly Active Users). Это ключевая метрика для отслеживания роста пользовательской базы.
Подход к решению
Логика:
SQL-запрос
WITH monthly_mau AS (
SELECT
DATE_TRUNC('month', date) AS month,
COUNT(DISTINCT user_id) AS mau
FROM logins
GROUP BY DATE_TRUNC('month', date)
)
SELECT
TO_CHAR(month, 'YYYY-MM') AS month,
mau,
ROUND(
(
(mau - LAG(mau) OVER (ORDER BY month)) /
LAG(mau) OVER (ORDER BY month) * 100
)::NUMERIC,
2
) AS mau_change_pct
FROM monthly_mau
ORDER BY month ASC;
Пошаговое объяснение
Решение
Задача требует расчета процента успешных звонков (дозвонов) для каждого дня в указанном периоде. Необходимо вычислить долю звонков с флагом dozv_flg = 1 от общего количества звонков в день.
Подход к решению
Этап 1: Группируем звонки по дате.
Этап 2: Подсчитываем общее количество звонков в день.
Этап 3: Подсчитываем количество успешных звонков (dozv_flg = 1).
Этап 4: Рассчитываем процент успешности.
SQL-запрос
SELECT
call_date,
COUNT(*) AS call_count,
SUM(dozv_flg) AS success_count,
ROUND(
CAST(SUM(dozv_flg) AS NUMERIC) / COUNT(*) * 100,
2
) AS success_rate
FROM calls
WHERE call_date >= '2024-10-01'
GROUP BY call_date
ORDER BY call_date ASC;
Пошаговое объяснение
Решение
MAU (Monthly Active Users) — это ключевая метрика для анализа активности приложения. Задача состоит из двух частей: сначала найти уникальных пользователей за каждый месяц, а затем вычислить среднее значение по всем месяцам.
Подход к решению
Этап 1: Группируем данные по месяцам и считаем уникальных пользователей в каждом месяце.
Этап 2: Вычисляем среднее значение MAU по всем месяцам.
SQL-запрос
WITH monthly_users AS (
SELECT
DATE_TRUNC(month, activity_date) AS month,
COUNT(DISTINCT client_id) AS mau
FROM clients
GROUP BY DATE_TRUNC(month, activity_date)
)
SELECT
ROUND(AVG(mau)::NUMERIC, 2) AS average_mau
FROM monthly_users;
Альтернативный вариант (для MySQL/SQLite)
Если используется MySQL или SQLite, можно применить функцию DATE_FORMAT:
Решение
Задача требует нахождения клиентов, которые совершили определённую покупку (Gel), но не совершили другую (Soap). Это классическая задача на использование подзапросов и логики исключения. Существует несколько эффективных подходов.
Вариант 1: NOT EXISTS (рекомендуемый)
SELECT DISTINCT
c.customer_id,
c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM purchases p
WHERE p.customer_id = c.customer_id AND p.product = 'Gel'
)
AND NOT EXISTS (
SELECT 1 FROM purchases p
WHERE p.customer_id = c.customer_id AND p.product = 'Soap'
);
Вариант 2: С использованием подзапросов IN/NOT IN
SELECT
c.customer_id,
c.name
FROM customers c
WHERE c.customer_id IN (
SELECT DISTINCT customer_id FROM purchases WHERE product = 'Gel'
)
AND c.customer_id NOT IN (
SELECT DISTINCT customer_id FROM purchases WHERE product = 'Soap'
);
Вариант 3: С GROUP BY и HAVING
Решение
Задача требует нахождения сотрудника (или сотрудников) с максимальной зарплатой в каждом отделе. Ключевой момент — обработка случаев, когда несколько сотрудников имеют одинаковую максимальную зарплату.
Подход к решению
Оконные функции RANK() или DENSE_RANK() позволяют ранжировать зарплаты внутри каждого отдела и выбрать те, которые имеют ранг 1 (максимальные).
SQL-запрос (рекомендуемый вариант)
WITH ranked_salaries AS (
SELECT
depname,
empno,
salary,
RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank
FROM salaries
)
SELECT
depname,
empno,
salary
FROM ranked_salaries
WHERE rank = 1
ORDER BY depname, empno;
Пошаговое объяснение
1. CTE ranked_salaries
PARTITION BY depname — окно считается отдельно для каждого отделаORDER BY salary DESC — сортируем по зарплате в убывающем порядкеRANK() — присваивает ранг (при одинаковых значениях ранг одинаковый, следующий скачет)Решение
Задача требует расчета нарастающей суммы (cumulative sum) денежного потока с использованием оконных функций SQL. Это классический пример применения функции SUM() в окне с рамкой UNBOUNDED PRECEDING.
Подход к решению
Оконная функция SUM() OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) вычисляет сумму от первой строки до текущей для каждого дня.
SQL-запрос
SELECT
date,
cash_flow,
SUM(cash_flow) OVER (
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_cf
FROM transactions
ORDER BY date ASC;
Пошаговое объяснение
1. Оконная функция SUM()
OVER (ORDER BY date ...) — определяет окно (partition) для функцииРешение
Задача требует вычисления 7-дневного скользящего среднего (moving average) для ежедневных регистраций с использованием оконных функций SQL. Скользящее среднее — это среднее значение за последние N дней, включая текущий день.
Подход к решению
Оконная функция AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) вычисляет среднее за 7 дней (текущий + 6 предыдущих).
SQL-запрос
SELECT
date,
sign_ups,
ROUND(
AVG(sign_ups) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)::NUMERIC,
2
) AS moving_avg_7d
FROM signups
ORDER BY date ASC;
Пошаговое объяснение
1. Оконная функция AVG()
OVER (ORDER BY date ...) — определяет окно функцииРешение
Задача требует нахождения пар штатов с похожей популярностью потоков. Ключевой момент — исключить дубликаты пар и пары с самим собой.
Подход к решению
Логика:
SQL-запрос (основной вариант)
SELECT
s1.state AS state1,
s2.state AS state2,
s1.total_streams AS streams1,
s2.total_streams AS streams2,
ABS(s1.total_streams - s2.total_streams) AS streams_diff
FROM state_streams s1
CROSS JOIN state_streams s2
WHERE s1.state < s2.state -- Исключает дубликаты и пары с самим собой
AND ABS(s1.total_streams - s2.total_streams) <= 1000
ORDER BY s1.state, s2.state;
Пошаговое объяснение
Решение
Задача требует условной классификации пользователей с приоритетом: если пользователь имеет записи обоих классов, его нужно отнести только к классу "b".
Подход к решению
Логика:
SQL-запрос (основной вариант)
Решение
Задача требует нахождения времени отклика на письма, полученные на адрес поддержки. Нужно соединить исходное письмо с первым ответом и вычислить разницу во времени.
Подход к решению
Логика:
SQL-запрос (рекомендуемый вариант)
Решение
Анализ данных о длительности звонков в call-центре
Проведу комплексный анализ с использованием статистических методов и визуализаций.
1. План анализа
Этап 1: Описательная статистика
SELECT
COUNT(*) AS total_calls,
MIN(duration_seconds) AS min_duration,
MAX(duration_seconds) AS max_duration,
AVG(duration_seconds)::INT AS avg_duration,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_seconds) AS median_duration,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY duration_seconds) AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration_seconds) AS q3,
STDDEV(duration_seconds)::INT AS std_dev,
VARIANCE(duration_seconds)::INT AS variance,
SKEWNESS(duration_seconds) AS skewness,
KURTOSIS(duration_seconds) AS kurtosis
FROM calls;
Решение
Задача требует создания гистограммы — распределения сессий по интервалам длительности. Это типичная задача для анализа пользовательского поведения и создания визуализаций.
Подход к решению
Логика:
SQL-запрос (основной вариант)
WITH bucketed_sessions AS (
SELECT
session_id,
length_seconds,
(FLOOR(length_seconds / 5.0) * 5)::INT AS bucket_start,
(FLOOR(length_seconds / 5.0) * 5 + 5)::INT AS bucket_end
FROM sessions
)
SELECT
bucket_start || '-' || bucket_end AS bucket,
COUNT(*) AS session_count
FROM bucketed_sessions
GROUP BY bucket_start, bucket_end
ORDER BY bucket_start ASC;
Пошаговое объяснение
Решение
Задача требует классификации узлов дерева по их типам (Root, Leaf, Inner). Это типичная задача анализа иерархических структур, часто встречается при работе с каталогами, организационными структурами и ситемами меню.
Подход к решению
Логика классификации:
SQL-запрос
SELECT
t.node,
CASE
WHEN t.parent IS NULL THEN 'Root'
WHEN NOT EXISTS (SELECT 1 FROM tree WHERE parent = t.node) THEN 'Leaf'
ELSE 'Inner'
END AS type
FROM tree t
ORDER BY t.node;
Пошаговое объяснение
1. Условие для Root
WHEN t.parent IS NULL — проверяем, есть ли родитель