sql задания для практики
5 сайтов для оттачивания навыков написания SQL-запросов
Одним из ключевых навыков при работе с базами данных является умение писать sql-запросы. Мы отобрали 5 лучших сайтов для оттачивания этого навыка.
1. sql-ex и sql-tutorial
2. pgexercises
Как следует из названия, этот сайт является задачником по написанию запросов, используя синтаксис Postgre DB. Упражнения делятся по тематикам. Рассматриваются такие вещи, как рекурсия, строки, использование aggregate функций и join’ов, работа с датами и подзапросами. Для желающих предоставляем ссылку на документацию.
3. SQLBolt
Интерактивный туториал по изучению SQL. Рекомендуется для новичков и тех, кто желает освежить свои знания. Здесь нет типичного деления на практику и теорию, информация подаётся в формате уроков. Уроки состоят из необходимой теории с примерами, а в конце предлагается несколько задач по только что прочитанному материалу. Вводных уроков 18, разбираются такие темы, как ограничения, join’ы, выражения, агрегаты и действия с таблицами и со строками. Однако если вы уже прилично разбираетесь в написании запросов, то, возможно, вам стоит обратить внимание на более «продвинутые» источники.
4. sqlzoo
Указанный ресурс отлично подойдёт для программистов любого уровня. Здесь есть простенькие задачи, разбитые по темам, теоретическая справка по каждому пункту и викторина по выбранной теме. Вся информация излагается лаконично и в доступной форме.
5. hackerrank
Сайт, который исповедует принцип соревновательного программирования. Справки нет, упражнения делятся по темам, сложности и максимальному количеству баллов за безупречное решение задачи. Ресурс всемирный, и предоставляет возможность также посоревноваться в решении задач на C++, Python, Java и PHP. Рекомендуется тем, кто уже неплохо разбирается в базах данных.
5 заданий по SQL с реальных собеседований
Авторизуйтесь
5 заданий по SQL с реальных собеседований
SQL — один из самых востребованных навыков в современной IT индустрии (на 3 месте по популярности согласно StackOverflow Developer Survey 2020, даже Python идет на 4 месте).
Само собой, конкуренция в этой сфере огромна и собеседования порой превращаются в сущую пытку — кандидатам дают огромные задачи, задают десятки каверзных вопросов, устраивают дизайн-интервью и все это на позицию Junior аналитика…
Сегодня Елизавета, аналитик и эксперт IT Resume, делится 5 задачами и вопросами, которые входят в программу подготовки к собеседованию SQL Interview. Это задачи с реальных собеседований в крупные IT-компании, и они разбиты по уровням — Junior, Middle и Senior. Попробуйте и вы свои силы — сможете их решить без подсказки 😉
аналитик и эксперт в IT Resume
Вводные данные
Есть таблица анализов Analysis:
Есть таблица групп анализов Groups:
Есть таблица заказов Orders:
Далее мы будем работать с этими таблицами.
Задача 1. Уровень: Junior
Формулировка: вывести название и цену для всех анализов, которые продавались 5 февраля 2020 и всю следующую неделю.
Это задача для начинающих специалистов. В ней проверяется базовое знание SELECT-запросов и умение работать с датой-временем.
Примечание На собеседованиях редко придираются к специфике диалекта — если Вы привыкли работать в PostgreSQL, то используйте привычные функции. Главное — правильно решить задачу.
Задача 2. Уровень: Middle
Формулировка: нарастающим итогом рассчитать, как увеличивалось количество проданных тестов каждый месяц каждого года с разбивкой по группе.
Эта задача уже более высокого уровня: ее можно давать как Middle, так и Junior специалистам. Здесь проверяется базовое понимание оконных функций, джоинов и группировок.
Примечание После того, как вы написали первую версию своего запроса, попробуйте его оптимизировать. Например, в данном примере мы используем CTE — обобщенные табличные выражения.
Задача 3: Уровень Senior
В этой задаче мы будем работать с другой таблицей (да, она будет всего одна). Сам запрос в этой задаче не сложный, но для его написания необходимо как бы уметь «мыслить на SQL».
Рассмотрим таблицу балансов клиентов:
ClientBalance(client_id, client_name, client_balance_date, client_balance_value)
Формулировка: в данной таблице в какой-то момент времени появились полные дубли. Предложите способ для избавления от них без создания новой таблицы.
Вопрос 1. Уровень: Junior
Есть категория «хитрых» вопросов, которые особенно любят задавать Junior-специалистам. Хотя чего уж там, любым специалистам.
Один из таких видов — вопросы по темам, на которые в повседневной жизни не обращаешь внимания и о которых не задумываешься. Просто делаешь на автомате, а на собеседовании это стреляет. Ну или на проекте, когда код начинает работать неправильно. Но это другая история…
Вопрос: Как оператор GROUP BY обрабатывает поля с NULL?
Если Вы не знаете ответ на этот вопрос, то после прочтения ответа обязательно проверьте свои проекты — может у вас где-то закралась ошибка? 😉
Учитывая, что NULL в SQL — просто отсутствие значения, то все значения NULL при группировке попадают в одну группу. Например, пусть есть таблица:
Тогда запрос select sum(score) from table group by name даст:
Вопрос 2. Уровень Middle
Этот вопрос не такой хитрый, как предыдущий, а вполне себе конкретный. Однако, он требует знания оконных функций и их тонкостей, а это исконное Middle требование.
Примечание Кстати говоря, по мотивам этого вопроса очень любят давать задачи на собеседованиях в разных вариациях: пронумеровать строки с одинаковыми значениями без разрывов, с разрывами и так далее. Так что потренируйтесь на досуге 🙂
Эпилог
Мы разобрали с вами всего 5 возможных заданий, которые судьба и рекрутеры могут подкинуть вам на собеседовании. Однако, вариантов, на самом деле, масса. И чтобы реально подготовиться к собеседованию, нужно нарешать как можно больше задач, понять и прочувствовать теоретические аспекты и буквально научиться «мыслить на SQL». Именно на это и нацелена программа SQL Interview — так что будем рады помочь вам получить работу мечты!
И, помимо этого, не забывайте: SQL — чисто прикладной инструмент. Чтобы его освоить, нужно практиковаться, практиковаться и практиковаться. Выучить его можно буквально за несколько недель интенсивных занятий, а вот освоить на уровне Senior… порой на это уходят годы.
11 задач по SQL с собеседований на позицию Data Engineer
Авторизуйтесь
11 задач по SQL с собеседований на позицию Data Engineer
data-аналитик в ГК FINBRIDGE
Позиция data-инженера востребована в IT-индустрии. Такой специалист занимается обработкой данных, которые чаще всего хранятся в базах данных. Работа с БД, в свою очередь, подразумевает хорошее знание языка запросов SQL.
На собеседованиях в мобильном операторе мы для проверки у кандидата знаний SQL задавали небольшие, но хитрые задачи по запросам, которые будут описаны ниже. Некоторые из них вводили в ступор даже опытных разработчиков баз данных.
Итак, начнём. Представьте, что у вас в базе данных есть две таблицы: TABLE1 и TABLE2. Ниже будут приведены несколько SQL-запросов. Нужно ответить на один простой вопрос: отработает ли данный запрос или упадет с ошибкой? И объяснить, почему.
Задача 1
Вопрос с подвохом. Тут нет однозначного ответа, отработает или нет. Всё зависит от списка полей в таблице. Символ * эквивалентен перечислению всех полей таблицы. Если в таблице будет всего одно поле с названием id, то запрос отработает. Иначе — нет, потому что при наличии в таблице нескольких полей символ * будет означать перечисление всех полей, что в SQL требует перечисления искомого в конструкции GROUP BY. Либо кандидат должен уточнить, какие поля есть в таблице, дабы однозначно ответить на вопрос.
Задача 2
Да, отработает. В SQL допускается перечисление не всех полей в разделе SELECT, если мы группируемся по нескольким полям. В данном примере группировка выполняется по двум полям, а выводится на экран одно поле. А вот наоборот сделать уже нельзя.
Задача 3
Примечание: field1, field2 являются числовыми полями.
Да, отработает. В SQL можно в разделе having указывать поле, по которому выполняется группировка. Несмотря на то, что на практике такое условие редко используется, подобный запрос можно выполнить. Чаще всего разработчики в конструкции having используют агрегирующую функцию — например, having count(*) >0. Эта задача рассчитана на то, чтобы сбить с толку. Кандидат, хорошо знающий язык SQL, ответит на вопрос уверенно, в то время как неопытный засомневается. Хотя могут быть исключения.
Задача 4
Нет, не отработает. Опытный data-инженер знает, что row_number() — это часть аналитической функции, которая не может быть применена сама по себе. Аналитические функции используются только в конcтрукциях SELECT или ORDER BY.
Задача 5
Примечание: field1, field2, field3 являются текстовыми полями.
Здесь всё очень просто. При вставке строчек в таблицу количество полей при объявлении не соответствует количеству вставляемых значений в конструкции values. Это должен знать даже начинающий SQL-разработчик.
Задача 6
Здесь тоже всё просто — запрос не отработает. Конструкция having не может использоваться при удалении строк. Она используется только при операции SELECT и чаще всего вместе с группировкой строк (GROUP BY). Эта задачка рассчитана на новичков. Опытный SQLщик сразу ответит на вопрос.
Задача 7
Тоже довольно простой вопрос. Разработчик, постоянно работающий с базами данных, сразу ответит, что будет ошибка. В запросе не хватает ключевого слова table, т.е. должно быть truncate table TABLE1. Также можно задать дополнительный вопрос: чем отличается truncate table TABLE1 от delete from TABLE1? Truncate — это DDL-операция, т.е. операция изменения объекта в базе. Она выполняется намного быстрее. Delete — это операция DML, т.е. операция с данными внутри таблицы. Опытный разработчик должен знать нюансы SQL.
Задача 8
Вопрос нацелен на то, чтобы сбить с толку кандидата. На практике при операции join в большинстве случаев выполняется жесткое сопоставление одного поля в одной таблице с другим полем во второй. Например, t.field1 = tt.field1. В данном же случае запрос отработает. В этом примере выполняется сначала декартово произведение всех строк одной таблице со всем строками другой и затем применяется условие, что tt.field1 = 1. Такое написание допустимо, хотя и редко где применяется. Неопытные data-инженеры часто впадают в ступор на этой задаче.
Задача 9
Примечание: все поля существуют в таблице и соответствуют нужному типу данных.
Да, отработает. Классический пример аналитической функции, внутри которой сначала выполняется группировка (partition by), потом выполняется сортировка (order by) и применяется агрегирующая функция. В данном случае все написано правильно. Этот вопрос проверяет у кандидата знание оконных функций.
Задача 10
Да, отработает. Такое написание синтаксиса допускается в SQL. В этом случае выполняется декартово произведение всех строк одной таблицы со всеми строками другой. Если её обогатить ещё конструкцией where с сопоставлением полей в таблицах table1 и table2, то получится классический inner join. На самом деле, это старый синтаксис внутреннего соединения таблиц. Обычно вместо нее рекомендуется использовать inner join или cross join из соображений оптимизации запросов.
Задача 11
Примечание: представьте, что таблица table1 не пустая. Вернет ли этот запрос строки? Почему?
Если готовитесь к устройству на работу или хотите просто освежить знания, посмотрите ещё эти 5 заданий по SQL с реальных собеседований.
Упражнения по SQL, практика, решение
Что такое SQL?
SQL расшифровывается как язык структурированных запросов и является стандартным компьютерным языком ANSI для доступа к системам баз данных и управления ими. Он используется для управления данными в системе управления реляционными базами данных, которая хранит данные в форме таблиц, а связь между данными также хранится в форме таблиц. Операторы SQL используются для извлечения и обновления данных в базе данных.
Список упражнений SQL
Структура базы данных инвентаризации:
Структура базы данных персонала:
Структура базы данных фильмов:
Структура футбольной базы данных:
Структура базы данных сотрудников:
Структура базы данных больницы:
Вы можете скачать структуру и данные таблиц базы данных, на которых построены упражнения SQL.
Синтаксическая диаграмма оператора SQL SELECT
Обратите внимание, что используется PostgreSQL 9.4, а файл, который вы хотите загрузить, создается с использованием pg_dump.
SQL для аналитики — рейтинг прикладных задач с решениями
Привет, Хабр! У кого из вас black belt на sql-ex.ru, признавайтесь? На заре своей карьеры я немало времени провел на этом сайте, практикуясь и оттачивая навыки. Должен отметить, что это было увлекательное и вознаграждающее путешествие. Пришло время воздать должное.
В этой публикации я собрал топ прикладных задач и мои подходы к их решению в терминах SQL. Каждая задача снабжена кусочком данных и кодом, с которым можно интерактивно поиграться на SQL Fiddle.
Уверен, неискушенные пользователи смогут многое для себя почерпнуть. Продвинутых же пользователей призываю поделиться своими наиболее интересными задачами и поучаствовать в обсуждении.
Конкатенация значений из нескольких строк в одну через разделитель
Когда это может быть полезно? К примеру, если исходный набор данных хранит каждый тег, присвоенный сделке, в отдельной строке (это же получится при соединении таблиц лидов и тегов), и есть необходимость собрать все теги, обойдясь при этом без дублирования строк по каждой сделке.
Формулировка задачи: Для каждого лида вывести список тегов, разделенных запятой в одном столбце
Аналитические функции при сохранении всех строк выборки
Речь пойдет о так называемых analytic functions, которые оперируют над партициями данных (окна, windows), возвращая результат для каждой строки. В отличие от aggregate functions, “схлопывающих” строки, оконные функции оставляют все строки выборки.
Окно определяется спецификацией (выражение OVER) и основывается на трех основных концепциях:
Разбиение строк на группы (выражение PARTITION BY)
Порядок сортировки строк в каждой группе (выражение ORDER BY)
Рамки, которые определяют ограничения по количеству строк относительно каждой строки (выражение ROWS)
Таких функций существует немало, от аналитических: всем известные SUM, AVG, COUNT, менее известные LAG, LEAD, CUMEDIST, и до ранжирующих: RANK, ROWNUMBER, NTILE. Я же приведу несколько простых примеров часто встречающихся запросов:
Ко всем транзакциям пользователя вывести дату первой покупки
К каждой транзакции добавить дату предыдущей транзакции пользователя
Показать сумму покупок пользователя нарастающим итогом
Присвоить всем транзакциям пользователя / продавца / отделения порядковый номер
Работа с NULL и применение логики ветвления IF-THEN-ELSE в SQL
Про COALESCE / NVL знают все, и нет смысла останавливаться на них подробно. Зато с NVL2 и NULLIF знакомы уже не так много людей.
Как обработать ошибку деления на 0 (divide by zero error)
Как выводить NULL вместо пустых строк (‘’)
NVL2 в свою очередь вернет одно из значений, в зависимости от того, является ли входной аргумент NULL или NOT NULL. Например, если в таблице транзакций есть ссылка на invoiceid, значит транзакция в сегменте B2B, и ее следует пометить соответствующим образом.
Но больше всего мне нравится функция DECODE. Она в буквальном смысле позволяет расшифровать значения согласно заданной вами логике:
Формулировка задачи: Присвоить численному коду (или, например, битовой маске) текстовые наименования.
Дедупликация данных
Это классика. Задачу часто спрашивают на собеседованиях в формулировке “как удалить дубли / копии строк”, и решить ее можно несколькими способами. Я привык мыслить в терминах историзации данных в Хранилище, и удаление мне ни к чему, поэтому для решения задачи я воспользуюсь ранжирующей функцией ROWNUMBER().
Формулировка задачи: Выбрать самую актуальную запись с учетом статуса (успешная / отмененная транзакция) и временнОй метки
Некоторые СУБД, например, Teradata позволяют сделать запрос короче при помощи выражения QUALIFY:
Анализ временных рядов
Замечательно, если ваша BI система умеет работать с различными абсолютными и относительными фреймами, и наружу выставляет красивый визуальный интерфейс. Еще лучше, если в ваш инструментарий аналитика входит пара наиболее используемых функций:
Конвертация Unix Timestamp (epoch) в человекочитаемый формат
Анализ истории со Slowly Changing Dimensions (SCD)
Формулировка задачи: Какой статус был у клиентов на 3-й день месяца?
Формулировка задачи: Как в течение недели росло количество активных клиентов?
С помощью такого подхода можно подсчитать долю неактивных контрагентов на каждую дату за последний месяц. При этом неактивным считается контрагент, не совершивший ни одной транзакции за предыдущие 7 дней на каждую дату. Вот так может выглядеть визуализация решения задачи на дашборде:
Использование выражения CASE в агрегирующих функциях
Агрегирующие функции могут принимать в качестве аргумента результат оценки выражения CASE. Таким образом можно к агрегируемым строкам применить псевдофильтр. Это напоминает мне использование формулы СУММЕСЛИ из старого доброго Excel, только для реляционных баз данных. Смотрите сами:
Подсчитать все лиды и выручку
Подсчитать количество лидов со статусом success
Подсчитать выручку лидов с тегом python
Парсинг колонки с разделением на отдельные атрибуты
Чаще всего так поступают в условиях внешних ограничений, когда иного выхода нет. Например, при ограниченном наборе полей в CRM системе. Или при передаче нескольких UTM-меток в одной строковой переменной. Еще так могут делать люди, которые не слышали про нормализацию данных.
Формулировка задачи: Выделить закодированные в названии кампании атрибуты в отдельные колонки: сеть, регион, категория, температура, бренд.
Чуть более сложная ситуация с парсингом UTM-меток, а именно UTMContent, которая по сути является контейнером для произвольного набора атрибутов, разделенных любым символом. Поэтому стоит быть последовательным и аккуратным при формировании таких меток, хотя зачастую инженер вынужден работать с тем, что есть.
Формулировка задачи: Разбить строку UTMContent на отдельные атрибуты cid, gid, aid, kwd с соблюдением соответствия ключ-значение. Каждое значение предваряется наименованием ключа, все значения разделены вертикальной строкой (|).
FULL JOIN для соединений без потери строк
Уверен, что все знают про FULL JOIN, но кто хоть иногда использует этот тип соединения? Это незаменимый подход в ситуациях, когда я хочу сохранить все исходные строки с каждой стороны джоина. Иначе говоря, недопустимо терять факты трат денежных средств, даже если для них не нашлось соответствующих лидов в таблицах CRM.
Формулировка задачи: Подготовить витрину-трекер для сквозной аналитики лидов из CRM и трат из Рекламных Кабинетов (Яндекс.Директ, Google Adwords, Facebook).
Пример упрощен и умозрителен. Однако этой задаче я посвятил одну из своих предыдущих публикаций: Сквозная Аналитика на Azure SQL + dbt + Github Actions + Metabase и недавнее выступление на вебинаре: Путь Инженера Аналитики: Решение для Маркетинга. Тема заслуживает отдельного внимания.
Разбиение пользовательских событий на сессии
Для чего это можно использовать? Прежде всего, для того, чтобы перейти от анализа хитов (кликов) к полноценному анализу пользовательского взаимодействия и поведения. Во-вторых, улучшение UX и качества сервисов, проведение A/B тестирования. Наконец, поиск паттернов, определенных сегментов пользователей, в том числе fraud monitoring (защита от мошенничества и ботов).
30 минут бездействия
Начало новых суток
Смена источника трафика (возврат на сайт по клику на новый рекламный баннер)
Базовая задача сессионизации сводится к следующему: превратить последовательность кликов из лога веб-сервера в набор сессий.
Попробуем декомпозировать и решить задачу по частям:
Шаг 1. Для каждого пользователя берем идентификатор просмотра, время просмотра, источник трафика (хеш-сумма). Хеш-сумма берется от текстовой конкатенации атрибутов источника трафика: utm_source + utm_medium + utm_campaign. При этом обрабатываются null-значения в любом из столбцов (заменяются на литерал ‘null’). По хеш-сумме легко проверить смену источника трафика.
Шаг 3. Рассчитываем, является ли каждый хит началом новой сессии. Это проверка на выполнение любого из трех указанных выше условий окончания сессии:
Шаг 4. Присваиваем каждой сессии уникальный идентификатор. Для этого сначала необходимо пронумеровать сессии одного пользователя монотонно возрастающими числами. Затем построить уникальный суррогатный ключ сессии: к номеру сессии добавить идентификатор пользователя, взять хеш-сумму:
В реальном мире всё сложнее
Помимо логики, выраженной в SQL, не меньшее значение имеет ряд других факторов:
СУБД, с которой вы работаете: то, какие функции и возможности она поддерживает, формат хранения данных: в виде колонок или строк
Фактически используемый план выполнения запроса: алгоритмы соединения таблиц, локальность операций, наличие статистических данных у оптимизатора
Используемые физические и логические модели данных: индексы, материализованные представления, кеш, предварительно отсортированные данные
На занятиях курса Data Engineer я и мои коллеги готовим объемлющий и интересный контент, затрагивающий множество тем, связанных с архитектурой аналитических приложений, внутренним устройством систем обработки больших данных и развертыванием ML.
Советую посетить ближайшие открытые вебинары:
Оставляйте ваши комментарии и вопросы, предлагайте собственные примеры задач и подходы к решению.