Star schema: факт + измерения

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

**Компания:** Озон

**Контекст:** Нужно спроектировать star schema для аналитики заказов в e-commerce. Основные разрезы анализа: товар, покупатель, дата, регион.

**Задание:**
1. Напишите DDL для таблицы фактов `fact_orders` и четырёх измерений
2. Объясните выбор суррогатных ключей vs натуральных
3. Напишите аналитический запрос: выручка по категориям товаров по месяцам

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

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

-- Измерение: дата
CREATE TABLE dim_date (
    date_sk INT PRIMARY KEY,           -- YYYYMMDD
    full_date DATE NOT NULL,
    year INT, quarter INT, month INT,
    month_name VARCHAR(20),
    day_of_week INT, day_name VARCHAR(20),
    is_weekend BOOLEAN
);

-- Измерение: товар
CREATE TABLE dim_product (
    product_sk SERIAL PRIMARY KEY,
    product_id INT NOT NULL,           -- натуральный ключ
    product_name VARCHAR(300),
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100)
);

-- Измерение: покупатель
CREATE TABLE dim_customer (
    customer_sk SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    name VARCHAR(200),
    segment VARCHAR(50),
    registration_date DATE
);

-- Измерение: регион
CREATE TABLE dim_region (
    region_sk SERIAL PRIMARY KEY,
    city VARCHAR(100),
    region VARCHAR(100),
    federal_district VARCHAR(100)
);

-- Таблица фактов
CREATE TABLE fact_orders (
    order_sk BIGSERIAL PRIMARY KEY,
    date_sk INT REFERENCES dim_date(date_sk),
    product_sk INT REFERENCES dim_product(product_sk),
    customer_sk INT REFERENCES dim_customer(customer_sk),
    region_sk INT REFERENCES dim_region(region_sk),
    order_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price NUMERIC(12,2),
    total_amount NUMERIC(12,2),
    discount_amount NUMERIC(12,2) DEFAULT 0
);

Темы

star schema DWH факт измерения DDL

Подсказки

• Факт содержит FK на измерения + числовые меры (amount, quantity)
• Суррогатный ключ (SERIAL) изолирует DWH от изменений в источнике
• dim_date — предзаполненная таблица с атрибутами дат (год, месяц, день недели)

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

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

Какой уровень знаний нужен для задачи "Star schema: факт + измерения"?

Это задание для уровня easy. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

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

Подобные задания в категории «data_engineering» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: star schema, DWH, факт, измерения, DDL.

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

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

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

На zasqlpython.ru есть другие задания в категории «data_engineering», продуктовые кейсы, справочник метрик, AI мок-собеседование с разбором ваших ответов.

← Все задания