Вопросы по mysql на собеседовании: ТОП-20 хитрых вопросов по SQL для собеседования

Содержание

Лучшие вопросы средней сложности по SQL на собеседовании аналитика данных / Блог компании Дата-центр «Миран» / Хабр

Первые 70% курса по SQL кажутся довольно простыми. Сложности начинаются на остальных 30%.

С 2015 по 2019 годы я прошёл четыре цикла собеседований на должность аналитика данных и специалиста по анализу данных в более чем десятке компаний. После очередного неудачного интервью в 2017 году — когда я запутался в сложных вопросах по SQL — я начал составлять задачник с вопросами по SQL средней и высокой сложности, чтобы лучше готовиться к собеседованиям. Этот справочник очень пригодился в последнем цикле собеседований 2019 года. За последний год я поделился этим руководством с парой друзей, а благодаря дополнительному свободному времени из-за пандемии отшлифовал его — и составил этот документ.

Есть множество отличных руководств по SQL для начинающих. Мои любимые — это интерактивные курсы Codecademy по SQL и Select Star SQL от Цзы Чон Као. Но в реальности первые 70% из курса SQL довольно просты, а настоящие сложности начинаются в остальных 30%, которые не освещаются в руководствах для начинающих. Так вот, на собеседованиях для аналитиков данных и специалистов по анализу данных в технологических компаниях часто задают вопросы именно по этим 30%.

Удивительно, но я не нашёл исчерпывающего источника по таким вопросам среднего уровня сложности, поэтому составил данное руководство.

Оно полезно для собеседований, но заодно повысит вашу эффективность на текущем и будущих местах работы. Лично я считаю, что некоторые упомянутые шаблоны SQL полезны и для ETL-систем, на которых работают инструменты отчётности и функции анализа данных для выявления тенденций.

Нужно понимать, что на собеседованиях дата-аналитиков и специалистов по анализу данных задают вопросы не только по SQL. Другие общие темы включают обсуждение прошлых проектов, A/B-тестирование, разработку метрик и открытые аналитические проблемы. Примерно три года назад на Quora публиковались советы по собеседованию на должность аналитика продукта (product analyst) в Facebook. Там эта тема обсуждается более подробно. Тем не менее, если улучшение знаний по SQL поможет вам на собеседовании, то это руководство вполне стоит потраченного времени.

В будущем я могу перенести код из этого руководства на сайт вроде Select Star SQL, чтобы было проще писать инструкции SQL — и видеть результат выполнения кода в реальном времени. Как вариант — добавить вопросы как проблемы на платформу для подготовки к собеседованиям LeetCode. Пока же я просто хотел опубликовать этот документ, чтобы люди могли прямо сейчас ознакомиться с этой информацией.

Предположения о знании языка SQL: Предполагается, что у вас есть рабочие знания SQL. Вероятно, вы часто используете его на работе, но хотите отточить навыки в таких темах, как самообъединения и оконные функции.

Как использовать данное руководство: Поскольку на собеседовании часто используется доска или виртуальный блокнот (без компиляции кода), то рекомендую взять карандаш и бумагу — и записать решения для каждой проблемы, а после завершения сравнить свои записи с ответами. Или отработайте свои ответы вместе с другом, который выступит в качестве интервьюера!

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

Приведённые ответы не обязательно единственный способ решить каждую задачу. Не стесняйтесь писать комментарии с дополнительными решениями, которые можно добавить в это руководство!

Сначала стандартные советы для всех собеседований по программированию…

Внимательно выслушайте описание проблемы, повторите всю суть проблемы интервьюеру

Сформулируйте пограничный случай, чтобы продемонстрировать, что вы действительно понимаете проблему (т. е. строку, которая не будет включена в итоговый запрос SQL, который вы собираетесь написать)

(Если проблема связана с самообъединением) для своей же пользы нарисуйте, как будет выглядеть самообъединение — обычно это минимум три столбца: нужный столбец из основной таблицы, столбец для объединения из основной таблицы и столбец для объединения из вторичной таблицы
- Или, когда вы лучше освоите задачи самообъединения, можете объяснить этот шаг устно

Начните писать SQL, пусть с ошибками, вместо попыток полностью понять проблему. Формулируйте свои предположения по ходу дела, чтобы ваш интервьюер мог вас поправить.

Некоторые из перечисленных здесь проблем адаптированы из старых записей в блоге Periscope (в основном написанных Шоном Куком около 2014 года, хотя его авторство, видимо, убрали из материалов после слияния SiSense с Periscope), а также из обсуждений на StackOverflow. В случае необходимости, источники отмечены в начале каждого вопроса.

На Select Star SQL тоже хорошая подборка задачек, дополняющих проблемы из этого документа.

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

№ 1. Процентное изменение месяц к месяцу

Контекст: часто полезно знать, как изменяется ключевая метрика, например, месячная аудитория активных пользователей, от месяца к месяцу. Допустим у нас есть таблица logins в таком виде:

| user_id | date       |
|---------|------------|
| 1       | 2018-07-01 |
| 234     | 2018-07-02 |
| 3       | 2018-07-02 |
| 1       | 2018-07-02 |
| ...     | ...        |
| 234     | 2018-10-04 |

Задача: найти ежемесячное процентное изменение месячной аудитории активных пользователей (MAU).

Решение:
(Это решение, как и другие блоки кода в этом документе, содержит комментарии об элементах синтаксисе SQL, которые могут отличаться между разными вариантами SQL, и прочие заметки)

WITH mau AS 
(
  SELECT 
   /* 
    * Обычно интервьюер позволяет вам написать псевдокод для
    * функций даты, т. е. НЕ будет проверять, как вы их помните. 
    * Просто объясните на доске, что делает функция
    *
    * В Postgres доступна DATE_TRUNC(), но аналогичный результат
    * могут дать другие функции даты SQL или их комбинации
    * См. https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
    */ 
    DATE_TRUNC('month', date) month_timestamp,
    COUNT(DISTINCT user_id) mau
  FROM 
    logins 
  GROUP BY 
    DATE_TRUNC('month', date)
  )
 
 SELECT 
    /*
    * В эту инструкцию SELECT не нужно буквально включать предыдущий месяц. 
    * 
    * Но как упоминалось в разделе с советами выше, может быть полезно
    * хотя бы набросать самообъединения, чтобы не запутаться, какая
    * таблица представляет прошлый месяц к текущему и т.д.
    */ 
    a.month_timestamp previous_month, 
    a.mau previous_mau, 
    b.month_timestamp current_month, 
    b.mau current_mau, 
    ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change 
 FROM
    mau a 
 JOIN 
    /*
    * Как вариант `ON b.month_timestamp = a.month_timestamp + interval '1 month'` 
    */
    mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'

№ 2. Маркировка древовидной структуры

Контекст: предположим, у вас есть таблица tree с двумя столбцами: в первом указаны узлы, а во втором — родительские узлы.

node   parent
1       2
2       5
3       5
4       3
5       NULL

Задача: написать SQL таким образом, чтобы мы обозначили каждый узел как внутренний (inner), корневой (root) или конечный узел/лист (leaf), так что для вышеперечисленных значений получится следующее:

node    label  
1       Leaf
2       Inner
3       Inner
4       Leaf
5       Root

(Примечание: более подробно о терминологии древовидной структуры данных можно почитать здесь. Однако для решения этой проблемы она не нужна!)

Решение:
Благодарность: это более обобщённое решение предложил Фабиан Хофман 2 мая 2020 года. Спасибо, Фабиан!

WITH join_table AS
(
    SELECT 
        cur.node, 
        cur.parent, 
        COUNT(next.node) AS num_children
    FROM 
        tree cur
    LEFT JOIN 
        tree next ON (next.parent = cur.node)
    GROUP BY 
        cur.node, 
        cur.parent
)

SELECT
    node,
    CASE
        WHEN parent IS NULL THEN "Root"
        WHEN num_children = 0 THEN "Leaf"
        ELSE "Inner"
    END AS label
FROM 
    join_table

Альтернативное решение, без явных соединений:

Благодарность: Уильям Чарджин 2 мая 2020 года обратил внимание на необходимость условия WHERE parent IS NOT NULL, чтобы это решение возвращало Leaf вместо NULL. Спасибо, Уильям!

SELECT 
    node,
    CASE 
        WHEN parent IS NULL THEN 'Root'
        WHEN node NOT IN 
            (SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
        WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
    END AS label 
 from 
    tree

№ 3. Удержание пользователей в месяц (несколько частей)

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Использование самообъединений для расчёта показателей удержания, оттока и реактивации».

Часть 1

Контекст: допустим, у нас есть статистика по авторизации пользователей на сайте в таблице logins:

| user_id | date       |
|---------|------------|
| 1       | 2018-07-01 |
| 234     | 2018-07-02 |
| 3       | 2018-07-02 |
| 1       | 2018-07-02 |
| ...     | ...        |
| 234     | 2018-10-04 |

Задача: написать запрос, который получает количество удержанных пользователей в месяц. В нашем случае данный параметр определяется как количество пользователей, которые авторизовались в системе и в этом, и в предыдущем месяце.

Решение:

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT a.user_id) retained_users 
 FROM 
    logins a 
 JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                             interval '1 month'
 GROUP BY 
    date_trunc('month', a.date)

Благодарность:
Том Моэртел указал на то, что предварительная дедубликация user_id перед самообъединением делает решение более эффективным, и предложил код ниже. Спасибо, Том!

Альтернативное решение:

WITH DistinctMonthlyUsers AS (
  /*
  * Для каждого месяца определяем *набор* пользователей, которые
  * выполнили авторизацию
  */
    SELECT DISTINCT
      DATE_TRUNC('MONTH', a.date) AS month_timestamp,
      user_id
    FROM logins
  )

SELECT
  CurrentMonth.month_timestamp month_timestamp,
  COUNT(PriorMonth.user_id) AS retained_user_count
FROM 
    DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN 
    DistinctMonthlyUsers AS PriorMonth
  ON
    CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
    AND 
    CurrentMonth.user_id = PriorMonth.user_id

Часть 2

Задача: теперь возьмём предыдущую задачу по вычислению количества удержанных пользователей в месяц — и перевернём её с ног на голову. Напишем запрос для подсчёта пользователей, которые не вернулись на сайт в этом месяце. То есть «потерянных» пользователей.

Решение:

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT b.user_id) churned_users 
FROM 
    logins a 
FULL OUTER JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                         interval '1 month'
WHERE 
    a.user_id IS NULL 
GROUP BY 
    DATE_TRUNC('month', a.date)

Обратите внимание, что эту проблему можно решить также с помощью соединений LEFT или RIGHT.

Часть 3

Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить и перейти к следующей задаче.

Контекст: итак, мы хорошо справились с двумя предыдущими проблемами. По условиям новой задачи теперь у нас появилась таблица потерянных пользователей user_churns. Если пользователь была активен в прошлом месяце, но затем не активен в этом, то он вносится в таблицу за этот месяц. Вот как выглядит user_churns:

| user_id | month_date |
|---------|------------|
| 1       | 2018-05-01 |
| 234     | 2018-05-01 |
| 3       | 2018-05-01 |
| 12      | 2018-05-01 |
| ...     | ...        |
| 234     | 2018-10-01 |

Задача: теперь вы хотите провести когортный анализ, то есть анализ совокупности активных пользователей, которые были реактивированы в прошлом. Создайте таблицу с такими пользователями. Для создания когорты можете использовать таблицы user_churns и logins. В Postgres текущая временная метка доступна через current_timestamp.

Решение:

