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

SQL-задачка от Яндекса

В тестовом задании была задачка на составление 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);

 




Комментарии (7)

This comment was minimized by the moderator on the site

Спасибо Огромное! Надеюсь мне это поможет разобраться с темой SQL. Очень наглядно! Очень нравиться направление IT, все очень интересно, но сложновато.

This comment was minimized by the moderator on the site

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

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

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

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

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

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

Companies - компании

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

Постройте таблицу, где для каждой страны посчитано число компаний, удовлетворяющих условиям:

1) штаб квартира компании находится в этой стране
2) число сотрудников компании не менее 1000 человек

Постройте таблицу, не просто запрос. Хотя мне кажется теперь задание попроще)

This comment was minimized by the moderator on the site

Привет, А!
Спасибо за информацию.
Я думаю, что имея перед глазами пример с решением, можно решить и измененную версию задания

This comment was minimized by the moderator on the site

Решил самостоятельно на самом деле, бездумное копирование от лукавого) потестил, вроде работает, в целом решение похоже на твое, не особо шарю в чистом sql, собственно задание показало что надо подучить))

This comment was minimized by the moderator on the site

Привет, А. Устроился асессором? Было еще живое собеседование?

This comment was minimized by the moderator on the site

Привет, А. Устроился асессором? Было еще живое собеседование?

Нет, товарища не взяли асессором. Причину не знаем, там была очень большая анкета, и, возможно, что-то из нее было не совместимое с яндексом.
Эти ребята ведь не дают обратную связь.

This comment was minimized by the moderator on the site

With big_country as (Select countries.name as name, countries.id as id
From countries
Where countries.population > 1000000 and countries.gdp > 10000000000
)
Select bc.name,
count(companies.id) as company_count,
sum(companies.revenue) as rev
From big_country as bc
Inner Join cities on cities.country_id = bc.id
Inner Join companies on cities.id = companies.city_id and companies.labors > 1000
Group by bc.name
Having sum(companies.revenue) > 1000000000;

Здесь не опубликовано еще ни одного комментария

Оставьте свой комментарий

  1. Опубликовать комментарий как Гость.
0 Значки
Вложения (0 / 3)
Поделитесь своим местоположением
Яндекс.Метрика
Сайт работает на быстром VPS/VDS хостинге от FASTVPS