Это должен знать каждый аналитик, чтобы пройти собеседование в Big Tech. Сегодня разберём несколько вопросов по SQL с реальных собеседований в топовые IT компании, если вы с чем-то не справились это знак пойти повторять SQL

1. В чём разница между WHERE и HAVING в SELECT-запросе?

Ответ: WHERE фильтрует строки до группировки, HAVING фильтрует уже сгруппированные данные.
Например есть задача "Какой запрос SQL вернёт максимальную зарплату сотрудников для каждого отдела, где средняя з/п по отделу превышает 5000?". Сначала нужно сгруппировать по отделам, а затем в HAVING отфильтровать по средней з/п, если это сделать в WHERE будет ошибка.

2. Допустим в таблице есть 3 столбца user_id, salary, employees. В чём разница между count(*) и count(employees)?

Ответ: count(*) считает все строки таблицы, а count(employees) считает только те строки, где в столбце employees значение не NULL

3. Как в PostgreSQL работать с NaN для числовых типов: чем он отличается от NULL и что вернут COALESCE(NaN::numeric, 0) и NULLIF(NaN::numeric, NaN::numeric)?

Ответ: NaN в PostgreSQL — это реальное числовое значение (Not-a-Number), а не отсутствие данных: оно хранится в ячейке, участвует в арифметике и сравнениях. NULL значит, что значения нет вовсе
Поэтому count(..), avg(..) не посчитают ячейки с NULL, но учтут ячейки с NaN

COALESCE(value [, ...]) возвращает первый ненулевой аргумент. Поэтому COALESCE(NaN::numeric, 0) вернёт сам NaN (первый аргумент не NULL)
NULLIF(value1, value2) возвращает NULL, если value1 = value2, иначе вернёт value1. Поэтому NULLIF(NaN::numeric, NaN::numeric) вернёт NULL

4. Допустим в двух разных таблицах есть колонки user_id и employees_id. В каком случае JOIN выполнится быстрее, если обе колонки будут типа numeric(11) или varchar(10)?

Ответ: numeric(11) быстрее, потому что значения хранятся как упорядоченные числа фиксированной длины и сравнение двух чисел — это одна бинарная операция. varchar(10) каждый ключ хранит длину и символы, соответственно для сравнения нужно пройтись посимвольно, что медленнее, а сами индексы больше (нужно читать больше страниц и в рабочем кэше помещается меньше записей).
Если БД очень важна использование числового тип данных может существенно ускорить работу запросов.

5. Какой компонент отвечает за оптимизацию и преобразование SQL-запроса в план выполнения?

Ответ: Query Processor (оптимизатор запросов)
Оптимизатор учитывает такие факторы, как количество записей, наличие индексов, партиций и т.д. На основе этого он строит план EXPLAIN — последовательность операций, которые
выполняет БД для получения требуемого в запросе результата.

6. Какой компонент восстанавливает базу после сбоя?

Ответ: Recovery Manager
Вместе с изменением данных ведется ещё и журнал этих изменений. Recovery manager читает журнал предзаписи WAL, повторно выполняет все зафиксированные изменения после контрольной точки (redo) и откатывает изменения незавершённых транзакций (undo)

7. Что такое партиции и где они хранятся?

Ответ: партиционирование — это логическое разделение данных на части (партиции) по заданным критериям, используется в основном для больших таблиц и позволяет избежать полного сканирования таблицы. Может располагаться во всех сегментах

@ProdAnalysis

Ratio - метрики

Ratio-метрики вроде CTR, ARPU или среднего чека часто ломают привычный подход к A/B-тестам, потому что в них числитель и знаменатель зависимы: у пользователя с большими "views" обычно больше и "clicks", у крупного покупателя выше и "revenue", и "orders". Если применить стандартный t-test как к обычной метрике, дисперсия получается оценена неверно и итоговое p-value начинает врать. Delta-method как раз помогает аккуратно оценить дисперсию метрики вида num / denom с учётом ковариации между числителем и знаменателем и дальше уже работать с этой метрикой как с обычной: считать размер выборки, uplift, доверительные интервалы и значимость. Удобная реализация для данных на уровне пользователя:

def est_ratio_var(num, denom):
    mean_num, mean_denom = np.mean(num), np.mean(denom)
    var_num, var_denom = np.var(num), np.var(denom)
    
    cov = np.cov(num, denom)[0, 1]
    
    ratio_var = (
        (var_num / mean_denom ** 2)
        - (2 * (mean_num / mean_denom ** 3) * cov)
        + ((mean_num ** 2 / mean_denom ** 4) * var_denom)
    )
    
    return ratio_var

Дальше посмотрим на CTR, где по каждому "user_id" есть "clicks" и "views". Для расчёта размера выборки можно сначала оценить дисперсию CTR в текущих данных, задать относительный MDE и перевести его в "cohen_d", а затем воспользоваться стандартной функцией мощности, например так:

rel_mde = 0.1

var = est_ratio_var(df["clicks"], df["views"])
control_std = var**0.5