WITH user_login_data AS 
(
    SELECT 
        DATE_TRUNC('month', a.date) month_timestamp,
        a.user_id,
        /* 
        * По крайней мере, в тех вариантах SQL, что я использовал, 
        * не нужно включать в инструкцию SELECT колонки из HAVING.
        * Я здесь выписал их для большей ясности.  
        */ 
        MAX(b.month_date) as most_recent_churn, 
        MAX(DATE_TRUNC('month', c.date)) as most_recent_active 
     FROM 
        logins a
     JOIN 
        user_churns b 
            ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date 
     JOIN
        logins c 
            ON a.user_id = c.user_id 
            AND 
            DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
     WHERE 
        DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
     GROUP BY 
        DATE_TRUNC('month', a.date),
        a.user_id
     HAVING 
        most_recent_churn > most_recent_active

№ 4. Нарастающий итог

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Моделирование денежных потоков в SQL».

Контекст: допустим, у нас есть таблица transactions в таком виде:

| date       | cash_flow |
|------------|-----------|
| 2018-01-01 | -1000     |
| 2018-01-02 | -100      |
| 2018-01-03 | 50        |
| ...        | ...       |

Где cash_flow — это выручка минус затраты за каждый день.

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

| date       | cumulative_cf |
|------------|---------------|
| 2018-01-01 | -1000         |
| 2018-01-02 | -1100         |
| 2018-01-03 | -1050         |
| ...        | ...           |

Решение:

SELECT 
    a.date date, 
    SUM(b.cash_flow) as cumulative_cf 
FROM
    transactions a
JOIN b 
    transactions b ON a.date >= b.date 
GROUP BY 
    a.date 
ORDER BY 
    date ASC

Альтернативное решение с использованием оконной функции (более эффективное!):

SELECT 
    date, 
    SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf 
FROM
    transactions 
ORDER BY 
    date ASC

№ 5. Скользящее среднее

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Скользящие средние в MySQL и SQL Server».

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

Контекст: допустим, у нас есть таблица signups в таком виде:

| date       | sign_ups |
|------------|----------|
| 2018-01-01 | 10       |
| 2018-01-02 | 20       |
| 2018-01-03 | 50       |
| ...        | ...      |
| 2018-10-01 | 35       |

Задача: написать запрос, чтобы получить 7-дневное скользящее среднее ежедневных регистраций.

Решение:

SELECT 
  a.date, 
  AVG(b.sign_ups) average_sign_ups 
FROM 
  signups a 
JOIN 
  signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY 
  a.date

№ 6. Несколько условий соединения

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Анализ вашей электронной почты с помощью SQL».

Контекст: скажем, наша таблица emails содержит электронные письма, отправленные с адреса [email protected] и полученные на него:

| id | subject  | from         | to           | timestamp           |
|----|----------|--------------|--------------|---------------------|
| 1  | Yosemite | [email protected]   | [email protected] | 2018-01-02 12:45:03 |
| 2  | Big Sur  | [email protected]  | [email protected] | 2018-01-02 16:30:01 |
| 3  | Yosemite | [email protected] | [email protected]   | 2018-01-02 16:35:04 |
| 4  | Running  | [email protected]   | [email protected]   | 2018-01-03 08:12:45 |
| 5  | Yosemite | [email protected]   | [email protected] | 2018-01-03 14:02:01 |
| 6  | Yosemite | [email protected] | [email protected]   | 2018-01-03 15:01:05 |
| .. | ..       | ..           | ..           | ..                  |

Задача:написать запрос, чтобы получить время отклика на каждое письмо (id), отправленное на [email protected]. Не включать письма на другие адреса. Предположим, что у каждого треда уникальная тема. Имейте в виду, что в треде может быть несколько писем туда и обратно между [email protected] и другими адресатами.

Решение:

SELECT 
    a.id, 
    MIN(b.timestamp) - a.timestamp as time_to_respond 
FROM 
    emails a 
JOIN
    emails b 
        ON 
            b.subject = a.subject 
        AND 
            a.to = b.from
        AND 
            a.from = b.to 
        AND 
            a.timestamp < b.timestamp 
 WHERE 
    a.to = '[email protected]' 
 GROUP BY 
    a.id

№ 1. Найти идентификатор с максимальным значением

Контекст: Допустим, у нас есть таблица salaries с данными об отделах и зарплате сотрудников в следующем формате:

  depname  | empno | salary |     
-----------+-------+--------+
 develop   |    11 |   5200 | 
 develop   |     7 |   4200 | 
 develop   |     9 |   4500 | 
 develop   |     8 |   6000 | 
 develop   |    10 |   5200 | 
 personnel |     5 |   3500 | 
 personnel |     2 |   3900 | 
 sales     |     3 |   4800 | 
 sales     |     1 |   5000 | 
 sales     |     4 |   4800 |

Задача: написать запрос, чтобы получить empno с самой высокой зарплатой. Убедитесь, что ваше решение обрабатывает случаи одинаковых зарплатами!

Решение:

WITH max_salary AS (
    SELECT 
        MAX(salary) max_salary
    FROM 
        salaries
    )
SELECT 
    s.empno
FROM 
    salaries s
JOIN 
    max_salary ms ON s.salary = ms.max_salary

Альтернативное решение с использованием RANK():

WITH sal_rank AS 
  (SELECT 
    empno, 
    RANK() OVER(ORDER BY salary DESC) rnk
  FROM 
    salaries)
SELECT 
  empno
FROM
  sal_rank
WHERE 
  rnk = 1;

№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)

Часть 1

Контекст: допустим, у нас есть таблица salaries в таком формате:

  depname  | empno | salary |     
-----------+-------+--------+
 develop   |    11 |   5200 | 
 develop   |     7 |   4200 | 
 develop   |     9 |   4500 | 
 develop   |     8 |   6000 | 
 develop   |    10 |   5200 | 
 personnel |     5 |   3500 | 
 personnel |     2 |   3900 | 
 sales     |     3 |   4800 | 
 sales     |     1 |   5000 | 
 sales     |     4 |   4800 |

Задача: написать запрос, который возвращает ту же таблицу, но с новым столбцом, в котором указана средняя зарплата по департаменту. Мы бы ожидали таблицу в таком виде:

  depname  | empno | salary | avg_salary |     
-----------+-------+--------+------------+
 develop   |    11 |   5200 |       5020 |
 develop   |     7 |   4200 |       5020 | 
 develop   |     9 |   4500 |       5020 |
 develop   |     8 |   6000 |       5020 | 
 develop   |    10 |   5200 |       5020 | 
 personnel |     5 |   3500 |       3700 |
 personnel |     2 |   3900 |       3700 |
 sales     |     3 |   4800 |       4867 | 
 sales     |     1 |   5000 |       4867 | 
 sales     |     4 |   4800 |       4867 |

Решение:

SELECT 
    *, 
    /*
    * AVG() is a Postgres command, but other SQL flavors like BigQuery use 
    * AVERAGE()
    */ 
    ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
    salaries

Часть 2

Задача: напишите запрос, который добавляет столбец с позицией каждого сотрудника в табели на основе его зарплаты в своём отделе, где сотрудник с самой высокой зарплатой получает позицию 1. Мы бы ожидали таблицу в таком виде:

  depname  | empno | salary | salary_rank |     
-----------+-------+--------+-------------+
 develop   |    11 |   5200 |           2 |
 develop   |     7 |   4200 |           5 | 
 develop   |     9 |   4500 |           4 |
 develop   |     8 |   6000 |           1 | 
 develop   |    10 |   5200 |           2 | 
 personnel |     5 |   3500 |           2 |
 personnel |     2 |   3900 |           1 |
 sales     |     3 |   4800 |           2 | 
 sales     |     1 |   5000 |           1 | 
 sales     |     4 |   4800 |           2 |

Решение:

SELECT 
    *, 
    RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
 FROM  
    salaries

№ 1. Гистограммы

Контекст: Допустим, у нас есть таблица sessions, где каждая строка представляет собой сеанс потоковой передачи видео с длиной в секундах:

| session_id | length_seconds |
|------------|----------------|
| 1          | 23             |
| 2          | 453            |
| 3          | 27             |
| ..         | ..             |

Задача: написать запрос, чтобы подсчитать количество сеансов, которые попадают промежутки по пять секунд, т. е. для приведённого выше фрагмента результат будет примерно такой:

| bucket  | count |
|---------|-------|
| 20-25   | 2     |
| 450-455 | 1     |

Максимальная оценка засчитывается за надлежащие метки строк («5-10» и т. д.)

Решение:

WITH bin_label AS 
(SELECT 
    session_id, 
    FLOOR(length_seconds/5) as bin_label 
 FROM
    sessions 
 )
 SELECT 
    CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket, 
    COUNT(DISTINCT session_id) count 
 GROUP BY 
    bin_label
 ORDER BY 
    bin_label ASC

№ 2. Перекрёстное соединение (несколько частей)

Часть 1

Контекст: допустим, у нас есть таблица state_streams, где в каждой строке указано название штата и общее количество часов потоковой передачи с видеохостинга:

| state | total_streams |
|-------|---------------|
| NC    | 34569         |
| SC    | 33999         |
| CA    | 98324         |
| MA    | 19345         |
| ..    | ..            |

(На самом деле в агрегированных таблицах такого типа обычно есть ещё столбец даты, но для этой задачи мы его исключим)

Задача: написать запрос, чтобы получить пары штатов с общим количеством потоков в пределах тысячи друг от друга. Для приведённого выше фрагмента мы хотели бы увидеть что-то вроде:

| state_a | state_b |
|---------|---------|
| NC      | SC      |
| SC      | NC      |

Решение:

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a
 CROSS JOIN 
    state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state

Для информации, перекрёстные соединения также можно писать без явного указания соединения:

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state

Часть 2

Примечание: этот скорее бонусный вопрос, чем реально важный шаблон SQL. Можете его пропустить!

Задача: как можно изменить SQL из предыдущего решения, чтобы удалить дубликаты? Например, на примере той же таблицы, чтобы пара NC и SC появилась только один раз, а не два.

Решение:

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state > b.state

№ 3. Продвинутые расчёты

Благодарность: эта задача адаптирована из обсуждения по вопросу, который я задал на StackOverflow (мой ник zthomas.nc).

Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить её!

Контекст: допустим, у нас есть таблица table такого вида, где одному и тому же пользователю user могут соответствовать разные значения класса class:

| user | class |
|------|-------|
| 1    | a     |
| 1    | b     |
| 1    | b     |
| 2    | b     |
| 3    | a     |

Задача: предположим, что существует только два возможных значения для класса. Напишите запрос для подсчёта количества пользователей в каждом классе. При этом пользователи с обеими метками a и b должны относиться к классу b.

Для нашего образца получится такой результат:

| class | count |
|-------|-------|
| a     | 1     |
| b     | 2     |

Решение:

WITH usr_b_sum AS 
(
    SELECT 
        user, 
        SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
    FROM 
        table
    GROUP BY 
        user
), 

usr_class_label AS 
(
    SELECT 
        user, 
        CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class 
    FROM 
        usr_b_sum
)

SELECT 
    class, 
    COUNT(DISTINCT user) count 
FROM
    usr_class_label
GROUP BY 
    class 
ORDER BY 
    class ASC

Альтернативное решение использует инструкции SELECT в операторах SELECT и UNION:

