🖥 Задание SQL с реального собеседования

Вводные данные
Есть таблица анализов Analysis:

an_id — ID анализа;
an_name — название анализа;
an_cost — себестоимость анализа;
an_price — розничная цена анализа;
an_group — группа анализов.
Есть таблица групп анализов Groups:

gr_id — ID группы;
gr_name — название группы;
gr_temp — температурный режим хранения.
Есть таблица заказов Orders:

ord_id — ID заказа;
ord_datetime — дата и время заказа;
ord_an — ID анализа.

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

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

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

Ответ

@sqlhub

🖥 Задание SQL с реального собеседования

Вводные данные
Есть таблица анализов Analysis:

an_id — ID анализа;
an_name — название анализа;
an_cost — себестоимость анализа;
an_price — розничная цена анализа;
an_group — группа анализов.
Есть таблица групп анализов Groups:

gr_id — ID группы;
gr_name — название группы;
gr_temp — температурный режим хранения.
Есть таблица заказов Orders:

ord_id — ID заказа;
ord_datetime — дата и время заказа;
ord_an — ID анализа.

Задача Уровень: Senior

В этой задаче мы будем работать с другой таблицей (да, она будет всего одна). Сам запрос в этой задаче не сложный, но для его написания необходимо как бы уметь «мыслить на SQL».

Рассмотрим таблицу балансов клиентов:

ClientBalance(client_id, client_name, client_balance_date, client_balance_value)

client_id — идентификатор клиента;
client_name — ФИО клиента;
client_balance_date — дата баланса клиента;
client_balance_value — значение баланса клиента.
Формулировка: в данной таблице в какой-то момент времени появились полные дубли. Предложите способ для избавления от них без создания новой таблицы.

Ответ

ставь ❤️ ,если нравится такой формат

@sqlhub

🖥 Вопросы на знание SQL

Вопрос 1. Уровень: Junior
Есть категория «хитрых» вопросов, которые особенно любят задавать Junior-специалистам. Хотя чего уж там, любым специалистам.

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

Вопрос: Как оператор GROUP BY обрабатывает поля с NULL?

Если Вы не знаете ответ на этот вопрос, то после прочтения ответа обязательно проверьте свои проекты — может у вас где-то закралась ошибка? 😉

Ответ
Учитывая, что NULL в SQL — просто отсутствие значения, то все значения NULL при группировке попадают в одну группу. Например, пусть есть таблица:

name | score
------|-------
Vasya | 5
Petya | 10
Petya | 3
Vasya | 4
NULL | 3
NULL | 8

Тогда запрос select sum(score) from table group by name даст:

name | score
------|-------
Vasya | 9
Petya | 13
NULL | 11

@sqlhub

🖥 Вопросы на знание SQL

Вопрос 2. Уровень Middle

Этот вопрос не такой хитрый, как предыдущий, а вполне себе конкретный. Однако, он требует знания оконных функций и их тонкостей, а это исконное Middle требование.

Вопрос: В чем отличие функции RANK() от DENSE_RANK()?

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

По аналогии с функцией ROW_NUMBER, оконные функции RANK и DENSE_RANK служат для нумерации строк. Однако, делают они это немного иначе: строки с одинаковым значениям получают одинаковый ранг. Для ряда задач это логично: если у двух сотрудников одинаковая зарплата, мы не можем сказать, что кто-то из них первый, а кто-то второй. Они одинаковы. Но при таком подходе возникает проблема: а какой ранг должен получить следующий сотрудник? Например, если первые два были одинаковые и у них ранги 1, то сотрудник со второй зарплатой в компании должен иметь ранг 2 или 3?

@sqlhub

🖥 Серия вопросов по СУБД (в частности Postgres). По всем пунктам можно погрузиться в достаточно низкоуровневые детали реализации, но важно и в целом оценить осведомленность кандидата об основных принципах работы базы, достаточных для большинства задач.

***

Что такое транзакция? Приведите пример, где это может пригодиться. Расскажите про свойства транзакций и уровень изолированности.

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

Что такое server side cursor и зачем он нужен?

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

Что такое VACUUM и зачем он нужен в PostgreSQL?

Ответ
Команда VACUUM высвобождает пространство, занимаемое «мертвыми» кортежами, что актуально для часто используемых таблиц. При обычных операциях в Postgres кортежи, удаленные или устаревшие в результаты обновления, физически не удаляются, а сохраняются в таблице до очистки.

Что такое EXPLAIN? Какая разница между ним и EXPLAIN ANALYZE?

Ответ
EXPLAIN ANALYZE – в отличие от просто EXPLAIN не только показывает план выполнения запроса, но и непосредственно выполняет запрос и показывает реальное время выполнения

@sqlhub

🖥 Хороший урок с задачами SQL

Github

@sqlhub

🖥 Вопросы собеседований

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

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

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

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

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

- Первая нормальная форма (1NF) — нет повторяющихся групп в строках

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

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

@sqlhub

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

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

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

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

@sqlhub

🖥 Вопросы собеседований

Удержание пользователей в месяц

Контекст: допустим, у нас есть статистика по авторизации пользователей на сайте в таблице 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)

