--- title: Лайфкодинг SQL seo: title: Лайфкодинг SQL — Data Scientist description: Тема «Лайфкодинг SQL» для собеседования Data Scientist. Задача №1. Задача №2. --- [Все темы Data Scientist](/data-scientist) ### ⌨️ Задачи --- ## Задача №1 [#q-14bee738d69b81019777c47c5b66b79e] ```text Пусть имеем таблицу с 3 столбцами: id, name и age, которая содержит 100 млн строк. Допустим, неизвестно, является ли id первичным ключом таблицы. Хотим проверить, так ли это. Каким простым запросом можно сделать такую валидацию? Допустим, нужно добавить в эту таблицу столбец с количеством раз, которое каждое имя (поле name) встречается в таблице. Как это сделать? Допустим, нужно определить возраст, который чаще всего встречается в таблице. Как это сделать? ``` Решение: - Этот запрос проверяет отсутствие NULL и повторов в текущих данных id. Наличие ограничения PRIMARY KEY проверяют отдельно в метаданных схемы. ```sql SELECT COUNT(*) AS total_rows, COUNT(id) AS non_null_ids, COUNT(DISTINCT id) AS distinct_ids FROM your_table_name; ``` Если все три значения равны, в текущих данных id нет NULL и повторов. Это ещё не означает, что в схеме объявлен PRIMARY KEY. - Чтобы добавить столбец с количеством раз, которое каждое имя встречается в таблице, можно использовать следующий запрос: ```sql ALTER TABLE your_table_name ADD COLUMN name_count INT; UPDATE your_table_name SET name_count = (SELECT COUNT(*) FROM your_table_name t2 WHERE t2.name = your_table_name.name); ``` Этот запрос добавляет новый столбец name_count в таблицу и затем обновляет его значения, подсчитывая количество строк с каждым именем. - Чтобы определить возраст, который чаще всего встречается в таблице, можно использовать следующий запрос: ```sql SELECT age FROM your_table_name GROUP BY age ORDER BY COUNT(*) DESC LIMIT 1; ``` Этот запрос группирует строки по возрасту, считает количество строк для каждого возраста, затем сортирует результаты по убыванию количества строк и выбирает самый часто встречающийся возраст. --- ## Задача №2 [#q-14bee738d69b81eaaf7eed76f8707425] ```text Для каждого клиента вычислить средний интервал между заказами в днях, используя следующие таблицы: Таблица order содержит следующие столбцы: - order_id: уникальный идентификатор заказа. - date_time: дата и время заказа. - amount: сумма заказа. - customer_id: идентификатор клиента, связанный с заказом. Таблица customer содержит следующие столбцы: - customer_id: уникальный идентификатор клиента. - age: возраст клиента. Для каждого клиента необходимо вычислить средний интервал между его заказами в днях. ``` Для вычисления среднего интервала между заказами для каждого клиента, мы можем сделать следующее: 1. Сначала для каждого клиента упорядочим заказы по date_time и order_id и найдём предыдущий заказ с помощью LAG. 1. Вычислим длительность между соседними заказами в секундах и переведём в дни. 1. Наконец, мы сгруппируем результаты по customer_id и вычислим среднее значение интервала для каждого клиента. Вот как это можно сделать в SQL: ```sql WITH intervals AS ( SELECT customer_id, TIMESTAMPDIFF(SECOND, LAG(date_time) OVER ( PARTITION BY customer_id ORDER BY date_time, order_id ), date_time) / 86400.0 AS interval_days FROM `order` ) SELECT c.customer_id, AVG(i.interval_days) AS avg_interval_days FROM customer c LEFT JOIN intervals i ON i.customer_id = c.customer_id GROUP BY c.customer_id; ``` Запрос для MySQL 8 вычисляет интервалы между соседними заказами и усредняет их по клиенту. Для клиентов с нулём или одним заказом средний интервал равен NULL. При одинаковом времени заказов учитывается нулевой интервал. --- ## Задача №3 [#q-14bee738d69b815db1e4f973d6711ce6] ```text Предложить SQL запрос для оценки качества данных в динамике по заполненности. В таблице table есть отчётные даты report_dt на конец месяца, номера договоров id не Null. Названия полей по которым анализируем пропуски: feature1, feature2. Вид таблицы: table(report_dt, id, feature1, feature2) Пример строки: '2024-01-31', 457294, 1, Null '2024-02-28', 784934, Null, 3 ``` Решение: ```sql SELECT report_dt, COUNT(id) AS total_records, COUNT(feature1) AS filled_feature1, COUNT(feature2) AS filled_feature2, 1.0 * COUNT(feature1) / COUNT(*) AS feature1_completion_rate, 1.0 * COUNT(feature2) / COUNT(*) AS feature2_completion_rate FROM "table" GROUP BY report_dt ORDER BY report_dt; ``` --- ## Задача №4 [#q-14bee738d69b815a87e5d5fe4e0dd173] ```sql -- Вот условие задания: CREATE TABLE shop ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL ); CREATE TABLE cheque ( uid UUID NOT NULL DEFAULT gen_random_uuid(), created_at TIMESTAMP NOT NULL DEFAULT now(), "sum" DECIMAL(10, 2) NOT NULL, shop_id INT NOT NULL, customer_id BIGINT NOT NULL, PRIMARY KEY (uid), FOREIGN KEY (shop_id) REFERENCES shop (id) ); -- Вот само задание, которое нужно решить: -- Вывести средний чек для покупателя за последнюю неделю, -- когда у него была покупка: -- покупатель - средний чек - дата последней покупки. ``` Решение для PostgreSQL. Под последней неделей понимаем календарную неделю с последней покупкой каждого клиента. ```sql WITH last_dates AS ( SELECT customer_id, MAX(created_at) AS last_purchase_date FROM cheque GROUP BY customer_id ) SELECT c.customer_id, AVG(c."sum") AS average_check, d.last_purchase_date FROM cheque c JOIN last_dates d ON c.customer_id = d.customer_id WHERE c.created_at >= date_trunc('week', d.last_purchase_date) AND c.created_at < date_trunc('week', d.last_purchase_date) + INTERVAL '1 week' GROUP BY c.customer_id, d.last_purchase_date; ``` --- ## Задача №5 [#q-14bee738d69b81b2aa56d9b5a566045c] ```text Есть две таблицы А=10 строк В=50 строк какое максимальное и минимальное количество строк получим при A left join B ``` Решение: При выполнении операции left join (или left outer join) в SQL, результат будет содержать все строки из таблицы A, даже если в таблице B нет соответствующих строк. Таким образом, минимальное количество строк в результирующей таблице будет равно количеству строк в таблице A, то есть 10 строк. Максимум составляет 10 × 50 = 500 строк, если каждая строка A соответствует каждой строке B по условию соединения.