Глеб Зайцев SQL на практике. 60 задач с решениями на данных магазина

SQL на практике

60 задач с решениями на данных магазина

Глеб Зайцев

SQLite · самостоятельная практика

Первое книжное издание 2026

Для выполнения заданий нужен компьютер с Python 3 и модулем sqlite3. Весь код создания учебной базы включён в книгу. Отдельный архив, платный сервис и доступ к чужому серверу не требуются.

Как работать с практикумом

Эта книга поможет потренировать SQL на одной связанной базе интернет-магазина. В ней 60 задач: от отбора товаров до рейтингов и накопительных сумм. Сначала решайте самостоятельно, затем сверяйтесь с подсказкой и эталонным запросом во второй части.

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

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

Задачи не требуют менять исходные строки. Сохраните свой запрос в отдельный файл answer.sql и выполняйте его только на учебной базе. Приведённая в приложении команда открывает базу в режиме чтения.

Примеры рассчитаны на диалект SQLite. Перенос на PostgreSQL, MySQL или другую СУБД потребует адаптации функций дат и некоторых правил группировки. Все 60 эталонов проверены на SQLite 3.53.1. Это техническая проверка примеров, а не обещание трудоустройства или уровня квалификации.

Подготовка учебной базы

Схема и правила расчётов

Часть первая Задачи

Базовые выборки

Агрегации

Связи таблиц

Даты и периоды

Подзапросы и CTE

Оконные функции

Часть вторая Подсказки и решения

Приложение А Создание базы

Приложение Б Выполнение запросов

Приложение В Проверки спорных случаев

Документация

Подготовка учебной базы

Создайте отдельную пустую папку для практикума. В текстовом редакторе создайте в ней файл create_shoplab.py в кодировке UTF-8. Скопируйте в него весь код из приложения А: от первой строки импорта до последнего print. Не включайте заголовки книги и номера страниц. Сохраняйте обычные прямые кавычки и переносы строк. В этой электронной версии каждая строка Python начинается с левого края: код не зависит от абзацных отступов читалки.

В терминале, открытом в этой папке, выполните команду ниже. Если на вашем компьютере Python запускается как python3 или py, замените только первое слово команды.

python create_shoplab.py

Программа создаст shoplab.sqlite3 рядом с файлом и выведет контрольные количества строк. Если база уже существует, она остановится и не перезапишет её. Для повторного чистого запуска используйте новую пустую папку.

customers — 30 строк.

products — 18 строк.

orders — 72 строк.

order_items — 144 строк.

payments — 60 строк.

Python и встроенная в него SQLite могут иметь разные версии. Для проверки поддержки оконных функций выполните следующий короткий пример в отдельном файле check_sqlite.py. Он не создаёт файлов базы и должен напечатать [(1,)].

import sqlite3

db = sqlite3.connect(":memory:")

print(db.execute("SELECT row_number() OVER ()").fetchall())

db.close()

Если Python не найден, установите актуальную стабильную версию Python 3 с официального сайта python.org. Если проверка выдаёт ошибку синтаксиса у OVER, используемая библиотека SQLite слишком старая для шестой главы. Текст книги можно читать с телефона, но выполнять практические задания удобнее на компьютере.

Создайте query.py из приложения Б. В файле answer.sql сохраните один запрос без обрамляющих кавычек:

SELECT COUNT(*) AS customers_count

FROM customers;

python query.py answer.sql

Ожидается заголовок customers_count, значение 30 и строка Строк: 1. Затем заменяйте содержимое answer.sql своим решением. Команда показывает весь результат и число строк; автоматическую оценку она не выставляет.

В некоторых читалках копирование длинного кода неудобно. Откройте приобретённую книгу на компьютере в доступном текстовом формате. При переносе проверяйте переносы строк и прямые кавычки. Если читалка добавила отступы абзацев, уберите пробелы только в начале строк кода; внутри строк пробелы сохраняйте. Код нельзя копировать как картинку.

Схема и правила расчётов

Покупатели. customer_id — первичный ключ, customer_name — учебное имя, city — город, segment — retail, pro или premium, signup_date — дата регистрации. email_opt_in равен 1 при согласии на рассылку и 0 иначе.

Товары. product_id — ключ, product_name — название, category — office, home, electronics или sport. price_rub — текущая цена в целых рублях, stock_qty — учебный остаток, is_active — признак активности 0 или 1.

Заказы. order_id — ключ, customer_id ссылается на покупателя, order_date — дата. status принимает paid, shipped, delivered или cancelled. channel принимает market, site или partner.

Строки заказов. Составной ключ order_id и product_id допускает товар в заказе один раз. quantity — количество, unit_price_rub — цена за единицу именно в этом заказе. Она может отличаться от текущей цены товара.

Платежи. payment_id — ключ; уникальный order_id означает не более одного платежа на заказ. paid_at — дата, amount_rub — сумма в целых рублях, method — card, sbp или invoice.

Связи: один покупатель имеет несколько заказов; один заказ имеет несколько товарных строк и ноль или один платёж. Одна строка заказа относится к одному товару. Внешние ключи включены при создании базы.

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

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