SELECT 
    "a" class,
    COUNT(DISTINCT user_id) - 
        (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count 
UNION
SELECT 
    "b" class,
    (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count

[Перевод] Топ-65 вопросов по SQL с собеседований, к которым вы должны подготовиться в 2019 году. Часть I — MAILSGUN.RU

Перевод статьи подготовлен для студентов курса «MS SQL Server разработчик»

Реляционные базы данных являются одними из наиболее часто используемых баз данных по сей день, и поэтому навыки работы с SQL для большинства должностей являются обязательными. В этой статье с вопросами по SQL с собеседований я познакомлю вас с наиболее часто задаваемыми вопросами по SQL (Structured Query Language — язык структурированных запросов). Эта статья является идеальным руководством для изучения всех концепций, связанных с SQL, Oracle, MS SQL Server и базой данных MySQL.

Наша статья с вопросами по SQL — универсальный ресурс, с помощью которого вы можете ускорить подготовку к собеседованию. Она состоит из набора из 65 самых распространенных вопросов, которые интервьюер может задать во время собеседования. Оно обычно начинается с базовых вопросов по SQL, а затем переходит к более сложным на основе обсуждения и ваших ответов. Эти вопросы по SQL с собеседований помогут вам извлечь максимальную выгоду на различных уровнях понимания.
Давайте начнем!

Вопрос 1. В чем разница между операторами DELETE и TRUNCATE?

DELETE	TRUNCATE
Используется для удаления строки в таблице	Используется для удаления всех строк из таблицы
Вы можете восстановить данные после удаления	Вы не можете восстановить данные
DML-команда	DDL-команда
Медленнее, чем оператор TRUNCATE	Быстрее

№ Вопрос 2. Из каких подмножеств состоит SQL?

DDL (Data Definition Language, язык описания данных) — позволяет выполнять различные операции с базой данных, такие как CREATE (создание), ALTER (изменение) и DELETE (удаление объектов).
DML (Data Manipulation Language, язык управления данными) — позволяет получать доступ к данным и манипулировать ими, например, вставлять, обновлять, удалять и извлекать данные из базы данных.
DCL (Data Control Language, язык контролирования данных) — позволяет контролировать доступ к базе данных. Пример — GRANT (предоставить права), REVOKE (отозвать права).

Вопрос 3. Что подразумевается под СУБД? Какие существуют типы СУБД?

База данных — структурированная коллекция данных. Система управления базами данных (СУБД) — программное обеспечение, которое взаимодействует с пользователем, приложениями и самой базой данных для сбора и анализа данных. СУБД позволяет пользователю взаимодействовать с базой данных. Данные, хранящиеся в базе данных, могут быть изменены, извлечены и удалены. Они могут быть любых типов, таких как строки, числа, изображения и т. д.

Существует два типа СУБД:

Реляционная система управления базами данных: данные хранятся в отношениях (таблицах). Пример — MySQL.
Нереляционная система управления базами данных: не существует понятия отношений, кортежей и атрибутов. Пример — Mongo

Вопрос 4. Что подразумевается под таблицей и полем в SВопросL?

Таблица — организованный набор данных в виде строк и столбцов. Поле — это столбцы в таблице. Например:
Таблица: Student_Information
Поле: Stu_Id, Stu_Name, Stu_Marks

Вопрос 5. Что такое объединения в SQL?

Для соединения строк из двух или более таблиц на основе связанного между ними столбца используется оператор JOIN. Он используется для объединения двух таблиц или получения данных оттуда. В SQL есть 4 типа объединения, а именно:

Inner Join (Внутреннее объединение)
Right Join (Правое объединение)
Left Join (Левое объединение)
Full Join (Полное объединение)

Вопрос 6. В чем разница между типом данных CHAR и VARCHAR2 в SQL?

И Char, и Varchar2 служат символьными типами данных, но varchar2 используется для строк символов переменной длины, тогда как Char используется для строк фиксированной длины. Например, char(10) может хранить только 10 символов и не сможет хранить строку любой другой длины, тогда как varchar2(10) может хранить строку любой длины до 10, т.е. например 6, 8 или 2.

Вопрос 7. Что такое первичный ключ (Primary key)?

Первичный ключ — столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице.
Однозначно идентифицирует одну строку в таблице
Нулевые (Null) значения не допускаются

_Пример: в таблице Student StuID является первичным ключом.

Вопрос 8. Что такое ограничения (Constraints)?

Ограничения (constraints) используются для указания ограничения на тип данных таблицы. Они могут быть указаны при создании или изменении таблицы. Пример ограничений:

NOT NULL
CHECK
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY

Вопрос 9. В чем разница между SQL и MySQL?

SQL — стандартный язык структурированных запросов (Structured Query Language) на основе английского языка, тогда как MySQL — система управления базами данных. SQL — ядро реляционной базы данных, которое используется для доступа к базе данных и управления ею, MySQL — реляционная СУБД (система управления базами данных), также как и SQL Server, Informix и т. д.

Вопрос 10. Что такое уникальный ключ (Unique key)?

Однозначно идентифицирует одну строку в таблице.
Допустимо множество уникальных ключей в одной таблице.
Допустимы нулевые значения.

Вопрос 11. Что такое внешний ключ (Foreign key)?

Внешний ключ поддерживает ссылочную целостность, обеспечивая связь между данными в двух таблицах.
Внешний ключ в дочерней таблице ссылается на первичный ключ в родительской таблице.
Ограничение внешнего ключа предотвращает действия, которые разрушают связи между дочерней и родительской таблицами.

Вопрос 12. Что подразумевается под целостностью данных?

Целостность данных определяет точность, а также согласованность данных, хранящихся в базе данных. Она также определяет ограничения целостности для обеспечения соблюдения бизнес-правил для данных, когда они вводятся в приложение или базу данных.

Вопрос 13. В чем разница между кластеризованным и некластеризованным индексами в SQL?

Различия между кластеризованным и некластеризованным индексами в SQL:
Кластерный индекс используется для простого и быстрого извлечения данных из базы данных, тогда как чтение из некластеризованного индекса происходит относительно медленнее.
Кластеризованный индекс изменяет способ хранения записей в базе данных — он сортирует строки по столбцу, который установлен как кластеризованный индекс, тогда как в некластеризованном индексе он не меняет способ хранения, но создает отдельный объект внутри таблицы, который указывает на исходные строки таблицы при поиске.
Одна таблица может иметь только один кластеризованный индекс, тогда как некластеризованных у нее может быть много.

Вопрос 14. Напишите SQL-запрос для отображения текущей даты.

В SQL есть встроенная функция GetDate (), которая помогает возвращать текущий timestamp/дату.

Вопрос 15. Перечислите типы объединений.

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

Inner join (Внутреннее объединение): Inner join в MySQL является наиболее распространенным типом. Оно используется для возврата всех строк из нескольких таблиц, для которых выполняется условие объединения.

Left Join (Левое объединение): Left Join в MySQL используется для возврата всех строк из левой (первой) таблицы и только совпадающих строк из правой (второй) таблицы, для которых выполняется условие соединения.

Right Join (Правое объединение): Right Join в MySQL используется для возврата всех строк из правой (второй) таблицы и только совпадающих строк из левой (первой) таблицы, для которых выполняется условие соединения.

Full Join (Полное объединение): Full Join возвращает все записи, для которых есть совпадение в любой из таблиц. Следовательно, он возвращает все строки из левой таблицы и все строки из правой таблицы.

Вопрос 16. Что вы подразумеваете под денормализацией?

Денормализация — техника, которая используется для доступа к данным из высших к низшим формам базы данных. Она помогает менеджерам баз данных повысить производительность всей инфраструктуры, поскольку вносит избыточность в таблицу. Он добавляет избыточные данные в таблицу, учитывая частые запросы к базе данных, которые объединяют данные из разных таблиц в одну таблицу.

Вопрос 17. Что такое сущности и отношения?

Сущности: человек, место или объект в реальном мире, данные о которых могут храниться в базе данных. В таблицах хранятся данные, которые представляют один тип сущности. Например — база данных банка имеет таблицу клиентов для хранения информации о клиентах. Таблица клиентов хранит эту информацию в виде набора атрибутов (столбцы в таблице) для каждого клиента.

Отношения: отношения или связи между сущностями, которые имеют какое-то отношение друг к другу. Например — имя клиента связано с номером учетной записи клиента и контактной информацией, которая может быть в той же таблице. Также могут быть отношения между отдельными таблицами (например, клиент к счетам).

Вопрос 18. Что такое индекс?

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

Вопрос 19. Опишите различные типы индексов.

Есть три типа индексов, а именно:

Уникальный индекс (Unique Index): этот индекс не позволяет полю иметь повторяющиеся значения, если столбец индексируется уникально. Если первичный ключ определен, уникальный индекс может быть применен автоматически.
Кластеризованный индекс (Clustered Index): этот индекс меняет физический порядок таблицы и выполняет поиск на основе значений ключа. Каждая таблица может иметь только один кластеризованный индекс.
Некластеризованный индекс (Non-Clustered Index): не изменяет физический порядок таблицы и поддерживает логический порядок данных. Каждая таблица может иметь много некластеризованных индексов.

Вопрос 20. Что такое нормализация и каковы ее преимущества?

Нормализация — процесс организации данных, цель которого избежать дублирования и избыточности. Некоторые из преимуществ:

Лучшая организация базы данных
Больше таблиц с небольшими строками
Эффективный доступ к данным
Большая гибкость для запросов
Быстрый поиск информации
Проще реализовать безопасность данных
Позволяет легко модифицировать
Сокращение избыточных и дублирующихся данных
Более компактная база данных
Обеспечивает согласованность данных после внесения изменений

Вопрос 21. В чем разница между командами DROP и TRUNCATE?

Команда DROP удаляет саму таблицу, и ее нельзя откатить из базы данных, тогда как команда TRUNCATE удаляет все строки из таблицы.

Вопрос 22. Объясните различные типы нормализации.

Существует много последовательных уровней нормализации. Это так называемые нормальные формы. Каждая последующая нормальная форма включает предыдущую. Первых трех нормальных форм обычно достаточно.

Первая нормальная форма (1NF) — нет повторяющихся групп в строках
Вторая нормальная форма (2NF) — каждое неключевое (поддерживающее) значение столбца зависит от всего первичного ключа
Третья нормальная форма (3NF) — каждое неключевое значение зависит только от первичного ключа и не имеет зависимости от другого неключевого значения столбца

Вопрос 23. Что такое свойство ACID в базе данных?

ACID означает атомарность (Atomicity), согласованность (Consistency), изолированность (Isolation), долговечность (Durability). Он используется для обеспечения надежной обработки транзакций данных в системе базы данных.

Атомарность. Гарантирует, что транзакция будет полностью выполнена или потерпит неудачу, где транзакция представляет одну логическую операцию данных. Это означает, что при сбое одной части любой транзакции происходит сбой всей транзакции и состояние базы данных остается неизменным.

Согласованность. Гарантирует, что данные должны соответствовать всем правилам валидации. Проще говоря, вы можете сказать, что ваша транзакция никогда не оставит вашу базу данных в недопустимом состоянии.

Изолированность. Основной целью изолированности является контроль параллелизма.

Долговечность. Долговечность подразумевает, что если транзакция запущена, она будет происходить независимо от того, что может встать у нее на пути, например, потеря питания, сбой или ошибки любого рода.

Вопрос 24. Что вы подразумеваете под «триггером» в SQL?

Триггер в SQL — особый тип хранимых процедур, которые предназначены для автоматического выполнения в момент или после изменения данных. Это позволяет вам выполнить пакет кода, когда вставка, обновление или любой другой запрос выполняется к определенной таблице.

Вопрос 25. Какие операторы доступны в SQL?

В SQL доступно три типа оператора, а именно:

Арифметические Операторы
Логические Операторы
Операторы сравнения

Вопрос 26. Совпадают ли значения NULL со значениями нуля или пробела?

Значение NULL вовсе не равно нулю или пробелу. Значение NULL представляет значение, которое недоступно, неизвестно, присвоено или неприменимо, тогда как ноль — это число, а пробел — символ.

Вопрос 27. В чем разница между перекрестным (cross join) и естественным (natural join) объединением?

Перекрестное объединение создает перекрестное или декартово произведение двух таблиц, тогда как естественное объединение основано на всех столбцах, имеющих одинаковое имя и типы данных в обеих таблицах.

Вопрос 28. Что такое подзапрос в SQL?

Подзапрос — это запрос внутри другого запроса, в котором определен запрос для извлечения данных или информации из базы данных. В подзапросе внешний запрос называется основным запросом, тогда как внутренний запрос называется подзапросом. Подзапросы всегда выполняются первыми, а результат подзапроса передается в основной запрос. Он может быть вложен в SELECT, UPDATE или любой другой запрос. Подзапрос также может использовать любые операторы сравнения, такие как >, < или =.

Вопрос 29. Какие бывают типы подзапросов?

Существует два типа подзапросов, а именно: коррелированные и некоррелированные.

Коррелированный подзапрос: это запрос, который выбирает данные из таблицы, на которую ссылается внешний запрос. Он не считается независимым запросом, поскольку ссылается на другую таблицу или столбец в таблице.
Некоррелированный подзапрос: этот запрос является независимым запросом, в котором выходные данные подзапроса подставляются в основной запрос.

Вопрос 30. Перечислите способы получить количество записей в таблице?

Для подсчета количества записей в таблице вы можете использовать следующие команды:
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

Ещё 35 вопросов с ответами опубликуем в следующей части… Следите за новостями!

Топ 10 вопросов для собеседования на позицию Администратор MS SQL

Коллеги, добрый день. Сегодня мне бы хотелось обсудить с вами вопросы, которые кажутся для меня наиболее интересными для обсуждения с кандидатом на позицию Администратор MS SQL Server. Раз тема называется «Топ 10», то я выбрал для вас 10 наиболее интересных вопросов в порядке возрастания сложности. Как бонус ниже «Топ 10» будут приведены дополнительные вопросы, которые так же могут быть интересными.

Топ 10 вопросов для собеседования на позицию Администратор MS SQL Server:

1. Расскажите о вашем опыте работы и какие задачи выполняли лично?

2. Назовите основной инструмент управления Microsoft SQL Server?

3. Какие средства мониторинга и поиска узких мест MS SQL Server вы знаете?

4. Какие виды backup существуют и чем они отличаются?

5. Какие три операции необходимо регулярно выполнять с БД, чтобы поддерживать её работоспособность на должном уровне?

6. Каковы плюсы и минусы использования индексов?

7. Что такое «join»?

8. Что такое триггер?

9. Знаете ли вы что такое DMV? Для чего их можно использовать?

10. Какие способы обеспечения отказоустойчивости вы знаете?

Примерно такие бы вопросы я задал человеку, претендующему на данную вакансию. Как многие уже наверное знают, собеседование это гораздо больше чем вопросы по технологии, важно понять что за человек сидит перед тобой и личные качества человека имеют не последнюю роль. Как и обещал вот дополнительные, интересные вопросы для собеседования.

Дополнительные вопросы:

1. Очень интересный и очень простой вопрос для тех, кто понимает работу SQL Server звучит следующим образом — «В каких случаях запрос select * from table where id = id вернет не все содержимое таблицы»

2. Что такое журнал транзакций?

3. Отличие функций от хранимых процедур

4. Какая разница между «where» и «having» выражениями?

5. Почему выражения «group by» или «order by» дорогие для выполнения (снижают производительность)?

6. Какие манипуляции необходимо выполнить, чтобы ускорить работу БД и запросов?

7. Что такое нормализация и денормализация? Зачем используется денормализация?

8. Что такое покрывающие индексы?

9. Какие службы Microsoft SQL Server вы знаете и для чего они нужны?

10. Что такое репликация? Какие виды репликации Вы знаете? Чем они отличаются?

11. Без чего нельзя добавить таблицу в репликацию?

12. Какая разница между varchar и nvarchar

Вопросы от опытных администраторам вам

1. Как вы изучаете новые вещи?

Если вы не развиваетесь, то это существенно уменьшает ваши шансы остаться в профессии

2. Я менеджер, а вы администратор MS SQL Server, объясните мне почему мы не должны перенести проекты на Oracle или MySQL?

Очень желательно если вы разбираетесь в конкурентах и знаете их слабые и ваши сильные стороны

3. Менеджеру нужен новый экземпляр SQL Server, что вы у него спросите?

Здесь нет однозначного ответа. Кто-то хочется услышать про возможность виртуализации, критичность и редакцию SQL Server, кто-то про Collation, требования к самому серверу или размер данных

4. Напишите скрипт, который реализует следующую логику: «Напечатайте числа от 1 до 100, для чисел кратных трем, выведите «Три» вместо числа,для кратных пяти — «Пять». Для чисел, кратных как трем так и пяти выведите «ТриПять».

Достаточно распространённый вопрос за границей, его можно решить разными способами.

5. В каких случаях и почему некластерный индекс может быть быстрее кластерного?

6. Что делать, если диск с tempdb пропал. Как запустить SQL Server?

А какие вопросы кажутся важными вам? Пишите в комментариях, почту ([email protected]) и вконтакте (https://vk.com/sqlcom).

Facebook

Twitter

Вконтакте

Google+

Лучшие вопросы средней сложности по SQL на собеседовании аналитика данных

Первые 70% курса по SQL кажутся довольно простыми. Сложности начинаются на остальных 30%.

Сделанные допущения и как использовать руководство

Советы по решению сложных задач на собеседованиях по SQL

Задачи на самообъединения
№ 1. Процентное изменение месяц к месяцу

№ 2. Маркировка древовидной структуры

№ 3. Удержание пользователей в месяц (несколько частей)
Часть 1

Часть 2

Часть 3

№ 4. Нарастающий итог

№ 5. Скользящее среднее

№ 6. Несколько условий соединения

Задачи на оконные функции
№ 1. Найти идентификатор с максимальным значением

№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)
Часть 1

Часть 2

Другие задачи средней и высокой сложности
№ 1. Гистограммы

№ 2. Перекрёстное соединение (несколько частей)
Часть 1

Часть 2

№ 3. Продвинутые расчёты

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

Приведённые ответы не обязательно единственный способ решить каждую задачу. Не стесняйтесь писать комментарии с дополнительными решениями, которые можно добавить в это руководство!

Сначала стандартные советы для всех собеседований по программированию…

Внимательно выслушайте описание проблемы, повторите всю суть проблемы интервьюеру

Сформулируйте пограничный случай, чтобы продемонстрировать, что вы действительно понимаете проблему (т. е. строку, которая не будет включена в итоговый запрос SQL, который вы собираетесь написать)

(Если проблема связана с самообъединением) для своей же пользы нарисуйте, как будет выглядеть самообъединение — обычно это минимум три столбца: нужный столбец из основной таблицы, столбец для объединения из основной таблицы и столбец для объединения из вторичной таблицы
- Или, когда вы лучше освоите задачи самообъединения, можете объяснить этот шаг устно

Начните писать SQL, пусть с ошибками, вместо попыток полностью понять проблему. Формулируйте свои предположения по ходу дела, чтобы ваш интервьюер мог вас поправить.

На Select Star SQL тоже хорошая подборка задачек, дополняющих проблемы из этого документа.

№ 1. Процентное изменение месяц к месяцу

| user_id | date       |
|---------|------------|
| 1       | 2018-07-01 |
| 234     | 2018-07-02 |
| 3       | 2018-07-02 |
| 1       | 2018-07-02 |
| ...     | ...        |
| 234     | 2018-10-04 |

Задача: найти ежемесячное процентное изменение месячной аудитории активных пользователей (MAU).

WITH mau AS 
(
  SELECT 
   /* 
    * Обычно интервьюер позволяет вам написать псевдокод для
    * функций даты, т. е. НЕ будет проверять, как вы их помните. 
    * Просто объясните на доске, что делает функция
    *
    * В Postgres доступна DATE_TRUNC(), но аналогичный результат
    * могут дать другие функции даты SQL или их комбинации
    * См. https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
    */ 
    DATE_TRUNC('month', date) month_timestamp,
    COUNT(DISTINCT user_id) mau
  FROM 
    logins 
  GROUP BY 
    DATE_TRUNC('month', date)
  )
 
 SELECT 
    /*
    * В эту инструкцию SELECT не нужно буквально включать предыдущий месяц. 
    * 
    * Но как упоминалось в разделе с советами выше, может быть полезно
    * хотя бы набросать самообъединения, чтобы не запутаться, какая
    * таблица представляет прошлый месяц к текущему и т.д.
    */ 
    a.month_timestamp previous_month, 
    a.mau previous_mau, 
    b.month_timestamp current_month, 
    b.mau current_mau, 
    ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change 
 FROM
    mau a 
 JOIN 
    /*
    * Как вариант `ON b.month_timestamp = a.month_timestamp + interval '1 month'` 
    */
    mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'

№ 2. Маркировка древовидной структуры

node   parent
1       2
2       5
3       5
4       3
5       NULL

node    label  
1       Leaf
2       Inner
3       Inner
4       Leaf
5       Root

WITH join_table AS
(
    SELECT 
        cur.node, 
        cur.parent, 
        COUNT(next.node) AS num_children
    FROM 
        tree cur
    LEFT JOIN 
        tree next ON (next.parent = cur.node)
    GROUP BY 
        cur.node, 
        cur.parent
)

SELECT
    node,
    CASE
        WHEN parent IS NULL THEN "Root"
        WHEN num_children = 0 THEN "Leaf"
        ELSE "Inner"
    END AS label
FROM 
    join_table

Альтернативное решение, без явных соединений:

SELECT 
    node,
    CASE 
        WHEN parent IS NULL THEN 'Root'
        WHEN node NOT IN 
            (SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
        WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
    END AS label 
 from 
    tree

№ 3. Удержание пользователей в месяц (несколько частей)

Часть 1

Контекст: допустим, у нас есть статистика по авторизации пользователей на сайте в таблице logins:

| user_id | date       |
|---------|------------|
| 1       | 2018-07-01 |
| 234     | 2018-07-02 |
| 3       | 2018-07-02 |
| 1       | 2018-07-02 |
| ...     | ...        |
| 234     | 2018-10-04 |

Решение:

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT a.user_id) retained_users 
 FROM 
    logins a 
 JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                             interval '1 month'
 GROUP BY 
    date_trunc('month', a.date)

Альтернативное решение:

WITH DistinctMonthlyUsers AS (
  /*
  * Для каждого месяца определяем *набор* пользователей, которые
  * выполнили авторизацию
  */
    SELECT DISTINCT
      DATE_TRUNC('MONTH', a.date) AS month_timestamp,
      user_id
    FROM logins
  )

SELECT
  CurrentMonth.month_timestamp month_timestamp,
  COUNT(PriorMonth.user_id) AS retained_user_count
FROM 
    DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN 
    DistinctMonthlyUsers AS PriorMonth
  ON
    CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
    AND 
    CurrentMonth.user_id = PriorMonth.user_id

Часть 2

Решение:

SELECT 
    DATE_TRUNC('month', a.date) month_timestamp, 
    COUNT(DISTINCT b.user_id) churned_users 
FROM 
    logins a 
FULL OUTER JOIN 
    logins b ON a.user_id = b.user_id 
        AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + 
                                         interval '1 month'
WHERE 
    a.user_id IS NULL 
GROUP BY 
    DATE_TRUNC('month', a.date)

Обратите внимание, что эту проблему можно решить также с помощью соединений LEFT или RIGHT.

Часть 3

| user_id | month_date |
|---------|------------|
| 1       | 2018-05-01 |
| 234     | 2018-05-01 |
| 3       | 2018-05-01 |
| 12      | 2018-05-01 |
| ...     | ...        |
| 234     | 2018-10-01 |

Решение:

WITH user_login_data AS 
(
    SELECT 
        DATE_TRUNC('month', a.date) month_timestamp,
        a.user_id,
        /* 
        * По крайней мере, в тех вариантах SQL, что я использовал, 
        * не нужно включать в инструкцию SELECT колонки из HAVING.
        * Я здесь выписал их для большей ясности.  
        */ 
        MAX(b.month_date) as most_recent_churn, 
        MAX(DATE_TRUNC('month', c.date)) as most_recent_active 
     FROM 
        logins a
     JOIN 
        user_churns b 
            ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date 
     JOIN
        logins c 
            ON a.user_id = c.user_id 
            AND 
            DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
     WHERE 
        DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
     GROUP BY 
        DATE_TRUNC('month', a.date),
        a.user_id
     HAVING 
        most_recent_churn > most_recent_active

№ 4. Нарастающий итог

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Моделирование денежных потоков в SQL».

Контекст: допустим, у нас есть таблица transactions в таком виде:

| date       | cash_flow |
|------------|-----------|
| 2018-01-01 | -1000     |
| 2018-01-02 | -100      |
| 2018-01-03 | 50        |
| ...        | ...       |

Где cash_flow — это выручка минус затраты за каждый день.

| date       | cumulative_cf |
|------------|---------------|
| 2018-01-01 | -1000         |
| 2018-01-02 | -1100         |
| 2018-01-03 | -1050         |
| ...        | ...           |

Решение:

SELECT 
    a.date date, 
    SUM(b.cash_flow) as cumulative_cf 
FROM
    transactions a
JOIN b 
    transactions b ON a.date >= b.date 
GROUP BY 
    a.date 
ORDER BY 
    date ASC

Альтернативное решение с использованием оконной функции (более эффективное!):

SELECT 
    date, 
    SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf 
FROM
    transactions 
ORDER BY 
    date ASC

№ 5. Скользящее среднее

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Скользящие средние в MySQL и SQL Server».

Контекст: допустим, у нас есть таблица signups в таком виде:

| date       | sign_ups |
|------------|----------|
| 2018-01-01 | 10       |
| 2018-01-02 | 20       |
| 2018-01-03 | 50       |
| ...        | ...      |
| 2018-10-01 | 35       |

Задача: написать запрос, чтобы получить 7-дневное скользящее среднее ежедневных регистраций.

Решение:

SELECT 
  a.date, 
  AVG(b.sign_ups) average_sign_ups 
FROM 
  signups a 
JOIN 
  signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY 
  a.date

№ 6. Несколько условий соединения

| id | subject  | from         | to           | timestamp           |
|----|----------|--------------|--------------|---------------------|
| 1  | Yosemite | [email protected]   | [email protected] | 2018-01-02 12:45:03 |
| 2  | Big Sur  | [email protected]  | [email protected] | 2018-01-02 16:30:01 |
| 3  | Yosemite | [email protected] | [email protected]   | 2018-01-02 16:35:04 |
| 4  | Running  | [email protected]   | [email protected]   | 2018-01-03 08:12:45 |
| 5  | Yosemite | [email protected]   | [email protected] | 2018-01-03 14:02:01 |
| 6  | Yosemite | [email protected] | [email protected]   | 2018-01-03 15:01:05 |
| .. | ..       | ..           | ..           | ..                  |

Решение:

SELECT 
    a.id, 
    MIN(b.timestamp) - a.timestamp as time_to_respond 
FROM 
    emails a 
JOIN
    emails b 
        ON 
            b.subject = a.subject 
        AND 
            a.to = b.from
        AND 
            a.from = b.to 
        AND 
            a.timestamp < b.timestamp 
 WHERE 
    a.to = '[email protected]' 
 GROUP BY 
    a.id

№ 1. Найти идентификатор с максимальным значением

  depname  | empno | salary |     
-----------+-------+--------+
 develop   |    11 |   5200 | 
 develop   |     7 |   4200 | 
 develop   |     9 |   4500 | 
 develop   |     8 |   6000 | 
 develop   |    10 |   5200 | 
 personnel |     5 |   3500 | 
 personnel |     2 |   3900 | 
 sales     |     3 |   4800 | 
 sales     |     1 |   5000 | 
 sales     |     4 |   4800 |

Решение:

WITH max_salary AS (
    SELECT 
        MAX(salary) max_salary
    FROM 
        salaries
    )
SELECT 
    s.empno
FROM 
    salaries s
JOIN 
    max_salary ms ON s.salary = ms.max_salary

Альтернативное решение с использованием RANK():

WITH sal_rank AS 
  (SELECT 
    empno, 
    RANK() OVER(ORDER BY salary DESC) rnk
  FROM 
    salaries)
SELECT 
  empno
FROM
  sal_rank
WHERE 
  rnk = 1;

№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)

Часть 1

Контекст: допустим, у нас есть таблица salaries в таком формате:

  depname  | empno | salary |     
-----------+-------+--------+
 develop   |    11 |   5200 | 
 develop   |     7 |   4200 | 
 develop   |     9 |   4500 | 
 develop   |     8 |   6000 | 
 develop   |    10 |   5200 | 
 personnel |     5 |   3500 | 
 personnel |     2 |   3900 | 
 sales     |     3 |   4800 | 
 sales     |     1 |   5000 | 
 sales     |     4 |   4800 |

  depname  | empno | salary | avg_salary |     
-----------+-------+--------+------------+
 develop   |    11 |   5200 |       5020 |
 develop   |     7 |   4200 |       5020 | 
 develop   |     9 |   4500 |       5020 |
 develop   |     8 |   6000 |       5020 | 
 develop   |    10 |   5200 |       5020 | 
 personnel |     5 |   3500 |       3700 |
 personnel |     2 |   3900 |       3700 |
 sales     |     3 |   4800 |       4867 | 
 sales     |     1 |   5000 |       4867 | 
 sales     |     4 |   4800 |       4867 |

Решение:

SELECT 
    *, 
    /*
    * AVG() is a Postgres command, but other SQL flavors like BigQuery use 
    * AVERAGE()
    */ 
    ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
    salaries

Часть 2

  depname  | empno | salary | salary_rank |     
-----------+-------+--------+-------------+
 develop   |    11 |   5200 |           2 |
 develop   |     7 |   4200 |           5 | 
 develop   |     9 |   4500 |           4 |
 develop   |     8 |   6000 |           1 | 
 develop   |    10 |   5200 |           2 | 
 personnel |     5 |   3500 |           2 |
 personnel |     2 |   3900 |           1 |
 sales     |     3 |   4800 |           2 | 
 sales     |     1 |   5000 |           1 | 
 sales     |     4 |   4800 |           2 |

Решение:

SELECT 
    *, 
    RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
 FROM  
    salaries

№ 1. Гистограммы

| session_id | length_seconds |
|------------|----------------|
| 1          | 23             |
| 2          | 453            |
| 3          | 27             |
| ..         | ..             |

| bucket  | count |
|---------|-------|
| 20-25   | 2     |
| 450-455 | 1     |

Максимальная оценка засчитывается за надлежащие метки строк («5-10» и т. д.)

Решение:

WITH bin_label AS 
(SELECT 
    session_id, 
    FLOOR(length_seconds/5) as bin_label 
 FROM
    sessions 
 )
 SELECT 
    CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket, 
    COUNT(DISTINCT session_id) count 
 GROUP BY 
    bin_label
 ORDER BY 
    bin_label ASC

№ 2. Перекрёстное соединение (несколько частей)

Часть 1

| state | total_streams |
|-------|---------------|
| NC    | 34569         |
| SC    | 33999         |
| CA    | 98324         |
| MA    | 19345         |
| ..    | ..            |

| state_a | state_b |
|---------|---------|
| NC      | SC      |
| SC      | NC      |

Решение:

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a
 CROSS JOIN 
    state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state

Для информации, перекрёстные соединения также можно писать без явного указания соединения:

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state <> b.state

Часть 2

Примечание: этот скорее бонусный вопрос, чем реально важный шаблон SQL. Можете его пропустить!

Решение:

SELECT
    a.state as state_a, 
    b.state as state_b 
 FROM   
    state_streams a, state_streams b 
 WHERE 
    ABS(a.total_streams - b.total_streams) < 1000
    AND 
    a.state > b.state

№ 3. Продвинутые расчёты

Благодарность: эта задача адаптирована из обсуждения по вопросу, который я задал на StackOverflow (мой ник zthomas.nc).

| user | class |
|------|-------|
| 1    | a     |
| 1    | b     |
| 1    | b     |
| 2    | b     |
| 3    | a     |

Для нашего образца получится такой результат:

| class | count |
|-------|-------|
| a     | 1     |
| b     | 2     |

Решение:

WITH usr_b_sum AS 
(
    SELECT 
        user, 
        SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
    FROM 
        table
    GROUP BY 
        user
), 

usr_class_label AS 
(
    SELECT 
        user, 
        CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class 
    FROM 
        usr_b_sum
)

SELECT 
    class, 
    COUNT(DISTINCT user) count 
FROM
    usr_class_label
GROUP BY 
    class 
ORDER BY 
    class ASC

Альтернативное решение использует инструкции SELECT в операторах SELECT и UNION:

SELECT 
    "a" class,
    COUNT(DISTINCT user_id) - 
        (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count 
UNION
SELECT 
    "b" class,
    (SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

Хотя составление SQL-запросов — это не самое интересное в работе дата-сайентистов, хорошее понимание SQL чрезвычайно важно для того, кто хочет преуспеть в любом занятии, связанном с обработкой данных. Дело тут в том, что SQL — это не только SELECT, FROM и WHERE. Чем больше SQL-конструкций знает специалист — тем легче ему будет создавать запросы на получение из баз данных всего, что ему может понадобиться.

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

Изучение механизмов, которые выходят за пределы базового знания SQL.

Рассмотрение нескольких практических задач по работе с SQL.

В статье рассмотрено 5 вопросов по SQL, взятых с Leetcode. Они представляют собой практические задачи, которые часто встречаются на собеседованиях.

Вопрос №1: второе место по зарплате

Напишите SQL-запрос для получения из таблицы со сведениями о заработной плате сотрудников (Employee) записи, содержащей вторую по размеру заработную плату.

Например, такой запрос, выполненный для таблицы, представленной ниже, должен вернуть 200. Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

▍Решение А: использование `IFNULL` и `OFFSET`

Вот основные механизмы, которые будут использованы в данном варианте решения задачи:

IFNULL(expression, alt): эта функция возвращает свой аргумент expression в том случае, если он не равен null. В противном случае возвращается аргумент alt. Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.

OFFSET: этот оператор используется с выражением ORDER BY для того чтобы отбросить первые n строк. Это нам пригодится по той причине, что нас интересует вторая строка результата (то есть — вторая по величине зарплата, данные о которой есть в таблице).

Вот — готовый запрос:

SELECT
    IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
        ), null) as SecondHighestSalary
FROM Employee
LIMIT 1

▍Решение B: использование `MAX`

В запросе, представленном ниже, используется функция MAX. Здесь выбирается самое большое значение заработной платы, не равное максимальной заработной плате, полученной по всей таблице. В результате мы и получаем то, что нам нужно — вторую по величине заработную плату.

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)

Вопрос №2: дублирующиеся адреса электронной почты

Напишите SQL-запрос, который обнаружит в таблице Person все дублирующиеся адреса электронной почты.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | [email protected] |
| 2  | [email protected] |
| 3  | [email protected] |
+----+---------+

▍Решение А: `COUNT` в подзапросе

Сначала мы создаём подзапрос, в котором выясняется частота появления каждого адреса в таблице. Затем результат, возвращаемый подзапросом, фильтруется с использованием инструкции WHERE count > 1. Запрос вернёт сведения об адресах, встречающихся в исходной таблице больше одного раза.

SELECT Email
FROM (
    SELECT Email, count(Email) AS count
    FROM Person
    GROUP BY Email
) as email_count
WHERE count > 1

▍Решение B: выражение `HAVING`

HAVING: это выражение, которое позволяет использовать инструкцию WHERE вместе с выражением GROUP BY.

SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1

Вопрос №3: растущая температура

Напишите SQL-запрос, который находит в таблице Weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

▍Решение: `DATEDIFF`

DATEDIFF: эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.

Если сформулировать обычным языком следующий запрос, то окажется, что он выражает следующую идею: нужно выбрать такие идентификаторы, чтобы температура, соответствующая представляемым ими датам, была бы больше, чем температура на «вчерашние» по отношению к ним даты.

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

Вопрос №4: самая высокая зарплата в подразделении

В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе (Id) сотрудника, о его имени (Name), о зарплате (Salary) и о подразделении компании, где он работает (Department).

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

В таблице Department содержатся сведения о подразделениях компании.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Напишите SQL-запрос, который находит в каждом из подразделений сотрудников с максимальной заработной платой. Например, для вышеприведённых таблиц подобный запрос должен возвращать результаты, представленные следующей таблицей (при этом порядок строк в таблице значения не имеет):

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

▍Решение: команда `IN`

Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR. Например, две следующие конструкции идентичны:

WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).

