30-дневная активная база по городам

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

**Контекст:** Задание по мотивам реального тестового в Dodo Brands. Продуктовый аналитик рассчитывает MAU (30-дневную активную базу) по городам для отчёта о «здоровье» рынков.

**Данные:**

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

| cityname | date | client_id | sales |
|----------|------------|-----------|--------|
| Москва | 2025-01-05 | 1 | 500.00 |
| Москва | 2025-01-10 | 2 | 300.00 |
| СПб | 2025-01-08 | 6 | 420.00 |
| ... | ... | ... | ... |

**Задание:**

1. Для каждой пары (город, дата из данных) посчитайте количество уникальных клиентов, сделавших хотя бы 1 заказ за предыдущие 30 дней (включая эту дату)
2. Выведите cityname, date и active_clients_30d

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

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

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    cityname VARCHAR(100) NOT NULL,
    date DATE NOT NULL,
    client_id INT NOT NULL,
    sales NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (cityname, date, client_id, sales) VALUES
('Москва', '2025-01-05', 1, 500.00),
('Москва', '2025-01-10', 2, 300.00),
('Москва', '2025-01-15', 1, 450.00),
('Москва', '2025-01-20', 3, 600.00),
('Москва', '2025-02-01', 4, 350.00),
('Москва', '2025-02-05', 1, 400.00),
('Москва', '2025-02-10', 5, 550.00),
('Москва', '2025-02-15', 2, 300.00),
('СПб',    '2025-01-08', 6, 420.00),
('СПб',    '2025-01-18', 7, 380.00),
('СПб',    '2025-01-25', 6, 500.00),
('СПб',    '2025-02-02', 8, 310.00),
('СПб',    '2025-02-12', 7, 450.00),
('Казань', '2025-01-12', 9, 270.00),
('Казань', '2025-02-08', 9, 320.00);

Темы

COUNT DISTINCT скользящее окно CROSS JOIN дата-спайн

Подсказки

• Для каждой строки нужно посчитать COUNT(DISTINCT client_id) в окне [date-29, date]
• Self-join: JOIN orders o2 ON o1.cityname = o2.cityname AND o2.date BETWEEN o1.date - 29 AND o1.date
• Альтернатива — RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW (PostgreSQL 11+)

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

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

Какой уровень знаний нужен для задачи "30-дневная активная база по городам"?

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

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

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

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

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

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

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

← Все задания