📘 Банк задач для собеседований по 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 a.*
FROM A a
LEFT JOIN B b
    ON a.key1 = b.key1
   AND a.key2 = b.key2
   AND a.key3 = b.key3
   AND a.key4 = b.key4
   AND a.key5 = b.key5
WHERE b.key1 IS NULL;

SELECT a.*
FROM A a
WHERE NOT EXISTS (
    SELECT 1
    FROM B b
    WHERE a.key1 IS NOT DISTINCT FROM b.key1
      AND a.key2 IS NOT DISTINCT FROM b.key2
      AND a.key3 IS NOT DISTINCT FROM b.key3
      AND a.key4 IS NOT DISTINCT FROM b.key4
      AND a.key5 IS NOT DISTINCT FROM b.key5
);

WITH ranked AS (
    SELECT
        a.*,
        ROW_NUMBER() OVER (
            PARTITION BY key1, key2, key3, key4, key5
            ORDER BY id
        ) AS rn
    FROM A a
)
SELECT *
FROM ranked
WHERE rn = 1;

WITH ranked AS (
    SELECT
        a.*,
        ROW_NUMBER() OVER (
            PARTITION BY key1, key2, key3, key4, key5
            ORDER BY id
        ) AS rn
    FROM A a
)
SELECT
    col1, col2, col3, col4, col5,
    col6, col7, col8, col9, col10,
    col11, col12, col13, col14, col15,
    col16, col17, col18, col19, col20
FROM ranked
WHERE rn = 1;

SELECT
    client_id,
    SUM(amount) AS total_amount
FROM transactions
WHERE transaction_date >= date_trunc('month', current_date) - INTERVAL '1 month'
  AND transaction_date < date_trunc('month', current_date)
GROUP BY client_id
ORDER BY total_amount DESC, client_id
LIMIT 5;

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;

WITH marked AS (
    SELECT
        product_id,
        price,
        effective_from,
        effective_to,
        version_id,
        CASE
            WHEN price = LAG(price) OVER (
                PARTITION BY product_id
                ORDER BY effective_from, version_id
            )
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM price_history
),
grouped AS (
    SELECT
        product_id,
        price,
        effective_from,
        effective_to,
        SUM(is_new_group) OVER (
            PARTITION BY product_id
            ORDER BY effective_from, version_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM marked
)
SELECT
    product_id,
    price,
    MIN(effective_from) AS effective_from,
    MAX(effective_to) AS effective_to
FROM grouped
GROUP BY product_id, price, group_id
ORDER BY product_id, effective_from;

SELECT
    name,
    SUM(CASE WHEN parameter = 'A' THEN amount ELSE 0 END) AS a,
    SUM(CASE WHEN parameter = 'B' THEN amount ELSE 0 END) AS b
FROM t
GROUP BY name
ORDER BY name;

SELECT
    name,
    SUM(amount) FILTER (WHERE parameter = 'A') AS a,
    SUM(amount) FILTER (WHERE parameter = 'B') AS b
FROM t
GROUP BY name
ORDER BY name;

WITH user_classes AS (
    SELECT
        user_id,
        CASE
            WHEN SUM(CASE WHEN class = 'B' THEN 1 ELSE 0 END) > 0 THEN 'B'
            ELSE 'A'
        END AS final_class
    FROM user_class
    GROUP BY user_id
)
SELECT
    final_class AS class,
    COUNT(*) AS users_count
FROM user_classes
GROUP BY final_class
ORDER BY final_class;

WITH user_classes AS (
    SELECT
        user_id,
        CASE WHEN BOOL_OR(class = 'B') THEN 'B' ELSE 'A' END AS final_class
    FROM user_class
    GROUP BY user_id
)
SELECT
    final_class AS class,
    COUNT(*) AS users_count
FROM user_classes
GROUP BY final_class
ORDER BY final_class;

WITH first_activity AS (
    SELECT
        user_id,
        toStartOfWeek(min(event_date), 1) AS cohort_week
    FROM events
    GROUP BY user_id
),
user_weeks AS (
    SELECT DISTINCT
        user_id,
        toStartOfWeek(event_date, 1) AS activity_week
    FROM events
)
SELECT
    fa.cohort_week,
    dateDiff('week', fa.cohort_week, uw.activity_week) AS week_number,
    countDistinct(uw.user_id) AS users
FROM first_activity fa
JOIN user_weeks uw
    ON uw.user_id = fa.user_id
WHERE uw.activity_week >= fa.cohort_week
GROUP BY
    fa.cohort_week,
    week_number
ORDER BY
    fa.cohort_week,
    week_number;

WITH first_activity AS (
    SELECT
        user_id,
        toStartOfWeek(min(event_date), 1) AS cohort_week
    FROM events
    GROUP BY user_id
),
user_weeks AS (
    SELECT DISTINCT
        user_id,
        toStartOfWeek(event_date, 1) AS activity_week
    FROM events
),
retention AS (
    SELECT
        fa.cohort_week,
        dateDiff('week', fa.cohort_week, uw.activity_week) AS week_number,
        countDistinct(uw.user_id) AS users
    FROM first_activity fa
    JOIN user_weeks uw
        ON uw.user_id = fa.user_id
    WHERE uw.activity_week >= fa.cohort_week
    GROUP BY
        fa.cohort_week,
        week_number
),
cohort_sizes AS (
    SELECT
        cohort_week,
        users AS cohort_users
    FROM retention
    WHERE week_number = 0
)
SELECT
    r.cohort_week,
    r.week_number,
    r.users,
    round(r.users / cs.cohort_users * 100, 2) AS retention_percent
FROM retention r
JOIN cohort_sizes cs
    ON cs.cohort_week = r.cohort_week
ORDER BY
    r.cohort_week,
    r.week_number;

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;