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

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

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

🖥 Задача. у вас в базе данных есть две таблицы: TABLE1 и TABLE2. Нужно ответить на один простой вопрос: отработает ли данный запрос или упадет с ошибкой? И объяснить, почему.

select * from table1 where null = null or null <> null or 123 <> null or null is null

Примечание: представьте, что таблица table1 не пустая. Вернет ли этот запрос строки? Почему?

Ответ

Да, вернет. Причина в последнем условии null is null. Оно указано правильно. Дело в том, что при операции сравнения (=, <> , > , <) значения null c каким-либо числом или полем всегда возвращается значение FALSE. Т.е. синтаксический запрос написан правильно и не упадет, но он отсекает строки.

NULL в базах данных — это специальное значение, которое может быть записано в поле таблицы. NULL соответствует понятию «пустое поле», т.е. «поле, не содержащее никакого значения». Но его нельзя сравнивать константой. NULL можно сравнивать только через оператор is т.е. null is null или field1 is null.

@sqlhub

🖥 Задачи, основанные на агрегатных функциях

Язык SQL отлично подходит для агрегации. Для этого есть множество функций, например SUM(), AVG(), MAX(), MIN(), COUNT() и т. д. Знание таких функций — это базовый уровень знаний, который ожидается от кандидата.

Рассмотрим следующую таблицу сотрудников. В каждой строке указаны данные о сотруднике — отдел, зарплата, руководитель и т. д.

-- Table: employees
-- | dept_id | employee_id | amount | manager_id |
-- |---------|-------------|--------|------------|
-- | 1 | 1 | 8000 | 3 |
-- | 1 | 2 | 5000 | 3 |
-- | 1 | 3 | 10000 | null |
-- | 2 | 4 | 15000 | null |
-- | 2 | 5 | 16000 | 4 |
-- | 3 | 6 | 8000 | null |

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

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

-- Часть 1: Получаем самую высокую зарплату в каждом отделе
SELECT max(amount) AS salary
From employees
GROUP BY dept_id

-- Часть 2: Получаем желаемый формат вывода employee_id
-- Поскольку employee_id нельзя напрямую использовать в группе путем агрегации, мы можем прибегнуть к подзапросам.

SELECT e1.employee_id
FROM employees e1
WHERE e1.amount IN (
SELECT max(e2.amount) AS amount
From employees as e2
GROUP BY e2.dept_id
HAVING e1.dept_id = e2.dept_id )

@sqlhub

🖥 Почему не выполнится этот запрос?

SELECT
user_name,
YEAR(user_birth_date) AS year_of_birth
FROM
users
WHERE
year_of_birth = 2000

Ответ
Запрос не выполнится из-за обращения к псевдониму year_of_birth в выражении WHERE. Дело в том, что псевдонимы полей в SQL используются для форматирования данных уже полученных из базы. Поэтому их можно использовать только в выражениях, которые отвечают за оформление результата, таких как GROUP BY, ORDER BY и HAVING. В выражениях, отвечающих за получение данных, таких как WHERE, нужно использовать оригинальные имена полей.

WHERE
YEAR(user_birth_date) = 2000

@sqlhub

🖥 DB Fiddle — онлайн-площадка для работы с базами данных SQL

Её можно использовать как своеобразную песочницу для тестирования, отладки и обмена фрагментами SQL. Есть поддержка разных версий MySQL, PostgreSQL и SQLite

Из интересного: к работе над базой можно подключить приятеля — делается это буквально в пару кликов

Стоимость: #бесплатно (но есть платный тариф)

#db #sql #web

@sqlhub

🖥 Имеет ли значение порядок колонок в составном индексе?

Ответ
Да.

CREATE NONCLUSTERED INDEX MyInd on users (user_name, user_birth_date);

это не то же самое, что

CREATE NONCLUSTERED INDEX MyInd on users (user_birth_date, user_name);

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

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

@sqlhub

🖥 SQL — Индексы.

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

Например, если вы хотите сослаться на все страницы в книге, на которых рассмотрена определенная тема, вам сначала нужно сослаться на индекс, в котором перечислены все темы в алфавитном порядке, а затем на несколько конкретных номеров страниц.
Индекс позволяет увеличить производительность запросов SELECT и WHERE, но замедляет ввод данных с помощью операторов UPDATE и INSERT. Индексы можно создать или удалить не затрагивая данные.

Создание индекса включает инструкцию CREATE INDEX, которая позволяет указать индекс, таблицу и столбцы или столбцы для индексации, а также задать порядок индексации: по возрастанию или по убыванию.

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

➡️ Читать

@sqlhub

🖥 Какая разница между TRUNCATE TABLE table_name и DELETE FROM table_name?

Ответ
Фактически обе эти команды вызовут удаление всех строк из таблицы под названием table_name, но вот произойдет это совсем по-разному:

1. При вызове команды TRUNCATE таблица полностью сбрасывается и создается снова, в то время как команда DELETE удаляет каждую строку таблицы по отдельности. Из-за этого TRUNCATE отрабатывает значительно быстрее.

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

3. В отличие от DELETE команда TRUNCATE не транзакционная. То есть, если в момент ее вызова, таблица table_name будет заблокирована какой-либо транзакцией — может возникнуть ошибка.

@sqlhub