Процент подтверждённых SMS по операторам

Условие задания

**Данные:** Две таблицы телеком-компании — `sms_sent` (отправка) и `sms_delivery` (доставка):

[см. код в задании]

**Задание:** Для каждого оператора и типа сообщения за последнюю неделю рассчитайте:
1. Общее количество отправленных SMS
2. Количество доставленных (`status = 'delivered'`)
3. Процент доставки (delivery rate)
4. Среднее время доставки в секундах

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE sms_sent (
    id SERIAL PRIMARY KEY,
    phone_number VARCHAR(20) NOT NULL,
    operator VARCHAR(50) NOT NULL, -- 'МТС', 'Билайн', 'Мегафон', 'Tele2'
    message_type VARCHAR(30) NOT NULL, -- 'transactional', 'marketing', 'otp'
    sent_at TIMESTAMP NOT NULL
);

CREATE TABLE sms_delivery (
    sms_id INT NOT NULL REFERENCES sms_sent(id),
    status VARCHAR(20) NOT NULL, -- 'delivered', 'failed', 'expired'
    delivered_at TIMESTAMP
);

Темы

LEFT JOIN условная агрегация CASE WHEN проценты

Подсказки

• LEFT JOIN sms_sent с sms_delivery — не все SMS имеют подтверждение
• CASE WHEN status = 'delivered' THEN 1 END внутри COUNT для подсчёта доставленных
• EXTRACT(EPOCH FROM delivered_at - sent_at) даст время доставки в секундах

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Процент подтверждённых SMS по операторам"?

Это задание для уровня Middle. Для middle-аналитиков с опытом 1-3 года, требует уверенного владения темой и понимания edge cases.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: LEFT JOIN, условная агрегация, CASE WHEN, проценты.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 15-30 минут — оцениваются подход, корректность, обработка edge cases. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания