Интересная задача из собеседования в ЦБ

Мой вариант решения

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

CREATE TABLE T1 (
  ID     NUMBER,
  OGRN   VARCHAR2(15),
  INN    VARCHAR2(12),
  NAME   VARCHAR2(200),
  DS     DATE,  -- Дата начала действия записи
  DE     DATE   -- Дата окончания действия записи
);

CREATE TABLE T2 (
  ID      NUMBER,
  STATUS  VARCHAR2(50),
  ADDRESS VARCHAR2(500),
  EQ      NUMBER(15,2), -- Уставный капитал
  DS      DATE,
  DE      DATE 
);

INSERT INTO T1 (ID, OGRN, INN, NAME, DS, DE) VALUES
(125, '1127847448520', '7810880684', 'ООО "ЛЕН-РЕЗЕРВ"',      TO_DATE('01.01.2017', 'DD.MM.YYYY'), TO_DATE('30.06.2018', 'DD.MM.YYYY'));

INSERT INTO T1 (ID, OGRN, INN, NAME, DS, DE) VALUES
(125, '1127847448520', '7810880684', 'ООО "ЛЕНТЕХ-РЕЗЕРВ"',   TO_DATE('01.07.2018', 'DD.MM.YYYY'), TO_DATE('31.12.2019', 'DD.MM.YYYY'));

INSERT INTO T1 (ID, OGRN, INN, NAME, DS, DE) VALUES
(125, '1127847448520', '7810880684', 'ООО "ЛЕН-РЕЗЕРВ"',      TO_DATE('01.01.2020', 'DD.MM.YYYY'), TO_DATE('31.12.9999', 'DD.MM.YYYY'));

INSERT INTO T1 (ID, OGRN, INN, NAME, DS, DE) VALUES
(124, '1127847448521', '7810880683', 'ООО "ЛЕН-РЕЗЕРВ"',      TO_DATE('01.01.2016', 'DD.MM.YYYY'), TO_DATE('30.06.2017', 'DD.MM.YYYY'));

INSERT INTO T1 (ID, OGRN, INN, NAME, DS, DE) VALUES
(124, '1127847448521', '7810880683', 'ООО "ЛЕНТЕХ-РЕЗЕРВ"',   TO_DATE('01.07.2017', 'DD.MM.YYYY'), TO_DATE('31.12.2018', 'DD.MM.YYYY'));

INSERT INTO T1 (ID, OGRN, INN, NAME, DS, DE) VALUES
(124, '1127847448521', '7810880683', 'ООО "ЛЕН-РЕЗЕРВ"',      TO_DATE('01.01.2019', 'DD.MM.YYYY'), TO_DATE('31.12.9999', 'DD.MM.YYYY'));

INSERT INTO T2 (ID, STATUS, ADDRESS, EQ, DS, DE) VALUES
(125, '001', 'Санкт-Петербуг, Шуваловский пр 22',     10000, TO_DATE('05.01.2017', 'DD.MM.YYYY'), TO_DATE('10.09.2018', 'DD.MM.YYYY'));

INSERT INTO T2 (ID, STATUS, ADDRESS, EQ, DS, DE) VALUES
(125, '101', 'Санкт-Петербуг, пр Просвещения 130',     10000, TO_DATE('11.09.2018', 'DD.MM.YYYY'), TO_DATE('20.04.2019', 'DD.MM.YYYY'));

INSERT INTO T2 (ID, STATUS, ADDRESS, EQ, DS, DE) VALUES
(125, '001', 'Санкт-Петербуг, Московский пр. 222',     10000, TO_DATE('21.04.2019', 'DD.MM.YYYY'), TO_DATE('31.12.9999', 'DD.MM.YYYY'));


INSERT INTO T2 (ID, STATUS, ADDRESS, EQ, DS, DE) VALUES
(124, '002', 'Санкт-Петербуг, Шуваловский пр 21',     10001, TO_DATE('05.01.2016', 'DD.MM.YYYY'), TO_DATE('10.09.2017', 'DD.MM.YYYY'));

