Ранжирование продавцов по GMV с возвратами и штрафами

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

**Контекст:** Маркетплейс ранжирует продавцов по «чистому» GMV (Gross Merchandise Value) с учётом возвратов и штрафов. Рейтинг используется для распределения промо-слотов на главной странице.

**Данные:**

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

**Задание:**
1. Для каждого продавца рассчитайте за последний месяц: gross GMV, сумму возвратов, сумму штрафов, net GMV (= gross - возвраты - штрафы)
2. Рассчитайте комиссию маркетплейса (SUM of gmv × commission_rate)
3. Ранжируйте продавцов по net GMV внутри каждой категории через RANK()
4. Выведите топ-3 продавца в каждой категории

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

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

CREATE TABLE sellers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100) NOT NULL,        -- 'Электроника', 'Одежда', 'Дом', ...
    registered_at TIMESTAMP NOT NULL
);

CREATE TABLE seller_orders (
    id SERIAL PRIMARY KEY,
    seller_id INT REFERENCES sellers(id),
    order_id INT NOT NULL,
    gmv NUMERIC(12,2) NOT NULL,
    commission_rate NUMERIC(5,4) NOT NULL,  -- 0.05–0.20
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE seller_returns (
    id SERIAL PRIMARY KEY,
    seller_order_id INT REFERENCES seller_orders(id),
    refund_amount NUMERIC(12,2) NOT NULL,
    returned_at TIMESTAMP NOT NULL
);

CREATE TABLE seller_penalties (
    id SERIAL PRIMARY KEY,
    seller_id INT REFERENCES sellers(id),
    penalty_type VARCHAR(50) NOT NULL,     -- 'late_shipment', 'counterfeit', 'bad_reviews'
    amount NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

Темы

CTE LEFT JOIN COALESCE RANK агрегация

Подсказки

• Соберите метрики по отдельности в CTE, затем соедините
• COALESCE(..., 0) для возвратов и штрафов, так как не у всех продавцов они есть
• RANK() OVER (PARTITION BY category ORDER BY net_gmv DESC) для ранжирования внутри категории

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

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

Какой уровень знаний нужен для задачи "Ранжирование продавцов по GMV с возвратами и штрафами"?

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

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

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

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

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

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

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

← Все задания