@sqlhub

🖥 Эквивалент SQL Left Join в Python и его использование при очистке данных

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

При выполнении left, right или full outer joins вы создаете таблицы, в которых присутствуют либо все записи, либо только записи из определенных таблиц. Для строки, в которой нет совпадений, помещается нулевое значение. Таким образом, соединения (Join) чрезвычайно полезны для определения отсутствующих или не связанных значений.

Представьте, что у вас есть таблица users в вашей базе данных, которая содержит всех ваших пользователей. Кроме того, у вас есть несколько других таблиц, которые ссылаются на идентификаторы таблицы users, такие как posts, logins, subscriptions и т.д. Вы заинтересованы в том, чтобы выяснить, кто из пользователей может быть удален из базы данных, поскольку они не взаимодействовали с вашим сайтом осмысленно. Это можно сделать, проверив, есть ли ссылки на идентификаторы в другом месте.

➡️ Читать дальше

@sqlhub

🖥 Преобразование xml-поля в SQL

В данной публикации рассматривается метод по парсингу информации из xml полей в таблицах различных баз данных в СУБД MS SQL.

В таблицах баз данных встречаются текстовые поля nvarchar(max), которые содержат в себе xml-формат представления данных. Фактически это таблица в таблице со своими столбцами и строками.

➡️ Читать дальше

@sqlhub

🖥 Вопросы собеседований

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


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

- Некоррелированный подзапрос: этот запрос является независимым запросом, в котором выходные данные подзапроса подставляются в основной запрос.

@sqlhub

🖥 Вопросы собеседований

В чем разница между командами DROP и TRUNCATE

Команда DROP удаляет саму таблицу, и нельзя сделать Rollback команды, тогда как команда TRUNCATE удаляет все строки из таблицы (прим. перевод.: в SQL Server Rollback нормально отработает и откатит DROP).

@sqlhub

🖥 Задача. Подсчет сессий пользователя.

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

+------+----------------------------+
| user | login |
+------+----------------------------+
| 1 | 2022-10-12 20:05:00.000000 |
| 1 | 2022-10-12 20:10:00.000000 |
| 1 | 2022-10-12 20:15:00.000000 |
| 1 | 2022-10-12 20:20:00.000000 |
| 1 | 2022-10-12 20:25:00.000000 |
| 1 | 2022-10-12 10:05:00.000000 |
| 1 | 2022-10-12 20:40:00.000000 |
| 1 | 2022-10-12 20:30:00.000000 |
| 1 | 2022-10-12 10:20:00.000000 |
| 1 | 2022-10-12 10:10:00.000000 |
| 1 | 2022-10-12 20:05:00.000000 |
| 2 | 2022-10-12 20:10:00.000000 |
| 2 | 2022-10-12 20:15:00.000000 |
| 2 | 2022-10-12 20:20:00.000000 |
| 2 | 2022-10-12 20:25:00.000000 |
| 2 | 2022-10-12 10:05:00.000000 |
| 2 | 2022-10-12 20:40:00.000000 |
| 2 | 2022-10-12 20:30:00.000000 |
| 2 | 2022-10-12 10:20:00.000000 |
| 2 | 2022-10-12 10:10:00.000000 |
+------+----------------------------+

Впервую очередь отсортируем данные и применим функцию lag для смещения данных на 1 значение вниз

SELECT t1.user,
login,
lag(login, 1, NULL) OVER
(PARTITION BY
t1.user ORDER BY t1.user,
login) next_log
FROM table t1
Теперь вычтем смещение и посмотрим разницу между 2 повторяющимися значениями, после чего применим функцию камулятивной суммы, это как раз таки и даст необходимый нам результат.

select *,
sum(ind) over
(PARTITION BY t3.user
ORDER BY t3.user rows
between unbounded preceding and current row) as sessions
from (
select t2.user,
login,
case
when (login-prev_log) > '5 mins' then 1
else 0
end ind
from (
SELECT t1.user,
login,
lag(login, 1,NULL)
OVER (PARTITION BY t1.user
ORDER BY t1.user, login) prev_log
FROM table t1) t2
) t3

@sqlhub

🖥 Как бы вы нашли вторую по величине зарплату в этой таблице?

select * from employee
select max(e_salary) from employee where e_salary not in (select max(e_salary) from employee)

@sqlhub

🖥 Выполнится ли этот запрос?

SELECT
order_id,
order_code,
SUM(order_value)
FROM
orders
GROUP BY
order_id


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

Если этот запрос будет выполняться в MySQL, то колонка order_code добавится в выражение GROUP BY автоматически и запрос выполнится нормально. Если же этот запрос будет выполняться MS SQL Server, то по умолчанию будет сгенерирована ошибка. Впрочем, это поведение настраивается.

@sqlhub

🍏 APP DEV - быстрый вход в мобильную разработку. Практика, советы от профи, полезные инструменты.

🖥 Android разработка - бесплатный авторский канал, который научит созданию приложений.

🖥 React - создание технологичных приложений на React.

🖥 Java/Kotlin - здесь собраны отборные гайды и материалы по java разработке.