Вопросы для собеседования на должность старшего аналитика данных: Полное руководство

-- ROW_NUMBER: Назначение уникальных номеров
SELECT 
    employee_id,
    first_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- RANK: Ранжирование сотрудников по зарплате в каждом отделе
SELECT 
    department,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG: Сравнение с предыдущей строкой
SELECT 
    year,
    revenue,
    LAG(revenue) OVER (ORDER BY year) AS prev_year_revenue,
    revenue - LAG(revenue) OVER (ORDER BY year) AS revenue_change
FROM annual_sales;

-- Накопительный итог
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Скользящее среднее (за последние 3 месяца)
SELECT 
    month,
    sales,
    AVG(sales) OVER (
        ORDER BY month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM monthly_sales;

-- Плохо: SELECT * и подзапрос
SELECT * FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE country = 'USA'
);

-- Хорошо: Конкретные столбцы и JOIN
SELECT o.order_id, o.order_date, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

-- Используйте EXPLAIN для анализа
EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';

-- Создайте индекс для повышения производительности
CREATE INDEX idx_order_date ON orders(order_date);

-- Используйте покрывающий индекс (включает все необходимые столбцы)
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, amount);

-- Разбейте большие таблицы на разделы
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Базовый CTE
WITH high_earners AS (
    SELECT employee_id, first_name, salary
    FROM employees
    WHERE salary > 80000
)
SELECT * FROM high_earners
WHERE first_name LIKE 'J%';

-- Множественные CTE
WITH 
sales_summary AS (
    SELECT 
        product_id,
        SUM(quantity) AS total_quantity,
        SUM(amount) AS total_revenue
    FROM sales
    GROUP BY product_id
),
product_info AS (
    SELECT product_id, product_name, category
    FROM products
)
SELECT 
    p.product_name,
    p.category,
    s.total_quantity,
    s.total_revenue
FROM sales_summary s
JOIN product_info p ON s.product_id = p.product_id
ORDER BY s.total_revenue DESC;

-- Рекурсивный CTE (организационная иерархия)
WITH RECURSIVE employee_hierarchy AS (
    -- Базовый случай: сотрудники верхнего уровня
    SELECT employee_id, first_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный случай: сотрудники, подчиняющиеся предыдущему уровню
    SELECT e.employee_id, e.first_name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level, employee_id;

-- UNION - удаляет дубликаты
SELECT customer_id FROM orders_2022
UNION
SELECT customer_id FROM orders_2023;
-- Результат: уникальные идентификаторы клиентов за оба года

-- UNION ALL - сохраняет дубликаты
SELECT customer_id FROM orders_2022
UNION ALL
SELECT customer_id FROM orders_2023;
-- Результат: все идентификаторы клиентов (могут быть дубликаты)

-- Сравнение производительности
-- UNION ALL быстрее, когда вы знаете, что дубликатов нет
SELECT 'Q1' AS quarter, revenue FROM q1_sales
UNION ALL
SELECT 'Q2', revenue FROM q2_sales
UNION ALL
SELECT 'Q3', revenue FROM q3_sales
UNION ALL
SELECT 'Q4', revenue FROM q4_sales;

-- Проверка на NULL
SELECT * FROM employees
WHERE manager_id IS NULL;  -- Не: = NULL

-- COALESCE: Возвращает первое значение, отличное от NULL
SELECT 
    first_name,
    COALESCE(middle_name, '') AS middle_name,
    COALESCE(bonus, 0) AS bonus
FROM employees;

-- NULLIF: Возвращает NULL, если значения равны
SELECT 
    product_name,
    NULLIF(discount, 0) AS discount  -- NULL, если скидка равна 0
FROM products;

-- NULL в вычислениях (NULL распространяется)
SELECT 
    salary,
    bonus,
    salary + bonus AS total  -- NULL, если бонус равен NULL
FROM employees;

-- Обработка NULL в агрегациях
SELECT 
    department,
    COUNT(*) AS total_employees,
    COUNT(manager_id) AS employees_with_manager,  -- Исключает NULL
    AVG(COALESCE(bonus, 0)) AS avg_bonus
FROM employees
GROUP BY department;

-- NULL в JOIN
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;  -- Сотрудники без отдела

-- Скалярный подзапрос
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Коррелированный подзапрос (выполняется для каждой строки)
SELECT e1.first_name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- EXISTS (эффективно для проверки существования)
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date > '2023-01-01'
);

