Задача из собеседования Яндекса
В реляционной базе данных существуют таблицы:

Cities - список городов:

id - первичный ключ
name - название
population - численность населения
founded - год основания
country_id - id страны

Countries - список стран

id - первичный ключ
name - название
population - численность населения
gdp - валовый продукт в долларах

Companies - компании

id - первичный ключ
name - название
city_id - город в котором находится штаб-квартира
revenue - годовая выручка в долларах
labors - численность сотрудников

Составьте запрос, который:

Для всех стран в базе данных посчитать количество компаний
со штаб квартирами в этой стране численность сотрудников в которых больше 1000 человек.

В результате должны быть только количество компаний
и названия стран с населением более 1 миллиона человек и валовым продуктом более 10 миллиардов долларов,
у которых суммарная выручка выбранных компаний составляет более 1 миллиарда долларов.

Создадим таблицы и заполним их тестовыми данными
SQL

CREATE TABLE Countries (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    population NUMBER NOT NULL,
    gdp NUMBER(15, 2) NOT NULL
);

CREATE TABLE Cities (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    population NUMBER NOT NULL,
    founded NUMBER NOT NULL,
    country_id NUMBER NOT NULL,
    CONSTRAINT fk_cities_country FOREIGN KEY (country_id) REFERENCES Countries(id)
);

CREATE TABLE Companies (
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    name VARCHAR2(150) NOT NULL,
    city_id NUMBER NOT NULL,
    revenue NUMBER(15, 2) NOT NULL,
    labors NUMBER NOT NULL,
    CONSTRAINT fk_companies_city FOREIGN KEY (city_id) REFERENCES Cities(id)
);

INSERT INTO Countries (id, name, population, gdp) VALUES
(1, 'Tanzania', 108000000, 1500000000000.00);
INSERT INTO Countries (id, name, population, gdp) VALUES
(2, 'Hungary', 117000000, 128900000000.00);
INSERT INTO Countries (id, name, population, gdp) VALUES
(3, 'Senegal', 142000000, 997600000000.00);
INSERT INTO Countries (id, name, population, gdp) VALUES
(4, 'Tuvalu', 86000000, 1900000000000.00);
INSERT INTO Countries (id, name, population, gdp) VALUES
(5, 'Italy', 101000000, 1180000000000.00);


INSERT INTO Cities (id, name, population, founded, country_id) VALUES
(1, 'West Tammyfort', 8500000, 1085, 2);
INSERT INTO Cities (id, name, population, founded, country_id) VALUES
(2, 'Hullport', 2400000, 994, 3);
INSERT INTO Cities (id, name, population, founded, country_id) VALUES
(3, 'Howardborough', 13500000, 1313, 5);
INSERT INTO Cities (id, name, population, founded, country_id) VALUES
(4, 'West Donald', 11900000, 1100, 5);
INSERT INTO Cities (id, name, population, founded, country_id) VALUES
(5, 'New Laurenside', 1700000, 951, 3);

INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(1, 'Roberts, Davis and Yates', 3, 3330000000.00, 35458);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(2, 'Johnston-Roberts', 4, 9630000000.00, 39580);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(3, 'Underwood Inc', 3, 5880000000.00, 29212);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(4, 'Zavala, Green and Jones', 3, 5960000000.00, 25274);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(5, 'Todd, Mcdowell and Mclean', 2, 5760000000.00, 19077);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(6, 'MicroTech Solutions', 1, 78000.00, 5);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(7, 'EcoFarm Co', 2, 252000.00, 12);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(8, 'SmartByte', 3, 975000.00, 23);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(9, 'MiniBuild', 4, 1100000.00, 35);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(10, 'BudgetSoft Ltd.', 5, 890000.00, 18);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(11, 'GreenCraft', 1, 125000.00, 9);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(12, 'NanoWare Inc', 2, 453000.00, 14);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(13, 'CityBakery', 3, 61000.00, 7);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(14, 'CoffeeExpress', 4, 152000.00, 11);
INSERT INTO Companies (id, name, city_id, revenue, labors) VALUES
(15, 'QuickFix Tech', 5, 330000.00, 15);