Платежи охватывают январь — август 2026 года; август неполный. Дни и месяцы без строк не добавляются автоматически. Временем отсчёта для «последних 30 дней» служит максимальная дата набора, а не сегодняшний день.

Глава 1 Базовые выборки

Начните с набора строк. WHERE оставляет только строки, удовлетворяющие условию, а SELECT задаёт столбцы результата. ORDER BY нужен, если важна последовательность; без него порядок не обещан.

В LIMIT сначала определите сортировку. Для устойчивого результата добавляйте идентификатор после основной метрики, если условие не требует сохранять ничьи. NULL проверяют через IS NULL, а не через равенство.

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

Выведите все активные товары: идентификатор, название, категорию и цену. Отсортируйте сначала по категории, затем по цене по возрастанию.

Результат: product_id, product_name, category, price_rub.

Найдите покупателей из Москвы и Казани. Покажите имя, город и сегмент, отсортируйте по городу и имени.

Результат: customer_name, city, segment.

Покажите идентификатор заказа, покупателя, дату и статус для заказов за февраль 2026 года. Включите весь месяц. Отсортируйте по дате и идентификатору заказа.

Результат: order_id, customer_id, order_date, status.

Получите список уникальных городов покупателей в алфавитном порядке.

Результат: city.

Найдите пять самых дорогих активных товаров.

Результат: product_name, category, price_rub.

Выведите товары с остатком меньше 10 единиц, но больше нуля.

Результат: product_id, product_name, stock_qty.

Найдите товары, в названии которых содержится подстрока «набор» точно в указанном регистре. Выведите product_id, product_name и price_rub, отсортировав по product_id.

Результат: product_id, product_name, price_rub.

Покажите все отменённые заказы из канала site.

Результат: order_id, customer_id, order_date.

Разделите товары на ценовые группы: цена меньше 500 рублей — low; от 500 включительно до 1000 не включительно — mid; от 1000 включительно — high. Выведите название, цену и группу, отсортировав по цене и product_id.

Результат: product_name, price_rub, price_band.

Выведите покупателей, которые согласились на email-рассылку и зарегистрировались не раньше 1 марта 2026 года.

Результат: customer_id, customer_name, signup_date.

Глава 2 Агрегации

GROUP BY меняет единицу наблюдения: вместо отдельных строк вы получаете одну строку на группу. WHERE фильтрует исходные строки, HAVING — уже посчитанные группы.

Перед SUM определите, что именно складывается: стоимость строк заказа, полученные платежи или количество единиц. COUNT(*) считает строки, COUNT(поле) пропускает NULL. Для долей используйте 100.0, чтобы избежать целочисленного деления.

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

Посчитайте количество покупателей в каждом городе.

Результат: city, customers_count.

Найдите среднюю цену активных товаров в каждой категории, округлив до двух знаков.

Результат: category, avg_price_rub.

Посчитайте число заказов каждого статуса.

Результат: status, orders_count.

Посчитайте сумму полученных платежей по каждому способу оплаты.

Результат: method, payments_count, revenue_rub.

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

Результат: product_id, product_name, units_sold.

Рассчитайте стоимость строк каждого заказа как сумму quantity × unit_price_rub. Учитывайте все статусы. Отсортируйте по сумме по убыванию, затем по order_id.

Результат: order_id, order_total_rub.

Покажите покупателей, у которых не меньше трёх заказов.

Результат: customer_id, customer_name, orders_count.

Найдите минимальную и максимальную цену внутри каждой категории.

Результат: category, min_price_rub, max_price_rub.

Рассчитайте долю отменённых заказов в процентах с точностью до одного знака.

Результат: cancellation_rate_pct.

Рассчитайте средний чек по фактически полученным платежам.

Результат: average_payment_rub.

Глава 3 Связи таблиц

Соединение связывает строки по ключу. INNER JOIN оставляет совпадения, LEFT JOIN сохраняет все строки слева и подставляет NULL там, где пары нет.

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

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

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

Результат: order_id, order_date, status, customer_name, city.

Соберите детализацию строк заказа: заказ, товар, количество, цена и сумма строки.

Результат: order_id, product_name, quantity, unit_price_rub, line_total_rub.

Найдите покупателей, у которых ещё нет ни одного заказа.

Результат: customer_id, customer_name, city.

Найдите товары, которые ни разу не попадали в заказ.

Результат: product_id, product_name, category.

Покажите заказы и способ оплаты. Сохраните в результате отменённые заказы без платежа.

Результат: order_id, status, method, amount_rub.

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

Результат: category, revenue_rub.

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

Результат: customer_id, customer_name, paid_rub.

Найдите заказы с существующим платежом, у которых сумма строк отличается от amount_rub. Заказы без платежа не включайте. Отсортируйте по order_id.

Результат: order_id, items_total_rub, amount_rub.

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

Результат: channel, orders_count, paid_rub.

Выведите топ-10 пар «покупатель — товар» по потраченной сумме.

Конец ознакомительного фрагмента.

Текст предоставлен ООО «Литрес».

Прочитайте эту книгу целиком, купив полную легальную версию на Литрес.

Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.