-- IN с подзапросом
SELECT product_name
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM sales
    WHERE sale_date > '2023-01-01'
);

-- JOIN альтернатива (часто быстрее)
SELECT DISTINCT p.product_name
FROM products p
INNER JOIN sales s ON p.product_id = s.product_id
WHERE s.sale_date > '2023-01-01';

-- Когортный анализ: Ежемесячное удержание
WITH user_cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', first_purchase_date) AS cohort_month
    FROM users
),
user_activities AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', activity_date) AS activity_month
    FROM activities
)
SELECT 
    uc.cohort_month,
    ua.activity_month,
    COUNT(DISTINCT ua.user_id) AS active_users,
    COUNT(DISTINCT ua.user_id) * 100.0 / 
        COUNT(DISTINCT uc.user_id) AS retention_rate
FROM user_cohorts uc
LEFT JOIN user_activities ua ON uc.user_id = ua.user_id
GROUP BY uc.cohort_month, ua.activity_month
ORDER BY uc.cohort_month, ua.activity_month;

-- Когортный анализ доходов
SELECT 
    cohort_month,
    months_since_cohort,
    SUM(revenue) AS cohort_revenue,
    AVG(revenue) AS avg_revenue_per_user
FROM (
    SELECT 
        DATE_TRUNC('month', u.signup_date) AS cohort_month,
        EXTRACT(MONTH FROM AGE(o.order_date, u.signup_date)) AS months_since_cohort,
        o.revenue,
        u.user_id
    FROM users u
    JOIN orders o ON u.user_id = o.user_id
) cohort_data
GROUP BY cohort_month, months_since_cohort
ORDER BY cohort_month, months_since_cohort;

-- Анализ результатов A/B-теста
WITH test_results AS (
    SELECT 
        variant,
        COUNT(*) AS visitors,
        SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS conversions,
        SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS conversion_rate
    FROM ab_test_data
    GROUP BY variant
)
SELECT 
    variant,
    visitors,
    conversions,
    ROUND(conversion_rate, 2) AS conversion_rate_pct,
    -- Рассчитайте прирост
    ROUND((conversion_rate - LAG(conversion_rate) OVER (ORDER BY variant)) / 
          LAG(conversion_rate) OVER (ORDER BY variant) * 100, 2) AS lift_pct
FROM test_results;

-- Расчет статистической значимости (критерий хи-квадрат)
-- Обычно выполняется в Python/R, но можно рассчитать компоненты в SQL
SELECT 
    variant,
    conversions,
    visitors - conversions AS non_conversions,
    visitors
FROM ab_test_data
GROUP BY variant;

-- Рассчитайте процентили
SELECT 
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS p75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY salary) AS p90,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) AS p95
FROM employees;

-- Процентиль по группе
SELECT 
    department,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary) AS median_salary,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS p75_salary
FROM employees
GROUP BY department;

-- Назначьте ранги процентилей каждой строке
SELECT 
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percentile_rank,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

-- Межквартильный размах (IQR) для обнаружения выбросов
WITH quartiles AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3
    FROM employees
)
SELECT 
    e.*,
    CASE 
        WHEN e.salary < q.q1 - 1.5 * (q.q3 - q.q1) THEN 'Low Outlier'
        WHEN e.salary > q.q3 + 1.5 * (q.q3 - q.q1) THEN 'High Outlier'
        ELSE 'Normal'
    END AS outlier_status
FROM employees e
CROSS JOIN quartiles q;

-- Скользящее среднее (сглаживает сезонность)
SELECT 
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM daily_sales;

-- Сравнение год к году
SELECT 
    EXTRACT(MONTH FROM sale_date) AS month,
    EXTRACT(YEAR FROM sale_date) AS year,
    SUM(amount) AS monthly_sales,
    LAG(SUM(amount), 12) OVER (ORDER BY sale_date) AS same_month_last_year,
    (SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY sale_date)) / 
        LAG(SUM(amount), 12) OVER (ORDER BY sale_date) * 100 AS yoy_growth
