**Задание по мотивам реального тестового в Aviasales.**
**Контекст:** Вам дали SQL-запрос для анализа воронки оплаты авиабилетов. Запрос содержит 5 ошибок, которые нужно найти и исправить. Запрос используется в Python-скрипте.
**Исходный (сломанный) SQL:**
[см. код в задании]
**Задание:**
1. Найдите все 5 ошибок в SQL
2. Напишите исправленную версию
3. Реализуйте на Python (pandas) эквивалент этого запроса на синтетических данных
4. Визуализируйте воронку конверсий
Структура для ориентира — реальные значения из эталонного решения.
import pandas as pd
import numpy as np
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
# ========================================
# 1. Пять ошибок в SQL
# ========================================
errors = """
Ошибка 1: COUNT(CASE WHEN clicked = 1)
→ Пропущен THEN 1 END
→ Исправление: COUNT(CASE WHEN clicked = 1 THEN 1 END)
Ошибка 2: LEFT JOIN payment_events p ON c.click_id = c.click_id
→ Self-join! Должно быть: p.click_id = c.click_id
→ Из-за этого каждый клик джойнится со ВСЕМИ платежами
Ошибка 3: clicks / searches AS ctr
→ Целочисленное деление → всегда 0
→ Исправление: clicks * 1.0 / NULLIF(searches, 0)
Ошибка 4: ROW_NUMBER() OVER (ORDER BY revenue)
→ Для "топ-10 по выручке" нужен DESC
→ ROW_NUMBER() OVER (ORDER BY revenue DESC)
Ошибка 5: WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31'
→ BETWEEN включает начало дня 31.03, но не конец
→ Для корректности: created_at >= '2024-01-01'
AND created_at < '2024-04-01'
"""
print(errors)
# ========================================
# 2. Исправленный SQL
# ========================================
fixed_sql = """
WITH funnel AS (
SELECT
date_trunc('day', s.created_at) AS day,
COUNT(DISTINCT s.search_id) AS searches,
COUNT(DISTINCT CASE WHEN c.click_id IS NOT NULL THEN s.search_id END) AS clicks,
COUNT(DISTINCT CASE WHEN c.redirected = 1 THEN c.click_id END) AS redirects,
COUNT(DISTINCT CASE WHEN p.paid = 1 THEN p.payment_id END) AS payments,
COALESCE(SUM(p.revenue), 0) AS revenue
FROM search_events s
LEFT JOIN click_events c ON s.search_id = c.search_id
LEFT JOIN payment_events p ON c.click_id = p.click_id -- FIX: p.click_id
WHERE s.created_at >= '2024-01-01'
AND s.created_at < '2024-04-01' -- FIX: < вместо BETWEEN
GROUP BY 1
),
conversion AS (
SELECT *,
clicks * 1.0 / NULLIF(searches, 0) AS ctr, -- FIX: float + NULLIF
redirects * 1.0 / NULLIF(clicks, 0) AS redirect_rate,
payments * 1.0 / NULLIF(redirects, 0) AS payment_rate,
ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rn -- FIX: DESC
FROM funnel
)
SELECT day, searches, clicks, ctr, redirect_rate, payment_rate, revenue
FROM conversion
WHERE rn <= 10
ORDER BY revenue DESC;
"""
print("=== Исправленный SQL ===")
print(fixed_sql)
# ========================================
# 3. Python-эквивалент на синтетических данных
# ========================================
np.random.seed(42)
dates = pd.date_range('2024-01-01', '2024-03-31', freq='D')
data = []
for dt in dates:
searches = np.random.randint(8000, 15000)
click_rate = np.random.uniform(0.15, 0.25)
redirect_rate = np.random.uniform(0.40, 0.60)
payment_rate = np.random.uniform(0.03, 0.08)
avg_revenue = np.random.uniform(5000, 12000)
clicks = int(searches * click_rate)
redirects = int(clicks * redirect_rate)
payments = int(redirects * payment_rate)
revenue = payments * avg_revenue
data.append({
'day': dt, 'searches': searches, 'clicks': clicks,
'redirects': redirects, 'payments': payments, 'revenue': round(revenue),
})
df = pd.DataFrame(data)
# Конверсии (избегаем деления на 0)
df['ctr'] = (df['clicks'] / df['searches'].replace(0, np.nan) * 100).round(2)
df['redirect_rate'] = (df['redirects'] / df['clicks'].replace(0, np.nan) * 100).round(2)
df['payment_rate'] = (df['payments'] / df['redirects'].replace(0, np.nan) * 100).round(2)
# Топ-10 дней по выручке
top10 = df.nlargest(10, 'revenue')
print("=== Топ-10 дней по выручке ===")
print(top10[['day', 'searches', 'clicks', 'ctr',
'redirect_rate', 'payment_rate', 'revenue']].to_string(index=False))
# ========================================
# 4. Визуализация воронки
# ========================================
avg_funnel = df[['searches', 'clicks', 'redirects', 'payments']].mean()
stages = ['Поиски', 'Клики', 'Редиректы', 'Оплаты']
values = avg_funnel.values
fig, ax = plt.subplots(figsize=(10, 5))
bars = ax.barh(stages[::-1], values[::-1], color=['#FF5A1F', '#FF8C42', '#FFB347', '#4A5568'])
for bar, val in zip(bars, values[::-1]):
ax.text(bar.get_width() + 50, bar.get_y() + bar.get_height()/2,
f'{val:,.0f}', va='center', fontsize=11)
ax.set_title('Средняя воронка (в день)', fontsize=14)
ax.set_xlabel('Количество')
plt.tight_layout()
plt.savefig('payment_funnel.png', dpi=150)
print("\nГрафик: payment_funnel.png")python SQL отладка CTE воронка Aviasales
Это задание для уровня Middle. Для middle-аналитиков с опытом 1-3 года, требует уверенного владения темой и понимания edge cases.
Подобные задания в категории «Python» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: python, SQL, отладка, CTE, воронка.
На реальном собеседовании на подобную задачу отводится 15-30 минут — оцениваются подход, корректность, обработка edge cases. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.
На zasqlpython.ru есть 482 Python задачи с проверкой через Pyodide, конспекты Python и pandas, AI мок-собеседование с разбором ваших ответов.
← Все задания