Здесь мы хотим получить таблицу, содержащую название подразделения (Department), имя сотрудника (Employee) и его заработную плату (Salary). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения (DepartmentID) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary) 
    IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
 )

Вопрос №5: пересаживание учеников

Мэри — учительница в средней школе. У неё есть таблица seat, хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.

Вот таблица исходного размещения учеников:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

Вот что должно получиться после пересаживания соседних учеников:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

Напишите запрос, который позволит учительнице решить вышеописанную задачу.

Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.

▍Решение: использование оператора `WHEN`

SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.

В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).

SELECT 
    CASE 
        WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
        WHEN id%2 = 1 THEN id + 1
        ELSE id - 1
    END AS id, student
FROM seat
ORDER BY id

Итоги

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

P.S. В нашем маркетплейсе есть Docker-образ с SQL Server Express, который устанавливается в один клик. Вы можете проверить работу контейнеров на VPS. Всем новым клиентам бесплатно предоставляются 3 дня для тестирования.

Уважаемые читатели! Что вы можете посоветовать тем, кто хочет освоить искусство создания SQL-запросов?

Топ вопросов для собеседование на позицию Разработчик SQL Server

Прошлая статья «Вопросы для Администраторов» получилась очень удачной и мы решили сделать новую, но на этот раз для Разработчиков SQL Server. Мы не будем разбирать все ответы, а только расскажем вам что следует изучить и какие бывают вопросы на собеседованиях.