Мое решение задачи.
SELECT c.country_name, c.company_amount
FROM (SELECT cou.name AS country_name,
COUNT (co.id) OVER (PARTITION BY cou.id) AS company_amount,
SUM (cou.gdp) OVER (PARTITION BY cou.id) AS country_gdp,
SUM (co.revenue) OVER (PARTITION BY co.id) AS country_revenue
FROM companies co
INNER JOIN CITIES ci ON (ci.id = co.city_id)
INNER JOIN Countries cou ON (cou.id = co.country_id)
WHERE co.labors > POWER (10, 3) AND co.population > POWER (10, 6)) с
WHERE c.company_amount >= POWER (10, 9) AND c.country_gdp >= POWER (10, 10);

Наткнулся в Книге Том Кайта "ORACLE для профессионалов" на то, что автор использует вьюхи v$mystat, v$statname для анализа запросов.
Написал свой вариант, который можно запускать в любом IDE.
Получилась мини трассировка.
SQL

DECLARE
  TYPE trStats IS RECORD
  (
    consistentGets    INT,
    physicalReads     INT,
    physicalWrites    INT,
    redoSize          INT,
    recursiveCalls    INT,
    pgaMemoryMax      INT,
    openedCursors     INT
  );

  old_stats_data   trStats;
  new_stats_data   trStats;
  row_count        INTEGER;
  docId            NUMBER := 453;

  PROCEDURE get_current_stats (stats_data OUT trStats) IS
    stats   trStats;
  BEGIN
    SELECT s.cr,
           s.pr,
           s.pw,
           s.redo_size,
           s.recursive_calls,
           s.pga_max_memory,
           s.opened_cursors
    INTO   stats.consistentGets,
           stats.physicalReads,
           stats.physicalWrites,
           stats.redoSize,
           stats.recursiveCalls,
           stats.pgaMemoryMax,
           stats.openedCursors
    FROM   (SELECT sn.name, ms.VALUE AS stat_value
            FROM   v$mystat ms, v$statname sn
            WHERE  ms.statistic# = sn.statistic#)
           PIVOT (MAX (stat_value)
                 FOR name
                 IN ('consistent gets' AS cr,
                    'physical reads' AS pr,
                    'physical writes' AS pw,
                    'recursive calls' AS recursive_calls,
                    'redo size' AS redo_size,
                    'session pga memory max' AS pga_max_memory,
                    'opened cursors current' AS opened_cursors)) s;

    stats_data   := stats;
  END get_current_stats;

  PROCEDURE stats_diff2str (old_stats_data IN trStats, new_stats_data IN trStats) IS
    stats_diff_str   VARCHAR2 (4000);
  BEGIN
    stats_diff_str   :=
      CASE
        WHEN old_stats_data.consistentGets - new_stats_data.consistentGets IS NOT NULL THEN
          SUBSTR (
               'consistent gets: '
            || TO_CHAR (old_stats_data.consistentGets - new_stats_data.consistentGets)
            || ', physical reads: '
            || TO_CHAR (old_stats_data.physicalReads - new_stats_data.physicalReads)
            || ', physical writes: '
            || TO_CHAR (old_stats_data.physicalWrites - new_stats_data.physicalWrites)
            || ', recursive calls: '
            || TO_CHAR (old_stats_data.redoSize - new_stats_data.redoSize)
            || ', redo size: '
            || TO_CHAR (old_stats_data.recursiveCalls - new_stats_data.recursiveCalls)
            || ', session pga memory max: '
            || TO_CHAR (old_stats_data.pgaMemoryMax - new_stats_data.pgaMemoryMax)
            || ', opened cursors current: '
            || TO_CHAR (old_stats_data.openedCursors),
            1,
            200)
      END;
    DBMS_OUTPUT.put_line (stats_diff_str);
  END stats_diff2str;
BEGIN
  get_current_stats (stats_data => old_stats_data);

  SELECT 1 INTO row_count FROM DUAL;

  get_current_stats (stats_data => new_stats_data);
  stats_diff2str (old_stats_data => old_stats_data, new_stats_data => new_stats_data);
END;

Задача от начальника разработки DB большого знатока Postgres - Ивана Петровича Могилы.
"Она такая, из практики, поэтому не очень красивая. Я нашим бдшникам её давал. Но она очень завязана на особенности postgresql"
Опишите логику, что делает эта функция

CREATE OR REPLACE FUNCTION dba_tools.pgcompact_clean_pages(_table_ident text, _column_ident text, _to_page integer, _page_offset integer, _max_tupples_per_page integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  _from_page integer := _to_page - _page_offset + 1;
  _min_ctid tid;
  _max_ctid tid;
  _ctid_list tid[];
  _next_ctid_list tid[];
  _ctid tid;
  _loop integer;
  _result_page integer;
  _update_query text :=
    'UPDATE ONLY ' || _table_ident ||
    ' SET ' || _column_ident || ' = ' || _column_ident ||
    ' WHERE ctid = ANY($1) RETURNING ctid';
BEGIN
  -- Check page argument values
  IF NOT (
    _page_offset IS NOT NULL AND _page_offset >= 1 AND
    _to_page IS NOT NULL AND _to_page >= 1 AND
    _to_page >= _page_offset)
    THEN
    RAISE EXCEPTION 'Wrong page arguments specified.';
  END IF;

  -- Check that session_replication_role is set to replica to
  -- prevent triggers firing
  IF NOT (
    SELECT setting = 'replica'
    FROM pg_catalog.pg_settings
    WHERE name = 'session_replication_role')
    THEN
    RAISE EXCEPTION 'The session_replication_role must be set to replica.';
  END IF;

  -- Define minimal and maximal ctid values of the range
  _min_ctid := (_from_page, 1)::text::tid;
  _max_ctid := (_to_page, _max_tupples_per_page)::text::tid;

  -- Build a list of possible ctid values of the range
  SELECT array_agg((pi, ti)::text::tid)
    INTO _ctid_list
  FROM generate_series(_from_page, _to_page) AS pi
    CROSS JOIN generate_series(1, _max_tupples_per_page) AS ti;

  <<_outer_loop>>
    FOR _loop IN 1.._max_tupples_per_page LOOP
    _next_ctid_list := array[]::tid[];

    -- Update all the tuples in the range
    FOR _ctid IN EXECUTE _update_query USING _ctid_list
    LOOP
      IF _ctid > _max_ctid THEN
        _result_page := -1;
        EXIT _outer_loop;
      ELSIF _ctid >= _min_ctid THEN
        -- The tuple is still in the range, more updates are needed
        _next_ctid_list := _next_ctid_list || _ctid;
      END IF;
    END LOOP;

    _ctid_list := _next_ctid_list;

    -- Finish processing if there are no tupples in the range left
    IF coalesce(array_length(_ctid_list, 1), 0) = 0 THEN
      _result_page := _from_page - 1;
      EXIT _outer_loop;
    END IF;
  END LOOP;

  -- No result
  IF _loop = _max_tupples_per_page AND _result_page IS NULL THEN
    RAISE EXCEPTION
      'Maximal loops count has been reached with no result.';
  END IF;

  RETURN _result_page;
END$_$;

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#PostgreSQL#PL/pgSQL
💼 #RealInterviewTasks

Логика функции следующая:
1. Идем по записям определенного диапазона страниц таблицы (далее Х),
для этого нам нужен только ctid, он доступен всем кто имеет права на чтение таблицы
2. Для каждой записи делаем update, создается новая версия строки,
эта версия может быть как в том же диапазоне Х, так и выше или ниже
- Если новая версия строки оказалась в странице выше диапазона Х,
то выходим, поэтому диапазон лучше указывать из хвоста таблицы, чтобы такая ситуация была невозможна
- Если новая версия строки оказалась в том же диапазоне Х, то делаем update снова
- Если новая версия строки оказалась в странице ниже диапазона Х,
то идем дальше, старую версию потом удалит vacuum

Таким образом с помощью update мы пытаемся согнать все строки из указанного диапазона страниц в страницы ниже,
а этот диапазон освободить. Если он будет в хвосте таблицы, то vacuum сможет его освободить полностью.

У vacuum в postgres есть особенность, что он освобождает место занятое таблицей только с конца
т.е. полностью пустые странице в хвосте могут быть освобождены,
но пустые страницы в середине не могут, если за ними есть страницы с живыми строками.
Ну и ещё одна особенность.

UPDATE ONLY  t_table_ident SET
  id = id
WHERE ctid = ANY($1)
RETURNING ctid;

Тут returning вернет ctid новой версии строки, так что значение будет отличаться от того, что передано в параметре.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💼 #RealInterviewTasks

Простая, но интересная задача на понимание.
(Мне задавали на собесе в 2022 году)

Необходимо написать запрос, который позволит понять, идентичны ли данные в двух таблицах.
Порядок хранения данных в таблицах значения не имеет.

create table t1(a number, b number);
 
create table t2(a number, b number);

Пример данных:
T1:
a b
1 1
2 2
2 2
3 3
4 4
T2:
a b
1 1
2 2
3 3
3 3
4 4

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💼 #RealInterviewTasks #SQL

Решение задачи вариант 1:
На собесе я решил через FULL OUTER JOIN
На текущий момент добавил все красивости и мое решение выглядело бы так:

SELECT CASE
         WHEN EXISTS
                (SELECT NULL
                 FROM   (SELECT   STANDARD_HASH (TO_CHAR (t1.a) || '/' || TO_CHAR (t1.b), 'MD5') AS hash_row, COUNT (*) AS row_amount
                         FROM     t1
                         GROUP BY STANDARD_HASH (TO_CHAR (t1.a) || '/' || TO_CHAR (t1.b), 'MD5')) t11
                        FULL OUTER JOIN
                        (SELECT   STANDARD_HASH (TO_CHAR (t2.a) || '/' || TO_CHAR (t2.b), 'MD5') AS hash_row, COUNT (*) AS row_amount
                         FROM     t2
                         GROUP BY STANDARD_HASH (TO_CHAR (t2.a) || '/' || TO_CHAR (t2.b), 'MD5')) t21
                          ON (t11.hash_row = t21.hash_row AND t11.row_amount = t21.row_amount)
                 WHERE  t11.hash_row IS NULL OR t21.hash_row IS NULL) THEN
           'DIFFERENT'
         ELSE
           'IDENTICAL'
       END AS comparison_result
FROM   DUAL;

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💼 #RealInterviewTasks #SQL

Кстати, чат GPT предложил решение через MINUS.

SELECT CASE
         WHEN
           (
             SELECT COUNT(*) FROM (
               SELECT a, b, COUNT(*) cnt FROM t1 GROUP BY a, b
               MINUS
               SELECT a, b, COUNT(*) cnt FROM t2 GROUP BY a, b
             )
           ) = 0
           AND
           (
             SELECT COUNT(*) FROM (
               SELECT a, b, COUNT(*) cnt FROM t2 GROUP BY a, b
               MINUS
               SELECT a, b, COUNT(*) cnt FROM t1 GROUP BY a, b
             )
           ) = 0
         THEN 'IDENTICAL'
         ELSE 'DIFFERENT'
       END AS comparison_result
FROM dual;

Я слегка его переделал и
и это решение мне тоже нравится
Вариант 2

SELECT CASE
         WHEN
         EXISTS
           (
               SELECT   STANDARD_HASH (TO_CHAR (t1.a) || '/' || TO_CHAR (t1.b), 'MD5') AS hash_row, COUNT (*) AS row_amount
                         FROM     t1
               GROUP BY STANDARD_HASH (TO_CHAR (t1.a) || '/' || TO_CHAR (t1.b), 'MD5')
               MINUS
               SELECT   STANDARD_HASH (TO_CHAR (t2.a) || '/' || TO_CHAR (t2.b), 'MD5') AS hash_row, COUNT (*) AS row_amount
                         FROM     t2
               GROUP BY STANDARD_HASH (TO_CHAR (t2.a) || '/' || TO_CHAR (t2.b), 'MD5')
           ) 
           OR
           EXISTS
           (
               SELECT   STANDARD_HASH (TO_CHAR (t2.a) || '/' || TO_CHAR (t2.b), 'MD5') AS hash_row, COUNT (*) AS row_amount
                         FROM     t2
               GROUP BY STANDARD_HASH (TO_CHAR (t2.a) || '/' || TO_CHAR (t2.b), 'MD5')
               MINUS
               SELECT   STANDARD_HASH (TO_CHAR (t1.a) || '/' || TO_CHAR (t1.b), 'MD5') AS hash_row, COUNT (*) AS row_amount
                         FROM     t1
               GROUP BY STANDARD_HASH (TO_CHAR (t1.a) || '/' || TO_CHAR (t1.b), 'MD5')
           )
         THEN 'DIFFERENT'
         ELSE 'IDENTICAL'
       END AS comparison_result
FROM dual;

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

💼 #RealInterviewTasks #SQL

🔎 Сложная задача с собеса в компанию UNLIMIT
(Я её не решил в моменте т.е. как не решил, я предложил вариант, но он ресурсный и потому неподходящий.)
Задача на уровень Senior, а может быть Expert.

💡 Условие:

У нас — высоконагруженная база ORACLE:
📈 до 1000 и выше транзакций в секунду.
Часть из них — commit, часть — rollback.

Нужно:
📆 Назначать каждой транзакции уникальный порядковый номер в пределах суток (часа, минуты)
При этом:

🔁 Номера должны идти без пропусков и дублей.

📌 Сохраняем их в таблице transactions.

⛔ Нельзя использовать блокировки, которые мешают другим транзакциям.

⚡ Назначение номера должно происходить быстро.

📊 Пример:

Дата Номер транзакции
01.01.2025 1
01.01.2025 2
... ...
01.01.2025 1000009
02.01.2025 1
02.01.2025 2
... ...

🤔 Как бы вы реализовали такой счётчик?
С условиями:

Без блокировок

Без пробелов

Быстро

И чтобы не сносило систему под нагрузкой

Пишите ваши идеи 👇

🔄 UPDATE 02.02.2025:

Спасибо, что с нами есть Иван Петрович.

📌 В PostgreSQL такое провернуть невозможно.
Дело в том, что PostgreSQL при попытке вставки уникального значения блокирует строку (если используется, например, SELECT ... FOR UPDATE, INSERT ... ON CONFLICT, уникальные индексы и т. д.).
Сессии, которые вставляют те же значения одновременно, вынуждены ждать — из-за row-level locks.

🔄 UPDATE 23.09.2025:

Спасибо, что с нами есть Евгений Артурович.

💡 В Oracle поведение аналогичное - при вставке двух новых значений в уникальный индекс ошибка проявляется в момент коммита. Поэтому для избежание конкуренции назначения номера должно быть в автономной транзакции.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #Oracle #PL/SQL

💡 Разбор «невозможной» задачи про сквозной счётчик-без-пробелов

🔑 Главные наблюдения
INSERT — самая быстрая DML-операция; читаем как можно меньше.

FAST FULL INDEX SCAN даёт O(1) чтение «хвоста» уникального индекса.

Сессии изолированы, но автономная транзакция и уникальный индекс гарантирует, что два одинаковых значения не вставятся одновременно.

🛠 Алгоритм
Шаг Действие Почему это безопасно
1 Читаем последний номер через SELECT … ORDER BY num DESC FETCH 1
(FAST FULL INDEX SCAN) Один блочный I/O, без блокировок
2 INSERT INTO period_tx_idx(period, num) VALUES(:p, :last_num) Дешевле, чем update
3 Если вставка успешна → номер наш ✅ Уникальный индекс не дал коллизии
4 Если ORA-00001 / unique constraint → кто-то уже взял этот num → num := num + 1 и GO TO 2 Перебираем до победы — обычно 1-2 попытки

🔸 На практике при 1 000 TPS конкурируют считанные сессии → конфликт мизерный.
🔸 «Дырки» возможны только при аварийном выключении питания в момент вставки, и то чисто теоретически.

❗️ Практический совет
Такой счетчик надо организовывать после всей логики и всех проверок, все констрейны на уровне таблицы тоже должны быть дополнительно проверены, чтобы избежать неожиданного EXCEPTION.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

#RealInterviewTasks #Oracle #PL/SQL

Скрипт для проверки. Можно запустить в разных сессиях и посмотреть.
UPDATE 23.09.2025 Указанный скрипт неточный.
Исправленный скрипт в комментариях.