📘 Банк задач для собеседований по SQL

WITH ranked AS (
    SELECT
        sku_id_competitor,
        competitor_id,
        promo_price_competitor,
        regular_price_competitor,
        parsing_date,
        COALESCE(promo_price_competitor, regular_price_competitor) AS price,
        ROW_NUMBER() OVER (
            PARTITION BY sku_id_competitor, competitor_id, parsing_date
            ORDER BY CASE WHEN promo_price_competitor IS NOT NULL THEN 1 ELSE 0 END DESC
        ) AS rn
    FROM public.competitors_prices
)
SELECT
    sku_id_competitor,
    competitor_id,
    price,
    parsing_date
FROM ranked
WHERE rn = 1;

WITH movements AS (
    SELECT
        dttm::date AS dt,
        dttm,
        product,
        SUM(amt) OVER (
            PARTITION BY product
            ORDER BY dttm
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS movement_total,
        ROW_NUMBER() OVER (
            PARTITION BY product, dttm::date
            ORDER BY dttm DESC
        ) AS rn
    FROM stock_movements
)
SELECT
    m.dt,
    m.product,
    b.start_balance + m.movement_total AS stock_left
FROM movements AS m
JOIN stock_balance AS b
    ON b.product = m.product
WHERE m.rn = 1
ORDER BY dt, product;

SELECT
    m1.user_id AS user1,
    m2.user_id AS user2,
    COUNT(DISTINCT m1.room_id) AS shared_meetings
FROM meetings m1
JOIN meetings m2
    ON m1.room_id = m2.room_id
   AND m1.user_id < m2.user_id
GROUP BY m1.user_id, m2.user_id
HAVING COUNT(DISTINCT m1.room_id) > 1

SELECT
    managerId,
    COUNT(*) AS employee_count
FROM Employee
WHERE managerId IS NOT NULL
GROUP BY managerId
HAVING COUNT(*) > 5;

WITH courier_speed AS (
    SELECT
        courier_id,
        AVG(distance / travel_time) AS avg_speed
    FROM orders
    WHERE EXTRACT(YEAR FROM date) = 2021
      AND EXTRACT(MONTH FROM date) = 6
    GROUP BY courier_id
),
overall_speed AS (
    SELECT
        AVG(avg_speed) AS avg_speed_all
    FROM courier_speed
)
SELECT
    cs.courier_id,
    cs.avg_speed
FROM courier_speed cs
CROSS JOIN overall_speed os
WHERE cs.avg_speed > os.avg_speed_all
ORDER BY cs.courier_id;

WITH ranked AS (
    SELECT
        name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) AS avg_salary_per_department,
        MAX(salary) OVER (PARTITION BY department) AS max_salary_per_department,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, id
        ) AS rn
    FROM employees
    WHERE fired_date IS NULL
)
SELECT
    name,
    department,
    salary,
    avg_salary_per_department,
    salary - max_salary_per_department AS salary_max_diff
FROM ranked
WHERE rn <= 3
ORDER BY department, salary DESC, name;

SELECT
    name,
    SUM(CASE WHEN mark = 2 THEN 1 ELSE 0 END) AS cnt_twos
FROM student_marks
GROUP BY name
HAVING SUM(CASE WHEN mark = 5 THEN 1 ELSE 0 END) > 10;

SELECT
    e.name
FROM employees e
JOIN employees m
  ON e.id_master = m.employee_id
WHERE e.salary > m.salary;

WITH RECURSIVE hierarchy AS (
    SELECT
        id,
        name,
        manager_id,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    SELECT
        e.id,
        e.name,
        e.manager_id,
        h.level + 1 AS level
    FROM employees e
    JOIN hierarchy h
      ON e.manager_id = h.id
)
SELECT
    name,
    level
FROM hierarchy
ORDER BY level, name;

SELECT 
    name, 
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT 
    name,
    salary,
    department_id,
    RANK() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

WITH ranked AS (
    SELECT
        ls.dt,
        ls.table_name,
        ls.status,
        ls.dt::date AS day,
        ROW_NUMBER() OVER (
            PARTITION BY ls.table_name, ls.dt::date
            ORDER BY ls.dt DESC
        ) AS rn
    FROM public.load_status AS ls
    WHERE ls.dt >= current_date - INTERVAL '14 days'
)
SELECT
    dt,
    table_name,
    status,
    day
FROM ranked
WHERE rn = 1
ORDER BY dt DESC;

CREATE OR REPLACE FUNCTION get_department_salary_summary(dept_id INT)
RETURNS TABLE (
    employees_count INT,
    avg_salary NUMERIC
)
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*) AS employees_count,
        AVG(salary) AS avg_salary
    FROM employees
    WHERE department_id = dept_id;