Начнём с базовых вопросов для разработчика

Агрегатные функции
Индексы: виды, как организованы, различия
Уровни изоляции транзакций (спрашивают 9 из 10 раз)
Типы логических соединений (Inner Join, Left Join, Right Join и тд.)
Триггеры
Логический порядок обработки инструкции SELECT
Null и как с ним работать
Try/Catch
Работа с SQL Profiler (старое направление, но многие спрашивают) и Extended Events
Обобщённое табличное выражение (СTE)
Как написать динамический SQL (exec, sp_executesql)
Нормализация и денормализация БД
Хинты. Чем могут помочь. Риски
Курсоры.

Так же очень полезно знать

Типы физических соединений (Merge Join, Hash, Nested Loop. Как работает, чем отличается)
Оконные функции
План запроса (Seek, Scan, Spool, Key Lookup и тд.)
Секционирование. В каких случаях и для чего применяется. Как физически выглядит. Какие при этом бывают индексы
Временная таблица и табличные переменные
Понимание механизма распределенных транзакций
Служба сервис-брокер и основные понятия
Полнотекстовый поиск
Виды репликаций, их особенности и сферы применения
Как работает оптимизатор
SQLOS. Что такое и как устроено

А теперь перейдём к конкретным вопросам

Наверняка попросят написать или оптимизировать какой то запрос, спросят как-что лучше спроектировать и почему
Нужно быстро удалить записи из таблицы. Как это сделать быстро?
Удаление дубликатов из таблицы (несколько способов)
Чем отличается count(*) от count(name)?
Чем отличается decimal от float, для каких задач лучше применять каждый из этих типов?
Нарастающие итоги
Задачи типа gaps and islands
Что делать, если у вас случилось переполнение столбца int, а вам нужно быстро продолжить работу?
Дана таблица:
CREATE TABLE dbo.call
(
subscriber_name VARCHAR(64) NOT NULL,
event_date DATETIME NOT NULL,
event_cnt INT NOT NULL
)