control_mean = df["clicks"].sum() / df["views"].sum()
test_mean = control_mean * (1 + rel_mde)

cohen_d = (test_mean - control_mean) / control_std

n1 = tt_ind_solve_power(
    effect_size=cohen_d,
    alpha=0.05,
    power=0.8,
    ratio=9,
    alternative="two-sided"
)

print(
    f"Размер выборки delta-method: "
    f"n1 = {round(n1)}, "
    f"n2 = {round(9 * n1)}"
)

Когда эксперимент уже прошёл, по тем же группам можно оценить статистическую значимость uplift CTR. Для этого считаем итоговые "clicks", "views" по группам, через delta-method получаем дисперсии и дальше строим нормальную статистику и p-value:

def delta_method(group_A, group_B):
    ratio_A = group_A["clicks"].sum() / group_A["views"].sum()
    ratio_B = group_B["clicks"].sum() / group_B["views"].sum()

    var_A = est_ratio_var(group_A["clicks"], group_A["views"])
    var_B = est_ratio_var(group_B["clicks"], group_B["views"])

    uplift = ratio_B - ratio_A
    se = np.sqrt(var_B / len(group_B) + var_A / len(group_A))

    t = uplift / se
    p_val = (1 - stats.norm.cdf(abs(t))) * 2
    
    return p_val

Delta-method особенно удобен тем, что оставляет привычную для бизнеса картинку: "uplift", стандартная ошибка, доверительный интервал и p-value, но при этом корректно учитывает зависимость в ratio-метриках. Для надёжности в реальных задачах полезно иногда сверяться с бутстрепом или линеаризацией, но как базовый рабочий инструмент delta-method уже даёт аккуратные и довольно устойчивые оценки. Если формат такого кода и пояснений заходит, можно отдельно разобрать "линеаризацию" или "бутстреп" для тех же CTR и ARPU.

@ProdAnalysis

Оптимизации SQL

Оптимизация SQL в основном это уменьшение числа читаемых строк и корректная работа планировщика с индексами, а не сложный синтаксис запросов. Типичный кейс: большая таблица orders с полями user_id, created_at, status, amount и ежедневный отчёт по выручке за последние 30 дней. Наивный запрос:

SELECT
    user_id,
    SUM(amount) AS revenue
FROM orders
WHERE created_at >= now() - interval "30 days"
  AND status IN ("paid", "completed")
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 100;

По мере роста данных планировщик часто делает Seq Scan, потому что фильтр мало селективен и разрозненные индексы по created_at и status не помогают. Практичный подход — спроектировать один составной и покрывающий индекс под шаблон запроса:

CREATE INDEX CONCURRENTLY idx_orders_status_created_user
ON orders (status, created_at, user_id)
INCLUDE (amount);

Фильтры и JOIN должны быть sargable: столбцы используются напрямую, без обёртки в функции. Вариант WHERE date_trunc("day", created_at) >= current_date - interval "30 days" часто ломает использование индекса по created_at, и в таком случае либо переписывают условие на диапазон по created_at, либо заводят функциональный индекс:

CREATE INDEX CONCURRENTLY idx_orders_created_day
ON orders (date_trunc("day", created_at));

Во многих системах часть нагрузки создаёт ORM через шаблон N+1, когда вместо одного агрегирующего запроса выполняются десятки маленьких. Чаще всего их можно заменить одним запросом с JOIN и агрегацией:

SELECT
    u.id,
    u.name,
    SUM(o.amount) AS revenue_30d
FROM users u
JOIN orders o
  ON o.user_id = u.id
WHERE o.created_at >= now() - interval "30 days"
  AND o.status IN ("paid", "completed")
GROUP BY u.id, u.name
ORDER BY revenue_30d DESC
LIMIT 100;

Под рабочие запросы разумно делать частичные индексы только по нужным данным, например:

CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at, user_id)
WHERE status IN ("paid", "completed");

Ключевая идея при любой оптимизации - смотреть планы через EXPLAIN (ANALYZE, BUFFERS) и сравнивать их до и после изменений: тип скана, количество строк, время выполнения. Получается, что при написании эффективного кода мы выделяем важные запросы, делаем под них индексы с продуманным порядком колонок, формулируем условия в sargable виде и избегаем N+1 и тяжёлых агрегаций по сырым данным.

А также напоминаем, что уже скоро стартует наш курс по SQL, где вас научат писать сложные, эффективные запросы с нуля, а также покажут основы устройства и проектирования БД😎

@ProdAnalysis

Разбор на стажировку Яндекса

Финальные 6 часов скидок на наши курсы подходят к концу. В честь этого выкладываем разбор актуального контеста в Яндекс:
— Бэкенд
— Аналитика
— ML & DS

Обязательно пересылайте такую годноту своим друзьям и одногруппникам в чаты. Больше подписчиков — больше разборов.

@postypashki_old