END;
$$ LANGUAGE plpgsql;

  SELECT
      o.supplier_id,
      SUM(o.price * o.quantity) AS total_order_amount
  FROM orders o
  JOIN suppliers s
      ON o.supplier_id = s.supplier_id
  WHERE s.supplier_type <> 'WAREHOUSE'
    OR s.supplier_type IS NULL
  GROUP BY o.supplier_id;

  SELECT DISTINCT r1.user_id
  FROM ride r1
  JOIN ride r2
    ON r1.user_id = r2.user_id
  AND r2.dt = r1.dt + INTERVAL '1 day';

-- Индекс для быстрого отбора заказов по дате
CREATE INDEX idx_orders_created_at_id
    ON orders (created_at, id);
 
-- Индекс для быстрого поиска позиций заказа по ключу соединения
CREATE INDEX idx_order_items_order_id_inc
    ON order_items (order_id)
    INCLUDE (price, quantity);

CREATE INDEX idx_orders_id
    ON orders (id);
 
CREATE INDEX idx_order_items_order_id
    ON order_items (order_id);

Но без индекса по `created_at` таблицу `orders` всё равно может быть дорого фильтровать, поэтому в реальной задаче лучше оставить индекс `(created_at, id)`.

SELECT
    SUM(ua_costs_usd)::numeric / NULLIF(SUM(num_of_installs), 0) AS cpi
FROM user_installs
WHERE date_ BETWEEN DATE '2025-01-01' AND DATE '2025-01-07';

WITH calendar AS (
    SELECT generate_series(
        DATE '2025-01-01',
        DATE '2025-12-31',
        INTERVAL '1 day'
    )::date AS date_
),
installs_by_day AS (
    SELECT
        c.date_,
        COALESCE(ui.num_of_installs, 0) AS num_of_installs,
        COALESCE(ui.ua_costs_usd, 0) AS ua_costs_usd
    FROM calendar c
    LEFT JOIN user_installs ui
        ON ui.date_ = c.date_
)
SELECT
    date_,
    SUM(ua_costs_usd) OVER (
        ORDER BY date_
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )::numeric
    /
    NULLIF(
        SUM(num_of_installs) OVER (
            ORDER BY date_
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        0
    ) AS cpi_7d
FROM installs_by_day
ORDER BY date_;

SELECT
    c.customer_id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_dttm >= NOW() - INTERVAL '90 days'
      AND o.order_dttm < NOW()
);

WITH ranked AS (
    SELECT
        ro.*,
        ROW_NUMBER() OVER (
            PARTITION BY ro.order_id
            ORDER BY
                ro.updated_at DESC,
                CASE WHEN ro.source_system = 'core' THEN 0 ELSE 1 END,
                ro.order_dttm DESC
        ) AS rn
    FROM raw_orders ro
)
SELECT
    order_id,
    user_id,
    order_dttm,
    amount,
    status,
    updated_at,
    source_system
FROM ranked
WHERE rn = 1;

CREATE TEMP TABLE t_fact_events
DISTRIBUTED BY (user_id)
AS
SELECT
    user_id,
    event_id,
    event_dttm::date AS dt,
    amount
FROM fact_events
WHERE event_dttm >= TIMESTAMP '2026-01-01 00:00:00'
  AND event_dttm < TIMESTAMP '2026-02-01 00:00:00';
 
CREATE TEMP TABLE t_dim_users
DISTRIBUTED BY (user_id)
AS
SELECT
    user_id,
    country
FROM dim_users
WHERE is_active = true;
 
SELECT
    f.dt,
    u.country,
    COUNT(*) AS events_cnt,
    SUM(f.amount) AS total_amount
FROM t_fact_events f
JOIN t_dim_users u
    ON f.user_id = u.user_id
GROUP BY f.dt, u.country;

WITH last_purchase AS (
    SELECT
        customer_id,
        MAX(created_at) AS last_purchase_at
    FROM cheque
    GROUP BY customer_id
),
weekly_avg AS (
    SELECT
        c.customer_id,
        AVG(c."sum") AS avg_sum,
        lp.last_purchase_at
    FROM cheque c
    JOIN last_purchase lp
        ON lp.customer_id = c.customer_id
    WHERE c.created_at >= lp.last_purchase_at - INTERVAL '7 days'
      AND c.created_at <= lp.last_purchase_at
    GROUP BY c.customer_id, lp.last_purchase_at
)
SELECT
    customer_id,
    avg_sum,
    last_purchase_at::date AS last_purchase_date
FROM weekly_avg
ORDER BY customer_id;