CREATE TABLE dbo.call
(
subscriber_name VARCHAR(64) NOT NULL,
event_date DATETIME NOT NULL,
event_cnt INT NOT NULL
)

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

Как бы вы оптимизировали следующий запрос (показан полный скрипт таблицы; приведите обоснование своего выбора)?

CREATE TABLE dbo.call
(
id INT IDENTITY PRIMARY KEY CLUSTERED,
subscriber_name VARCHAR(64) NOT NULL,
event_date DATETIME NOT NULL,
subtype VARCHAR(32) NOT NULL,
type VARCHAR(128) NOT NULL,
event_cnt INT NOT NULL
)

SELECT *
FROM dbo.call
WHERE subscriber_name = @a
AND event_date > @b
AND subtype = @c

CREATE TABLE dbo.call

(

id INT IDENTITY PRIMARY KEY CLUSTERED,

subscriber_name VARCHAR(64) NOT NULL,

event_date DATETIME NOT NULL,

subtype VARCHAR(32) NOT NULL,

type VARCHAR(128) NOT NULL,

event_cnt INT NOT NULL

)

SELECT *

FROM dbo.call

WHERE subscriber_name = @a

AND event_date > @b

AND subtype = @c

Из таблицы следующей структуры:

CREATE partition FUNCTION pf_monthly(datetime) AS range RIGHT FOR VALUES (
‘20120201’, ‘20120301’, ‘20120401’, ‘20120501’, ‘20120601’, ‘20120701’,
‘20120801’, ‘20120901’, ‘20121001’, ‘20121101’, ‘20121201’)

go

CREATE partition scheme ps_monthly AS partition pf_monthly ALL TO ([primary])

go

CREATE TABLE dbo.order_detail
(
order_id INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
purchase_date DATETIME NOT NULL,
amount MONEY NOT NULL
)
ON ps_monthly(purchase_date)

go

CREATE CLUSTERED INDEX ix_purchase_date
ON dbo.order_detail(purchase_date)

go

CREATE partition FUNCTION pf_monthly(datetime) AS range RIGHT FOR VALUES (

‘20120201’, ‘20120301’, ‘20120401’, ‘20120501’, ‘20120601’, ‘20120701’,

‘20120801’, ‘20120901’, ‘20121001’, ‘20121101’, ‘20121201’)

CREATE partition scheme ps_monthly AS partition pf_monthly ALL TO ([primary])

CREATE TABLE dbo.order_detail

(

order_id INT NOT NULL,

product_id INT NOT NULL,

customer_id INT NOT NULL,

purchase_date DATETIME NOT NULL,

amount MONEY NOT NULL

)

ON ps_monthly(purchase_date)

CREATE CLUSTERED INDEX ix_purchase_date

ON dbo.order_detail(purchase_date)

Необходимо удалить случайно внесенные данные по клиенту с id 42, за период с мая по июнь (включительно) 2012-го года, что составляет более 80% записей за этот период. В таблице несколько миллиардов записей. Какие есть способы решения данной задачи?

Система успешно работала полгода, затем неожиданно производительность серьезно деградировала. Каковы возможные проблемы, пути решения?
Какое отличие(я) между delete from dbo.my_table и truncate table?

Facebook

Twitter

Вконтакте

Google+

109 популярных вопросов на собеседовании в сфере Data science

Подготовка к интервью – часто непростая задача. Вне зависимости от своего опыта работы и знаний в технической области, кандидат часто бывает застигнут врасплох вопросами работодателя. Во время интервью в сфере Data Science круг вопросов, которые могут быть заданы, охватывает мат. статистику, программирование и создание сложных моделей. Также соискатель должен продемонстрировать свои коммуникативные способности. Все это значит, что к интервью нужно готовиться.

Портал Spring Board подготовил перечень вопросов, к которым должен быть готов соискатель на должность data science специалиста. Этот список поможет понять, чего следует ждать. Возможные вопросы интервью поделены на шесть тематических категорий: статистику, программирование, моделирование, прошлый опыт, культуру и решение проблем. Представляем вашему внимание адаптированную версию этого материала.

Статистика

Статистические вычисления — это процесс, с помощью которого Data-специалисты анализируют данные, делают прогнозы и создают модели.

Без глубокого знания статистики сложно быть хорошим специалистом, поэтому грамотный интервьюер наверняка задаст кандидату несколько вопросов на понимание ее основ.

Вот несколько примеров элементарных вопросов по статистике:

Существует четыре основных допущения: 1. Есть линейная зависимость между зависимой переменной и регрессорами, то есть модель, которую вы создаете, соответствует имеющимся данным. 2. Ошибки или остатки данных обычно распределяются и независимы друг от друга, 3. Существует минимальная мультиколлинеарность между объясняющими переменными, 4. Гомоскедастичность. (Означает, что дисперсия вокруг линии регрессии одинакова для всех значений предикторной переменной).

Что такое статистическое взаимодействие? Ответ

Что такое смещение выбора?

Какие примеры набора данных с негауссовым распределением вы можете привести?

Что такое биномиальная формула вероятности?

Программирование

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

Общие вопросы программирования

С какими языками программирования вам удобнее всего работать?

Какие плюсы и минусы есть у вашего любимого статистического программного обеспечения?

Расскажите об алгоритме, который вы создали.

Опишите Data science проект, над которым вы работали продолжительное время. Что вы узнали из этого опыта?

Работаете ли вы над проектами с открытым исходным кодом?

Как бы вы очистили dataset в (язык программирования)?

Расскажите о коде, который вы использовали во время своего последнего проекта?

Вопросы по Big Data

Назовите два основных компонента структуры Hadoop.

Объясните наиболее простым способом принцип работы MapReduce. Ответ

Как бы вы отсортировали большой массив чисел?

Перед вами — большой набор данных. Как вы будете работать с выбросами? Как поступите с недостающими значениями? Что скажете о преобразованиях?

Вопросы по Python

Какие модули / библиотеки вы используете? Что вам нравится/ не нравится в них?

Каковы поддерживаемые типы данных есть в Python?

В чем разница между кортежем и списком в Python?

Чтобы разобраться с дополнительными вопросами по Python, стоит ознакомиться с этим полезным ресурсом, созданным Toptal.

Вопросы по R

Каковы различные типы алгоритмов сортировки, доступные на языке R? Существуют алгоритмы сортировки вставкой, пузырьком и выбором.

Каковы различные объекты данных в R?

Какие пакеты вы используете? Что вам нравится/не нравится в них?

Как получить доступ к элементу во втором столбце и четвертой строке матрицы с именем M?

Какая команда используется для хранения объектов R в файле?

Каков наилучший способ совместного использования Handoop и R для анализа?

Как вы разделите непрерывную переменную на разные группы/ранги в R?

Напишите функцию на R-языке, чтобы заменить отсутствующее значение в векторе средним значением этого вектора.

Вопросы по SQL

Часто вопросы по SQL ориентированы на решение конкретных задач.То есть работодатель попросит кандидата решить определенную проблему, чтобы сделать выводы о его практических знаниях.

Например, соискателя могут попросить отфильтровать/сжать/систематизировать данные из таблицы, а потом сделать выводы. При наличии неуверенности в данном вопросе есть смысл воспользоваться информацией ресурса Mode Analytics с отличным введением в SQL.

Какова цель групповых функций в SQL? Приведите примеры групповых функций.

Групповые функции необходимы для получения суммарной статистики данных. COUNT, MAX, MIN, AVG, SUM и DISTINCT — это все групповые функции.

Объясните разницу между внутренним джоином, джоином слева / справа и UNION. Ответ

Что делает UNION? В чем заключается разница между UNION и UNION ALL?

В чем заключается разница между SQL, MySQL и SQL Server?

Если таблица содержит повторяющиеся строки, возвращает ли результат запроса двойные значения по умолчанию? Как вы можете удалить повторяющиеся строки из результата запроса?

Для дополнительной информации по SQL ознакомьтесь с этим ресурсом, созданным Toptal.

Моделирование

Превращать данные в прогнозы и действительно актуальную информацию так же непросто, как и говорить о моделировании с работодателем. Вопросы, представленные ниже, задаются кандидатам, чтобы те могли описать свой предыдущий опыт, возникшие сложности и их преодоление. Если соискатель не сможет ответить на теоретические вопросы и сделать выводы из своего прошлого опыта, то это вряд ли произведет положительное впечатление на работодателя. Не все приведенные ниже вопросы обязательно встретятся в ходе интервью, их основная цель — освежить в памяти кандидата его прошлый опыт моделирования.

Расскажите о том, как вы разработали модель для прошлого работодателя или клиента.

Каковы ваши любимые методы визуализации данных?

Как бы вы могли наиболее эффективно представить данные с пятью измерениями?

Чем kNN отличается от кластеризации k-средних?

kNN, или k-ближайших соседей — это алгоритм классификации, где k является целым числом, которое описывает количество соседних точек данных, которые влияют на представленную классификацию. K-средство — это алгоритм кластеризации, где k — это целое число, описывающее количество кластеров, возникающих из представленных данных. Все они выполняют разные задачи.

Как бы выглядела ваша модель логистической регрессии?

Вы использовали модель временного ряда? Вы понимаете кросс-корреляцию с временными задержками?

Объясните правило 80/20 и расскажите мне о его важности при проверке модели.

Объясните, что такое точность и отзыв. Как они относятся к ROC-кривой?