Товарищи, Поступашкам нужны контент мейкеры. Если вы творческая личность, интересующейся фронтендом, бэкендом, дата сайнс, аналитикой, алгоритмами и тд, вам нравится писать посты/ придумывать идеи для контента, то обязательно пишите @vice22821. Оплата сдельная, ориентировочно за один пост от 2 тыс до 15 тыс рублей.

Обязательно делитесь с ребятами, которым это может быть интересно.

Т-академия

Экзамены до 22 января. Курсы для фаст трека на стажировку. Податься одновременно и на стажировку и на академию нельзя, только если с фейков. Задания уже выложены тут. Разбор алгоритмов для аналитиков и бэкендеров будет только на нашем курсе по алгоритмам, на который скидка заканчивается уже сегодня.

Разбор экзамена по SQL Т-банка

Разбор всех направлений выложен только на наших курсах. До конца дня действует льготная цена 13450 8950. Записываемся!

@ProdAnalysis

задача 1

select count(distinct student_id)
from (
select student_id
from enrollments
where status = 'completed'
) t

задача 2

select s.city
from students s
join enrollments e on s.student_id = e.student_id
where status = 'completed'
group by city
order by sum(e.progress_pct) desc, s.city asc
limit 1;

задача 3

select round(avg(rating)::numeric, 2)
from reviews;

задача 4

with cte_1 as (
select course, student_id, count(student_id) as cnt
from enrollments
where status = 'completed'
group by course, student_id
), cte_2 as (
select course, sum(case when cnt >= 2 then 1 else 0 end) as repeat_students, sum(cnt) as completed
from cte_1
group by course
)
select course
from cte_2
order by repeat_students desc, completed desc, course asc
limit 1;

Ментор+

Эта услуга для тех, кто хочет трудоустроиться уже сейчас без лишней головной боли и теории.

Что мы обещаем
Достигнуть ваши цели, такие как получить оффер на стажировку, получить оффер в Бигтех с грейдом не ниже вашего текущего. Мы обсуждаем сроки. Если мы не в состоянии достигнуть ваших целей, то мы сразу же вам об этом скажем и предложим альтернативу (расширим пул компаний).

Что мы не обещаем
Мы не можем обещать нереальных или почти нереальных результатов как "оффер на 500 тыс (стажер)".
Мы не можем обещать попадание в конкретную компанию.

Как мы достигаем цели?

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

Если вам нужно проработать легенду и составить резюме, но вы хотите проходить собеседования полностью самостоятельно, то это отдельная услуга. Она стоит 15 тысяч рублей. Для записи: @vice22821.

2. Прохождение собеседований
Подбираем с вами вакансии, проходим с вами собеседования, сидя у вас на наушнике, проходим лайвкодинг с помощью удаленного доступа.

Если вам разово нужен специалист, который поможет на собеседовании: посидит на наушнике или пройдет лайвкодинг с удаленного доступа, то это отдельная услуга. Ориентировочная цена 10 тыс за пол часа собеса. Для записи: @vice22821

3. Теоретические занятия
Теоретические занятия в ментор+ не входят, ментор+ для тех, кому нужно трудоустройство. Если вам нужны знания, вам подойдут курсы.

Если у вас непростая ситуация и требуется консультация специалиста, то это отдельная услуга. Она стоит 8 тыс в час. Для записи: @vice22821.

Если вам нужно мок собеседование, то это отдельная услуга. Она стоит 8 тыс в час. Для записи @vice22821.

Как записаться
Написать @vice22821 со своим запросом. Мы обсудим насколько ваша цель достижима, сколько на это потребуется времени и готовы ли мы взяться за ваш заказ. Если вас все устроит, то начнем работать.

Оплата ментор +
Залог составляет 30 тыс. Уже после получения оффера нужно внести 1 месячную зарплату после вычета налогов.

Что если не получится
Если не получилось получить оффер по нашей вине (после собеседований в 5 компаний у вас нет оффера) в установленные сроки, то мы возвращаем вам залог.

Если не получилось получить оффер по вашей вине (например, вы решили отказаться от сотрудничества) в установленные сроки, то залог не возвращается.

Ответы на часто задаваемые вопросы

1. Сколько в среднем вся работа займет времени?
Если с вашей стороны нет задержек (например, уехали в отпуск), то максим 6 месяцев. Обычно удается получить оффер за 3 месяца.

2. Есть ли сопровождение на испытательном сроке?
Не входит в ментор+, отдельная услуга и обсуждается индивидуально.

Мы в поиске новых ТАЙНЫХ КЛИЕНТОВ!

🔝Работаем только с проверенными крупными компаниями.
🚀Прямо сейчас ты можешь начать и заработать свои первые 2–3 000 ₽ ЗА 1 ДЕНЬ!

Список актуальных заданий:

1.Тайный клиент в магазинах. Проверка качества персонала — 1 000 ₽.

2.Тайный клиент онлайн-доставок. Заказ оплачивается — 500 ₽.

3.Тайный клиент доставки цветов — 700 ₽.

4.Тайный клиент банковских продуктов — от 500 ₽.
Чтобы узнать актуальные задания пиши мне -@hheaddachee