FROM sales
GROUP BY EXTRACT(MONTH FROM sale_date), EXTRACT(YEAR FROM sale_date);

-- Расчет сезонного индекса
WITH monthly_avg AS (
    SELECT 
        EXTRACT(MONTH FROM date) AS month,
        AVG(sales) AS avg_sales
    FROM daily_sales
    GROUP BY EXTRACT(MONTH FROM date)
),
overall_avg AS (
    SELECT AVG(sales) AS overall_avg
    FROM daily_sales
)
SELECT 
    m.month,
    m.avg_sales,
    o.overall_avg,
    m.avg_sales / o.overall_avg AS seasonal_index
FROM monthly_avg m
CROSS JOIN overall_avg o
ORDER BY m.month;

-- Пример инкрементной загрузки
-- Извлечение: Получение новых/обновленных записей
CREATE TEMP TABLE staging_customers AS
SELECT *
FROM source_customers
WHERE updated_at > (
    SELECT MAX(last_updated) 
    FROM target_customers
);

-- Преобразование: Очистка и стандартизация
UPDATE staging_customers
SET 
    email = LOWER(TRIM(email)),
    phone = REGEXP_REPLACE(phone, '[^0-9]', '', 'g'),
    country = UPPER(country);

-- Загрузка: Обновление или вставка в целевой объект
INSERT INTO target_customers
SELECT * FROM staging_customers
ON CONFLICT (customer_id) 
DO UPDATE SET
    email = EXCLUDED.email,
    phone = EXCLUDED.phone,
    updated_at = EXCLUDED.updated_at;

-- Запись в журнал выполнения ETL
INSERT INTO etl_log (table_name, records_processed, run_date)
VALUES ('customers', (SELECT COUNT(*) FROM staging_customers), CURRENT_TIMESTAMP);

-- Проверка качества данных
-- 1. Проверка на наличие NULL в обязательных полях
SELECT COUNT(*) AS null_emails
FROM customers
WHERE email IS NULL;

-- 2. Проверка на наличие дубликатов
SELECT email, COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- 3. Проверка на наличие недействительных форматов
SELECT COUNT(*) AS invalid_emails
FROM customers
WHERE email NOT LIKE '%@%.%';

-- 4. Проверка ссылочной целостности
SELECT COUNT(*) AS orphaned_orders
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- 5. Проверка на наличие выбросов
SELECT COUNT(*) AS outlier_count
FROM orders
WHERE amount < 0 OR amount > 100000;

-- 6. Проверка актуальности данных
SELECT 
    MAX(updated_at) AS last_update,
    EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(updated_at))) / 3600 AS hours_since_update
FROM customers;

-- Создание панели мониторинга качества данных
CREATE VIEW data_quality_metrics AS
SELECT 
    'customers' AS table_name,
    COUNT(*) AS total_records,
    SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_emails,
    SUM(CASE WHEN email NOT LIKE '%@%.%' THEN 1 ELSE 0 END) AS invalid_emails,
    MAX(updated_at) AS last_updated
FROM customers;

-- Нормализовано (3NF)
-- Таблица заказов
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT
);

-- Требуются объединения для отчетности
SELECT 
    c.customer_name,
    p.product_name,
    o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

-- Денормализовано (для отчетности)
CREATE TABLE orders_denormalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),  -- Денормализовано
    customer_email VARCHAR(100),  -- Денормализовано
    product_id INT,
    product_name VARCHAR(100),  -- Денормализовано
    product_category VARCHAR(50),  -- Денормализовано
    quantity INT,
    unit_price DECIMAL(10,2)  -- Денормализовано
);

-- Более простой, более быстрый запрос
SELECT customer_name, product_name, quantity
FROM orders_denormalized;

-- Создание материализованного представления для панели мониторинга
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    DATE_TRUNC('day', order_date) AS date,
    product_category,
    region,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('day', order_date), product_category, region;

-- Создание индекса для более быстрой фильтрации
CREATE INDEX idx_daily_sales_date ON daily_sales_summary(date);

-- Обновление материализованного представления (запланированное задание)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- Запрос панели мониторинга (быстрый, использует предварительно агрегированные данные)
SELECT * FROM daily_sales_summary
WHERE date >= CURRENT_DATE - INTERVAL '30 days'
AND region = 'North America';