Отзыв описывает, какой процент истинных положительных определен описан в качестве положительных моделью. Точность описывает, какой процент положительных прогнозов был правильным. Кривая ROC показывает взаимосвязь между отзывом модели и её спецификой. Отзыв, точность и ROC являются мерами, определяющими эффективность представленной классификационной модели.

Объясните разницу между методами регуляризации L1 и L2.

Что такое анализ основных причин?

Что такое коллизия хеш-таблиц?

Что такое точный тест?

На ваш взгляд, что более важно при разработке модели машинного обучения: производительность модели или её точность? Один из подходов к данному вопросу

Как вы будете обрабатывать несбалансированные данные, использованные для генерирования прогнозов? (отрицательных классов больше, чем положительных).

Как бы вы провели валидацию модели, созданной для количественного анализа при помощи множественной регрессии? Есть две модели сопоставимой точности и вычислительной производительности. Какую лучше выбрать для прода почему?

Как вы действуете с разреженностью?

Лучше потратить 5 дней на разработку на 90% точного решения или же 10 дней на 100% точность?

В каких ситуациях общая линейная модель неудачна?

Как вы думаете, что 50 маленьких деревьев принятия решений лучше одного большого или нет? Почему?

Изменяя алгоритм, как вы понимаете, что предпринимаемые действия лучше других?

Какие ошибки предпочтительней: первого или второго рода?

Прошлый опыт

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

Соискатель может услышать вопросы, касающиеся следующих тем:

Работа в команде

Руководство

Управление конфликтами

Решение проблем

Неудачи

Перед интервью стоит записать примеры, связанные с этими темами. Когда кандидата спрашивают о предыдущем опыте, лучше обязательно рассказать историю и в то же время быть кратким и логичным.

Примеры таких вопросов:

Расскажите о том рабочем периоде, когда вы взяли инициативу в свои руки.

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

Расскажите о том, когда вы разрешили конфликт.

Расскажите о своем провале и о выводах, сделанных впоследствии.

Расскажите о вашем прошлом опыте работы. Почему вы решили заниматься именно этим?

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

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

Что вы делали в прошлом, чтобы ваш клиент был удовлетворен результатами работы?

Какими своими достижениями на своей предыдущей работе вы гордитесь?

Каким образом вам удается поддерживать баланс между работой и личной жизнью?

Соответствие корпоративное культуре

В ходе интервью работодатель также хочет понять, насколько кандидат «соответствует» духу компании, почему он заинтересовался data science в целом, и предложенной вакансией — в частности. Следующие вопросы помогут кандидату не оказаться застигнутым врасплох, но самое главное при ответе на них — это честность. Правильного ответа не существует, однако лучший ответ — это тот, который сказан с уверенностью и улыбкой.

Какими data-специалистами вы больше всего восхищаетесь? Какими стартапами?

Как вы думаете, что характеризует хорошего data-специалиста?

Как вы заинтересовались data science?

Приведите несколько примеров «лучших практик» в data science.

Какую последнюю книгу о работе с данными вы прочитали? В каких мероприятиях по изучению данных вы в последний раз участвовали? Если вы давно ничего не читали ничего стоящего по data science, то у Springboard есть отличная подборка книг, которые заслуживают внимания!

Над каким проектом вы бы хотели поработать в нашей компании?

Какие уникальные навыки вы бы принесли команде?

Какой областью данных вы бы хотели заниматься при отсутствии ограничений?

Вы когда-нибудь задумывались о создании стартапа? Какая концепция/идея вас интересует?

О каких ваших хобби нет ни слова в резюме?

Каковы ваши прогнозы на следующие 20 лет?

Что вы делали сегодня/на этой неделе/на прошлой неделе?

Как бы вы поступили, если бы выиграли миллион долларов в лотерее?

Во что верите вы, в отличие от большинства людей?

Какие “пробивные” черты характера у вас есть?

Чем вы увлекаетесь?

Навыки решения проблем

В какой-то момент работодатель захочет протестировать способности кандидата в решении нестандартных задач. Например, соискателя в Yelp могут спросить, как о будет создать систему, обнаруживающую поддельные отчеты Yelp. Главное — не бояться задавать вопросы.

Работодатель хочет оценить способности кандидата к критическому мышлению, именно поэтому спрашивать и уточнять — это отличная возможность продемонстрировать умение задавать правильные вопросы. Если для ответа на вопрос нужно создать схематичную диаграмму или написать что-то на доске, соискателю стоит воспользоваться этой возможностью.

Какие решения вы можете предложить для выявления плагиата?

Сколько «полезных» голосов получит отчет Yelp?

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

Вы собираетесь отправить миллион электронных писем. Как оптимизировать доставку? Как вы оптимизируете ответ?

У вас есть набор данных, содержащий 100 тысяч строк и 100 столбцов, причем один из этих столбцов является нашей зависимой переменной для решаемой проблемы. Как быстро определить, какие столбцы будут нужны для прогноза? Определите два метода и объясните их как пятилетнему ребенку.

Как вы обнаружите фиктивные отзывы или фиктивные аккаунты на Facebook?

Это возможность продемонстрировать свои знания алгоритмов машинного обучения; особенно, сентиментального анализа и алгоритмов анализа текста. Продемонстрируйте свои знания о “мошенническом” поведении: что его характеризует?

Как бы вы выполняли кластеризацию на миллион уникальных ключевых слов, при том, что у вас есть 10 миллионов точек данных — каждая из двух ключевых слов, и метрика, оценивающая, насколько похожи два этих ключевых слова? Как бы вы создали эту таблицу из 10 миллионов таблиц данных на первом месте?

Как бы вы оптимизировали поисковик для более быстрой работы, получения более качественной информации и лучшего суммирования данных?

Идеальной методике по подготовке к data science собеседованию не существует, однако с помощью данного руководства почувствовать себя уверенно станет гораздо проще.

Подпишитесь на рассылку новостей

IT рекрутмента и HR Tech

Top 50 SQL Interview Вопросы и ответы

Home
Testing
- - Back
  - Agile Testing
  - BugZilla
  - Cucumber
  - Database Testing
  - JMT Testing Назад
  - JUnit
  - LoadRunner
  - Ручное тестирование
  - Мобильное тестирование
  - Mantis
  - Почтальон
  - QTP
  - Назад
  - Центр качества (ALM)
  - SAP Testing
  - SAPU
  - Управление тестированием
  - TestLink
SAP
- - Назад
  - ABAP
  - APO
  - Начинающий
  - Basis
  - BODS
  - BI
  - BPC
  - CO
  - Назад
  - CRM
  - Crystal Reports
  - QM4000
  - Заработная плата
  - Назад
  - PI / PO
  - PP
  - SD
  - SAPUI5
  - Безопасность
  - Менеджер решений
  - Successfactors
  - Учебники SAP
