Один товарищ рассматривал вариант устроиться на работу в Яндекс на вакансию "Асессор-разработчик".

В тестовом задании была задачка на составление SQL-запроса.



Задание:

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

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

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

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

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

Companies - компании

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

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

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

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



Вариант запроса:

 

SELECT cou.name AS `Country`, COUNT(com.id)

      FROM Companies com

     

      LEFT JOIN Cities cit

      ON cit.id = com.city_id

     

      LEFT JOIN Countries cou

      ON cit.country_id = cou.id

     

WHERE  com.labors > 1000

AND city_id IN (SELECT cit2.id

                  FROM Cities cit2

                                                                

                  LEFT JOIN Countries cou2

                  ON cit2.country_id = cou2.id

                                                                

                  WHERE cou2.population > 1000000 AND cou2.gdp > 10000000000)

                                                                

GROUP BY cou.id

                 

HAVING SUM(com.revenue) > 1000000000

 

Скорее всего опытный SQL'щик посмеется на такой реализацией задачи и сможет написать более оптимальный запрос. Если есть идеи по оптимизации - пишите свои варианты в коментариях.

Краткое описание логики запроса:

1) Во вложенном запросе получаем список id городов, у которых население более 1 миллиона человек, которые находятся в странах, имеющих валовый доход более 10 миллиардов долларов:

 

city_id IN (SELECT cit2.id

                  FROM Cities cit2

                                                                

                  LEFT JOIN Countries cou2

                  ON cit2.country_id = cou2.id

                                                                

                  WHERE cou2.population > 1000000 AND cou2.gdp > 10000000000)

 

2) Выводим список стран и количество компаний с помощью объединенного запроса:

 

SELECT cou.name AS `Country`, COUNT(com.id)

      FROM Companies com

     

      LEFT JOIN Cities cit

      ON cit.id = com.city_id

     

      LEFT JOIN Countries cou

      ON cit.country_id = cou.id

 

3) Дополнительные условия:

 

WHERE  com.labors > 1000

 

 

HAVING SUM(com.revenue) > 1000000000

 

4) Для группировки компаний в странах используем:

 

GROUP BY cou.id

 

Примечание: не рекомендую использовать в качестве готового ответа для отправки в Яндекс, только для анализа и проверки работоспособности, - чтобы потом написать свой вариант реализации. Так как этот вариант решения был отправлен в Яндекс, и, если вы его скопируте, плагиат может быть легко определен.

Тем кому лень создавать таблицы в БД с нуля, могут использовать мой тестовый дамп:

 

-- Дамп структуры для таблица yandex-sql.Cities

CREATE TABLE IF NOT EXISTS `Cities` (

  `id` int(11) NOT NULL DEFAULT '0',

  `name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,

  `population` int(11) DEFAULT NULL,

  `founded` int(11) DEFAULT NULL,

  `country_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- Дамп данных таблицы yandex-sql.Cities:

INSERT INTO `Cities` (`id`, `name`, `population`, `founded`, `country_id`) VALUES

      (1, 'Ульяновск', 750000, 1648, 1),

      (2, 'Москва', 3000000, 1420, 1),

      (3, 'Ташкент', 2500000, 956, 2),

      (4, 'Урумчи', 900000, 205, 3),

      (5, 'Шанхай', 3000000, 20, 3);

 

-- Дамп структуры для таблица yandex-sql.Companies

CREATE TABLE IF NOT EXISTS `Companies` (

  `id` int(11) NOT NULL,

  `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `city_id` int(11) NOT NULL,

  `revenue` int(11) NOT NULL,

  `labors` int(11) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- Дамп данных таблицы yandex-sql.Companies: ~9 rows (приблизительно)

INSERT INTO `Companies` (`id`, `name`, `city_id`, `revenue`, `labors`) VALUES

      (1, 'Супер-софт', 1, 900000000, 1500),

      (2, 'Мегасофт', 1, 500000000, 3000),

      (3, 'Ковер-самолет', 3, 5000000, 3000),

      (4, 'Трах-Тибидох Development', 3, 1000000000, 5000),

      (5, 'Ур Ум Чи\'ка-1', 4, 300000, 1001),

      (6, 'Ур Ум Чи\'ка-2', 4, 520000, 999),

      (7, 'Пу До Нг', 5, 600000000, 1600),

      (8, 'ZBAA Dev', 5, 520000000, 2500),

      (9, 'IBS', 2, 500, 1200);

 

-- Дамп структуры для таблица yandex-sql.Countries

CREATE TABLE IF NOT EXISTS `Countries` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,

  `population` int(11) DEFAULT NULL,

  `gdp` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

 

-- Дамп данных таблицы yandex-sql.Countries:

INSERT INTO `Countries` (`id`, `name`, `population`, `gdp`) VALUES

      (1, 'Россия', 3000000, 500000000000),

      (2, 'Узбекистан', 1000001, 200000000000),

      (3, 'Китай', 1000000000, 1000000000000);