INSERT INTO T2 (ID, STATUS, ADDRESS, EQ, DS, DE) VALUES
(124, '102', 'Санкт-Петербуг, пр Просвещения 131',     10001, TO_DATE('11.09.2017', 'DD.MM.YYYY'), TO_DATE('20.04.2018', 'DD.MM.YYYY'));

INSERT INTO T2 (ID, STATUS, ADDRESS, EQ, DS, DE) VALUES
(124, '002', 'Санкт-Петербуг, Московский пр. 223',     10001, TO_DATE('21.04.2018', 'DD.MM.YYYY'), TO_DATE('31.12.9999', 'DD.MM.YYYY'));

Решение задачи а)
SQL

WITH
  periods AS
    (SELECT   t.id,
              t.reg_date AS start_period,
              COALESCE (LEAD (t.reg_date) OVER (PARTITION BY t.id ORDER BY t.reg_date) - INTERVAL '1' DAY, TO_DATE ('31.12.9999', 'DD.MM.YYYY')) AS end_period
     FROM     (SELECT t1.id, t1.ds AS reg_date FROM t1
               UNION
               SELECT t2.id, t2.ds AS reg_date FROM t2) t
     ORDER BY t.id, t.reg_date)
select p.id, t1.ogrn, t1.inn, t1.name, t1.name, t2.status, t2.address, t2.eq, p.start_period as ds, p.end_period AS de
FROM   periods p
  LEFT JOIN t1 ON (p.id = t1.id AND p.start_period BETWEEN t1.ds AND t1.de AND p.end_period BETWEEN t1.ds AND t1.de)
  LEFT JOIN t2 ON (p.id = t2.id AND p.start_period BETWEEN t2.ds AND t2.de AND p.end_period BETWEEN t2.ds AND t2.de)
ORDER BY p.id, p.start_period

Решение задачи б)
SQL

WITH
  periods AS
    (SELECT   t.id,
              t.reg_date AS start_period,
              COALESCE (LEAD (t.reg_date) OVER (PARTITION BY t.id ORDER BY t.reg_date) - INTERVAL '1' DAY,
                        TO_DATE ('31.12.9999', 'DD.MM.YYYY')) AS end_period
     FROM     (SELECT t1.id, t1.ds AS reg_date FROM t1
               UNION
               SELECT t2.id, t2.ds AS reg_date FROM t2) t
     ORDER BY t.id, t.reg_date),
  summary_data AS
    (SELECT   p.id,
              t1.ogrn,
              t1.inn,
              t1.name,
              t2.eq,
              p.start_period AS ds,
              p.end_period AS de,
              STANDARD_HASH (t1.ogrn || t1.inn || t1.name || '/' || t2.eq) AS hash_group
     FROM     periods p
              LEFT JOIN t1 ON (p.id = t1.id AND p.start_period BETWEEN t1.ds AND t1.de AND p.end_period BETWEEN t1.ds AND t1.de)
              LEFT JOIN t2 ON (p.id = t2.id AND p.start_period BETWEEN t2.ds AND t2.de AND p.end_period BETWEEN t2.ds AND t2.de)
     )
SELECT
  MAX(sd2.id) AS id,
  MAX(sd2.ogrn) AS ogrn,
  MAX(sd2.inn) AS inn,
  MAX(sd2.name) AS name,
  MAX(sd2.eq) AS eq,
  MIN (sd2.ds) AS ds,
  MAX (sd2.de) AS de 
FROM
  (SELECT
    sd1.*,
    SUM(hash_group_sign) OVER (ORDER BY sd1.ds) AS group_id
  FROM
  (SELECT 
    sd.*,
    CASE 
      WHEN ROW_NUMBER () OVER (PARTITION BY sd.hash_group ORDER BY sd.ds) = 1 THEN 1
      ELSE 0
    END AS hash_group_sign
  FROM   summary_data sd) sd1) sd2
GROUP BY sd2.hash_group, sd2.group_id
ORDER BY id, ds

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

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