- Обязательно учите!
  - - Назад
    - Бухгалтерский учет
    - Алгоритмы
    - Android
    - Блокчейн
    - Business Analyst
    - Создание веб-сайта
    - CCNA
    - Облачные вычисления
    - 00030003 COBOL
    - Big Data
      - Назад
        AWS
        BigData
        Cassandra
        Cognos
        Хранилище данных
        0003
        HBOps
        0003
        HBOps
        MicroStrategy
        MongoDB
    .
    базовых и расширенных вопросов для собеседования по MySQL с ответами
    MySQL RDBMS — самая популярная исходная база данных opne, используемая в основном с PHP и другими онлайн-приложениями. Вот несколько наиболее часто задаваемых сборников интервью о MySQL. Загрузите полные ответы на часто задаваемые вопросы по PHP в формате PDF. Если вы программист PHP или разработчик программного обеспечения в Интернете, вам следует хорошо изучить базовую СУБД (систему управления реляционными данными). Вопросы, которые вы можете ожидать от этой темы, составляют около
    - Создание, удаление, изменение баз данных и таблиц
    - Связь с базой данных
    - Первичные ключи, индексные ключи и внешние ключи
    - Как начать проектировать таблицы согласно требованию
    - Запрос выбора
    - Оптимизация запросов с левым и правым соединением
    - Функции агрегирования, такие как СУММ
    - Выбор уникальных строк
    - избегая повторяющихся строк
    - Искать в Mysql таблице
    - Технические характеристики и общие сведения о MySQL
    Большинство компаний работают с этими таблицами MySQL, и есть много шансов получить работу в этой области в качестве администратора базы данных и программиста.Пожалуйста, просмотрите все эти вопросы и ответы на них, прежде чем идти на собеседование.
    О MySQL
    MySQL — это система управления реляционными базами данных (СУБД) с открытым исходным кодом. Название образовано от комбинации «My», что означает имя дочери соучредителя Майкла Видениуса, и «SQL», что означает язык структурированных запросов.
    MySQL принадлежит Oracle, хотя первоначально принадлежала и спонсировалась единственной коммерческой фирмой, шведской компанией MYSQLAB.Проект разработки MYSQL делает его исходный код доступным в соответствии с условиями GNU General Public License, включая ряд соглашений о правах собственности. Вы можете получить различные платные версии с дополнительными функциями для правильного использования.
    MySQL интервью вопросы и ответы
    Написание экзамена MySQL может быть немного сложным. Но если вы готовитесь к собеседованию, вот несколько возможных вопросов, которые могут вам задать, и их ответы.
    1. Что такое MySQL?
    Отв.
    MySQL — это СУБД с открытым исходным кодом, которая создается, поддерживается и распространяется MySQL AB. Сейчас он принадлежит Oracle.
    1. Почему мы используем MySQL?
    Отв.
    Сервер базы данных
    MySQL надежен, быстр и удобен в использовании. Программное обеспечение можно загрузить из Интернета и как бесплатное ПО.
    1. Какой порт по умолчанию для MySQL Server?
    Отв.
    Порт по умолчанию для сервера MySQL — 3306
    1. Что такое REGEXP?
    Отв.
    REGEXP — это совпадение с шаблоном, в котором соответствует шаблон в любом месте поискового значения
    1. Что делает TIMESTAMP для типа данных UPDATE CURRENT_TIMESTAMP?
    Отв.
    Когда таблица создается, ноль используется для обновления столбца TIMESTAMP. Модификатор UPDATE CURRENT_TIMESTAMP обновляет поле отметки времени до текущего времени всякий раз, когда изменяются другие поля таблицы.
    1. Что делает myisamchk?
    Отв.
    Он используется для сжатия таблиц MyISAM, уменьшая тем самым использование ими диска или памяти.
    1. Что вы понимаете под объединенными таблицами?
    Отв.
    Они позволяют вам иметь доступ к таблицам, которые находятся в других базах данных на других серверах.
    1. Как узнать автоинкремент, назначенный последней вставке?
    Отв.
    LAST_INSERT_ID вернет последнее значение, присвоенное автоматическим приращением, и указывать имя таблицы не обязательно.
    1. Как войти в MySQL с помощью оболочки Unix?
    Отв.
    Для входа мы можем использовать эту команду:
    # [каталог mysql] / mysql –h имя хоста –u <Имя пользователя> -p <пароль>
    1. Различия между CHAR_LENGTH и LENGTH
    Отв.
    В то время как CHAR_LENGTH — это количество символов, LENGTH — это количество байтов. Цифры разные для Unicode и других кодировок, но такие же для латинских символов.
    1. Как можно увидеть все индексы, определенные для таблицы?
    Отв.
    Вы можете определить индексы для таблицы по:
    ПОКАЗАТЬ ИНДЕКС ИЗ <имя таблицы>;
    1. Что такое операторы сравнения столбцов?
    Отв.
    Операторы =, <>, <=, <,> =,>, <<, >>, <=>, AND, OR или LIKE используются при сравнении столбцов в операторах SELECT
    1. Что такое InnoDB?
    Отв.
    InnoDB — это механизм безопасного хранения транзакций, разработанный Inno Oy, ныне Oracle Corporation.
    1. Сколько столбцов вы можете использовать при создании индекса?
    Отв.
    Для любой стандартной таблицы можно создать не более 16 индексированных столбцов
    1. Что вы подразумеваете под% и _ в заявлении LIKE?
    Отв.
    % соответствует 0 или более символам.Но _ — это ровно один символ в операторе LIKE.
    .
    MySQL вопросов на собеседовании
    Что такое MySQL?
    MySQL — это система управления реляционными базами данных с открытым исходным кодом, основанная на SQL . Он очень популярен среди пользователей, так как легко работает на нескольких платформах, таких как Windows, UNIX и LINUX .
    Он широко используется для разработки веб-приложений и публикаций в Интернете. Он работает на клиент-серверной модели и является частью корпоративного стека LAMP .
    Как сбросить пароль root MySQL?
    Пароль root MySQL можно установить, выполнив следующие действия:
    - Войдите как администратор.
    - Остановите сервер MySQL с помощью инструментов администрирования на панели управления.
    - Создайте текстовый файл и поместите инструкцию SQL для сброса пароля root. Замените существующий пароль новым паролем.
    - Сохраните файл и снова запустите MySQL из командной строки.
    - MySQL должен быть запущен с использованием параметра —init-file , где содержимое этого файла будет адресом файла измененных паролей.
    - После запуска Сервера удалите измененный файл пароля .
    Как проверить, работает ли MySQL?
    Следующие команды могут быть выполнены, чтобы проверить, запущен ли MySQL или нет:
    - C:> «C: Program FilesMySQLMySQL Server 5.7binmysql «тест
    - C:> «C: Program FilesMySQLMySQL Server 5.7binmysqlshow» -u root MySQL
    В случае, если пароль root был сброшен или был создан новый пользователь, необходимо использовать соответствующие параметры -u и –p вместе с приведенными выше командами.
    Разница между предложениями Have и Where в MySQL?
    Различия между оператором Where и Have a в MySQL:
    - Где используется для выборки требуемых строк из таблицы на основе условия, но Имеет используется для фильтрации сгруппированных
    - Где используется перед предложением Group By, но предложение Have используется после команды Group By.
    Как открыть командную строку MySQL?
    Командную строку MySQL можно открыть с помощью следующей команды:
    Для запуска mysqld в командной строке введите C:> «C: Program FilesMySQLMySQL Server 8.0binmysqld»
    Здесь путь к MySQL может варьироваться в зависимости от расположения файла в системе.
    Какой порт использует MySQL?
    Порт TCP / IP, на котором работает MySQL , по умолчанию имеет номер порта 3306.
    Что такое InnoDB в MySQL?
    InnoDB в MySQL — это используемый по умолчанию механизм хранения. Он предоставляет таблиц, совместимых с ACID, поддерживает внешние ключи, откат, фиксацию и восстановление после сбоя в случае сбоя базы данных и потери данных.
    Механизм помогает сократить количество запросов ввода / вывода , которые выполняются с использованием первичных ключей. Он поддерживает производительность базы данных MySQL.
    Что такое varchar в MySQL?
    Varchar — это тип данных в MySQL , который не имеет фиксированной длины.Это строки переменной длины, которым можно присвоить значение от 0 до 65 535 .
    Значения Varchar хранятся в MySQL как 1-байтовый или 2-байтовый префикс длины вместе с фактическими данными. Этот префикс длины определяет количество байтов в значении.
    Что такое большой двоичный объект в MySQL?
    BLOB (большой двоичный объект) в MySQL может хранить различные объемы данных. Они бывают разных типов, а именно TINYBLOB, MEDIUMBLOB, LONGBLOB и BLOB .
    Эти типы похожи, но различаются объемом данных, которые они могут хранить. У них есть 4 типа текста, а именно TINYTEXT, MEDIUMTEXT, LONGTEXT и TEXT.
    Что такое MySQL pid-файл?
    Файл pid содержит путь к файлу, в который сервер записывает свой идентификатор процесса . Этот файл создается сервером в каталоге данных, если не указан другой путь к другому каталогу.
    Что такое сопоставление в MySQL?
    В MySQL сопоставление — это порядок следования в определенном наборе символов.По сути, это набор правил, который используется для сортировки и сравнения символьных строк. Каждое сопоставление связано с набором символов.
    Что такое разделитель MySQL?
    Разделитель в MySQL используется для определения функций, хранимых процедур и создания триггеров. Точка с запятой является разделителем по умолчанию. Это помогает клиенту MySQL понять, где заканчиваются процедуры и функции, и что процедуры могут выполняться как единое целое.
    Вы можете изменить разделители для разработки новых процедур.В случае нескольких операторов разные разделители, такие как // и $$ .
    Что такое индекс в MySQL?
    Index в MySQL — это структура данных, которая используется для увеличения скорости операций в таблице. Фактически это своего рода таблица, в которой существует первичный ключ и указатель на каждую запись в исходной таблице.
    Их можно создавать с использованием одного или нескольких столбцов для повышения эффективности доступа к записям и упрощения поиска.
    Что такое естественное соединение в MySQL?
    Естественное соединение используется для выполнения тех же задач, которые выполняются посредством Inner Join и Left Join , где используются предложения Using и On.
    В Natural Join столбцы с аналогичными именами связанных таблиц будут отображаться в результатах запроса только один раз. Но в связанных таблицах должны быть столбцы с одинаковыми именами.
    Что такое беззнаковое в MySQL?
    Тип данных без знака используется для предоставления только неотрицательных чисел для столбца, но без отрицательных чисел.Его можно применять, когда в столбце требуется большой числовой диапазон.
    Все целые числа могут иметь беззнаковый тип данных в качестве необязательного атрибута. Например, если столбец INT и сделан без знака, размер его диапазона останется прежним, но конечные точки изменятся с 0 до 4294967295 .
    Что представляют собой подготовленные операторы в MySQL?
    Подготовленный оператор используется для повышения скорости и эффективности операторов SQL путем многократного выполнения одних и тех же операторов.Он использует двоичный протокол клиент-сервер.
    Три оператора SQL должны выполняться вместе с подготовленным оператором:
    - Подготовить — подготавливает оператор перед его запуском.
    - Execute — оператор выполняется этой командой.
    - Deallocate Prepare — Отменяет выписку.
    Что такое auto_increment в MySQL?
    Ключевое слово AUTO_INCREMENT используется для автоматического увеличения в MySQL .Это работает с числовыми типами данных, которые увеличиваются каждый раз, когда новая запись добавляется в таблицу базы данных.
    Его начальное значение — 1, и оно будет увеличиваться на 1 каждый раз при вставке записи. Эта функция очень полезна для обеспечения уникальности первичного ключа. Ограничение без знака помещается вместе с автоматически увеличивающимся первичным ключом.
    Что такое привилегии сброса в MySQL?
    Если таблицы GRANT изменяются напрямую с помощью таких операторов, как Insert, Delete и Update , изменения не будут иметь никакого эффекта, если серверу не будет дана команда перезапустить или перезагрузить все таблицы.В этом случае используются привилегии сброса.
    Это можно сделать, выполнив команду mysqladmin flush привилегий .
    Как удалить столбец в MySQL?
    Столбцы могут быть удалены в MySQL с помощью оператора DROP . Будут использованы следующие шаги:
    - После предложения Alter Table укажите таблицу, в которой существует столбец, который необходимо удалить.
    - После предложения Drop Column укажите имя этого столбца.
    Что такое рабочая среда MySQL?
    MySQL Workbench — это инструмент, который используется для проектирования физических моделей данных и изменения баз данных с помощью передовых методов, таких как обратное проектирование. Он поддерживает все объекты, такие как представления, хранимые процедуры, таблицы , которые являются частью любой базы данных.
    .
    вопросов на собеседовании по MySQL — 2 ответа
    Переполнение стека
    Около
    Продукты
    Для команд
    Переполнение стека
    Общественные вопросы и ответы
    Переполнение стека для команд
    Где разработчики и технологи делятся частными знаниями с коллегами
    Вакансии
    Программирование и связанные с ним технические возможности карьерного роста
    Талант
    Нанимайте технических специалистов и создавайте свой бренд работодателя
    Реклама
    Обратитесь к разработчикам и технологам со всего мира
    .
    No related posts.

Апрель 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

Вопросы по mysql на собеседовании: ТОП-20 хитрых вопросов по SQL для собеседования

Лучшие вопросы средней сложности по SQL на собеседовании аналитика данных / Блог компании Дата-центр «Миран» / Хабр

№ 1. Процентное изменение месяц к месяцу

№ 2. Маркировка древовидной структуры

№ 3. Удержание пользователей в месяц (несколько частей)

Часть 1

Часть 2

Часть 3

№ 4. Нарастающий итог

№ 5. Скользящее среднее

№ 6. Несколько условий соединения

№ 1. Найти идентификатор с максимальным значением

№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)

Часть 1

Часть 2

№ 1. Гистограммы

№ 2. Перекрёстное соединение (несколько частей)

Часть 1

Часть 2

№ 3. Продвинутые расчёты

[Перевод] Топ-65 вопросов по SQL с собеседований, к которым вы должны подготовиться в 2019 году. Часть I — MAILSGUN.RU

Вопрос 1. В чем разница между операторами DELETE и TRUNCATE?

№ Вопрос 2. Из каких подмножеств состоит SQL?

Вопрос 3. Что подразумевается под СУБД? Какие существуют типы СУБД?

Вопрос 4. Что подразумевается под таблицей и полем в SВопросL?

Вопрос 5. Что такое объединения в SQL?

Вопрос 6. В чем разница между типом данных CHAR и VARCHAR2 в SQL?

Вопрос 7. Что такое первичный ключ (Primary key)?

Вопрос 8. Что такое ограничения (Constraints)?

Вопрос 9. В чем разница между SQL и MySQL?

Вопрос 10. Что такое уникальный ключ (Unique key)?

Вопрос 11. Что такое внешний ключ (Foreign key)?

Вопрос 12. Что подразумевается под целостностью данных?

Вопрос 13. В чем разница между кластеризованным и некластеризованным индексами в SQL?

Вопрос 14. Напишите SQL-запрос для отображения текущей даты.

Вопрос 15. Перечислите типы объединений.

Вопрос 16. Что вы подразумеваете под денормализацией?

Вопрос 17. Что такое сущности и отношения?

Вопрос 18. Что такое индекс?

Вопрос 19. Опишите различные типы индексов.

Вопрос 20. Что такое нормализация и каковы ее преимущества?

Вопрос 21. В чем разница между командами DROP и TRUNCATE?

Вопрос 22. Объясните различные типы нормализации.

Вопрос 23. Что такое свойство ACID в базе данных?

Вопрос 24. Что вы подразумеваете под «триггером» в SQL?

Вопрос 25. Какие операторы доступны в SQL?

Вопрос 26. Совпадают ли значения NULL со значениями нуля или пробела?

Вопрос 27. В чем разница между перекрестным (cross join) и естественным (natural join) объединением?

Вопрос 28. Что такое подзапрос в SQL?

Вопрос 29. Какие бывают типы подзапросов?

Вопрос 30. Перечислите способы получить количество записей в таблице?

Топ 10 вопросов для собеседования на позицию Администратор MS SQL

Топ 10 вопросов для собеседования на позицию Администратор MS SQL Server:

Дополнительные вопросы:

Вопросы от опытных администраторам вам

Лучшие вопросы средней сложности по SQL на собеседовании аналитика данных

№ 1. Процентное изменение месяц к месяцу

№ 2. Маркировка древовидной структуры

№ 3. Удержание пользователей в месяц (несколько частей)

Часть 1

Часть 2

Часть 3

№ 4. Нарастающий итог

№ 5. Скользящее среднее

№ 6. Несколько условий соединения

№ 1. Найти идентификатор с максимальным значением

№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)

Часть 1

Часть 2

№ 1. Гистограммы

№ 2. Перекрёстное соединение (несколько частей)

Часть 1

Часть 2

№ 3. Продвинутые расчёты

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

Вопрос №1: второе место по зарплате

▍Решение А: использование IFNULL и OFFSET

▍Решение B: использование MAX

Вопрос №2: дублирующиеся адреса электронной почты

▍Решение А: COUNT в подзапросе

▍Решение А: использование `IFNULL` и `OFFSET`

▍Решение B: использование `MAX`

▍Решение А: `COUNT` в подзапросе

▍Решение B: выражение `HAVING`

▍Решение: `DATEDIFF`

▍Решение: команда `IN`

▍Решение: использование оператора `WHEN`