L1. Запити до датасету

базовийспирається на модуль 3

Мета

Аналітику ставлять питання словами: «які покупці з Києва жодного разу не отримали замовлення». Перетворити таке питання на запит, що дає правильну відповідь і на чистих, і на брудних даних, — навичка, яку перевіряє ця робота. Датасет «Крамниця» нерівний навмисно: у ньому є порожні значення, однакові email у різному регістрі, товари без категорії, замовлення з кількома платежами і замовлення без жодного.

Після роботи ви зможете:

перекладати бізнесове питання на SELECT і передбачати, скільки рядків він поверне;
відрізняти запит, який правильний, від запиту, який лише проходить на вашому прикладі;
вибирати між JOIN, EXISTS, підзапитом, CTE і віконною функцією за змістом питання;
перевіряти результат запиту за даними, а не за відчуттям.

Завдання

Напишіть 29 запитів: по одному у файл queries/NN.sql, де NN — номер завдання. У файлі має бути один запит SELECT (або WITH ... SELECT) до PostgreSQL з датасетом small (про розміри див. Датасет). Заготовки файлів уже лежать у каталозі queries/, у кожній є назва завдання і перелік колонок.

Як порівнюється результат. Перевірка виконує ваш запит і порівнює його результат із еталонним рядок за рядком.

Важливі кількість колонок, їхній порядок і значення. Назви колонок, псевдоніми таблиць і спосіб запису (JOIN, підзапит, CTE) значення не мають.
Порядок рядків важливий лише там, де в умові сказано «за зростанням» чи «за спаданням». У таких завданнях указано й правило для рівних значень.
Числа порівнюються так, як їх друкує psql: 4.1 і 4.10 різні. Де потрібне округлення, користуйтеся round(..., n) над numeric, а не приведенням до float.
Час у базі зберігається в UTC. Де в умові йдеться про день чи місяць, їх рахують за київським часом.

Готово, коли ./check.sh друкує усе гаразд: 29 перевірок.

Чого робити не треба. Змінювати дані чи схему: перевірка виконує запити в режимі «лише читання», а запит, що пише, вона відхилить. Індекси для цих запитів не потрібні, вони з’являться в L4. Шукати розв’язки в check.sh марно: еталонні результати лежать у expected/ як списки значень, без запитів.

Розминка: фільтри, NULL, дати

01. Найбільші населені пункти. Назвіть десять найбільших населених пунктів каталогу за кількістю мешканців. За однакового населення першим іде менший settlement_id. Стовпці: name, population; порядок рядків обов’язковий.

02. Чорна п’ятниця. Скільки замовлень і на яку загальну суму (total_amount) покупці оформили в «чорну п’ятницю», 28 листопада 2025 року? День рахуйте за київським часом. Стовпці: кількість, сума.

03. Невідома дата народження. Яким активним покупцям (status = 'active') не можна надіслати вітання з днем народження, бо дату народження не вказано? Стовпці: customer_id.

04. Без акційного коду. Скільки замовлень оформлено не з промокодом BLACK25? Замовлення без промокоду теж рахуйте. Стовпці: одне число.

05. Важкі товари. Які товари важчі за 1 кг? Вага лежить в attributes під ключем weight_g, у грамах. Виведіть product_id і вагу числом. Стовпці: product_id, weight_g.

З’єднання

06. Київські покупці та їхні замовлення. Покажіть усіх покупців із Києва й кількість замовлень кожного (усі статуси), включно з тими, хто не замовляв нічого. Для таких кількість дорівнює нулю. Стовпці: customer_id, full_name, orders.

07. Кияни без жодного отриманого замовлення. Які покупці з Києва жодного разу не отримали замовлення, тобто жодне їхнє замовлення не має статусу delivered? Сюди входять і ті, хто не замовляв нічого. Стовпці: customer_id.

08. Гроші так і не прийшли. Скільки замовлень за кожним статусом так і не принесли грошей, тобто не мають жодного платежу зі статусом captured? Замовлення без жодного платежу теж входять. Стовпці: status, orders.

09. Виручка за способом оплати. Для кожного способу оплати (payments.method) порахуйте, у скількох різних замовленнях його використано й яка загальна сума (total_amount) цих замовлень. Якщо спробу оплати повторили тим самим способом, замовлення рахується один раз. Стовпці: method, orders, amount.

10. Товари, яких ніхто не отримав. Які товари жодного разу не потрапляли в доставлені (delivered) замовлення? Товари, яких не замовляв ніхто, теж входять. Стовпці: product_id, title.

11. Пункти видачі на дату. Скільки пунктів видачі кожного перевізника працювало 1 липня 2025 року? Пункт працював, якщо відкрився не пізніше цієї дати й не був закритий до неї. Дата закриття в таблиці — перший день, коли пункт уже не працює. Стовпці: carrier, points.

Агрегати і групи

12. Рейтинг продавців. Яка середня оцінка відгуків на товари кожного продавця, округлена до сотих? Продавець без відгуків лишається в результаті з порожньою оцінкою. Стовпці: seller_id, avg_rating.

13. Розподіл покупців за кількістю замовлень. Скільки покупців зробили 0, 1, 2 і так далі замовлень? Виведіть кількість замовлень і кількість покупців із такою кількістю, за зростанням кількості замовлень. Стовпці: n_orders, customers; порядок рядків обов’язковий.

14. Популярні промокоди. Якими промокодами скористалися щонайменше сто разів? Відсутність промокоду промокодом не вважається. Стовпці: promo_code, uses.

15. Частка замовлень із приміткою. Яка частка замовлень, у відсотках з однією цифрою після коми, має примітку покупця? Стовпці: одне число.

16. Місяці з виручкою понад 6 млн. У які місяці виручка доставлених замовлень перевищила 6 млн грн? Виведіть місяць у форматі YYYY-MM за київським часом, кількість замовлень і суму total_amount, за зростанням місяця. Стовпці: month, orders, amount; порядок рядків обов’язковий.

Підзапити

17. Замовлення без відгуку. Скільки замовлень за кожним статусом не мають жодного відгуку? Відгук пов’язаний із замовленням через reviews.order_id. Стовпці: status, orders.

18. Дорожчі за середнє по категорії. Які товари дорожчі за середню ціну товарів своєї категорії? Товари без категорії пропустіть. Стовпці: product_id.

19. Дорожчі за звичайне для покупця. Скільки замовлень за кожним статусом коштують більше за середню суму замовлень того самого покупця? Стовпці: status, orders.

20. Покупці багатьох продавців. Які покупці замовляли товари щонайменше п’яти різних продавців (у замовленнях будь-якого статусу)? Виведіть покупця й кількість різних продавців. Стовпці: customer_id, sellers.

21. Дублікати email. Скільки акаунтів має кожен email, що повторюється? Регістр літер не враховуйте, email виведіть малими літерами. Стовпці: email, accounts.

22. Злиття дублікатів. Старі акаунти з однаковим email (без урахування регістру) треба злити. У кожній групі лишіть акаунт із найбільшою кількістю замовлень; за рівності лишіть раніше зареєстрований, а за рівності й цього — із меншим customer_id. Виведіть пари: акаунт, який лишається, і акаунт, який видаляється. Кожен акаунт, що видаляється, стоїть в окремому рядку. Стовпці: keep_id, drop_id.

CTE і рекурсія

23. Виручка за кореневими категоріями. Яка виручка доставлених замовлень у кожній кореневій категорії разом з усіма вкладеними? Виручка — сума рядків quantity × unit_price × (1 − discount_pct / 100), кожен рядок окремо округлений до копійок. Товари без категорії об’єднайте в один рядок «(без категорії)». Стовпці: root, revenue.

24. Глибина дерева категорій. Скільки категорій на кожному рівні дерева? Корені мають рівень 1. За зростанням рівня. Стовпці: level, categories; порядок рядків обов’язковий.

25. Розділи з малою кількістю товарів. Які категорії (будь-якого рівня) мають разом з усіма вкладеними категоріями менше десяти товарів? Порожні категорії теж потрібні, з нулем. Стовпці: category_id, name, products.

Вікна і LATERAL

26. Динаміка виручки по місяцях. Для кожного місяця за київським часом покажіть виручку доставлених замовлень (total_amount), наростаючу виручку з початку року й зміну до попереднього місяця у відсотках з однією цифрою після коми. Для першого місяця зміна порожня. За зростанням місяця. Стовпці: month, revenue, running, change_pct; порядок рядків обов’язковий.

27. Топ-3 товарів у категорії. Які три товари продано найбільшою кількістю штук (quantity у нескасованих замовленнях) у кожній категорії? Товари без категорії пропустіть. Якщо кілька товарів ділять місце в трійці, виведіть усіх. Стовпці: category_id, product_id, units.

28. Повторні замовлення за добу. Знайдіть пари послідовних замовлень одного покупця, між якими минуло менше доби. Порядок замовлень — за placed_at, за рівного часу за order_id. Виведіть покупця, попереднє замовлення і наступне. Стовпці: customer_id, prev_order_id, order_id.

29. Три останні замовлення першої десятки. Для покупців із customer_id від 1 до 10 покажіть три останні замовлення кожного (за placed_at, за рівного часу за order_id). Покупець без замовлень має лишитися в результаті з порожніми полями замовлення. Стовпці: customer_id, order_id, total_amount.

Перед початком

Прочитайте модуль 3: логічний порядок виконання, LEFT JOIN з умовою в WHERE, NOT IN і NULL, рекурсивні CTE, віконні функції й LATERAL. Модуль 2 дає тризначну логіку NULL, яку завдання 03–04, 11, 14 і 17 проходять на практиці.
Розпакуйте архів курсу: потрібні labs/l01-sql-queries/ і setup/. Про середовище — на сторінці «Лабораторні».
Чернетки можна писати й у пісочниці без Docker, але check.sh потрібна база в контейнері.

Підніміть PostgreSQL з датасетом із кореня репозиторію й відкрийте psql:

docker compose --profile postgres up -d --wait
docker compose exec postgres psql -U shop -d shop

Етапи

Розминка: фільтри, NULL, дати (01–05). Перш ніж писати запит, подивіться на дані: скільки рядків у таблиці, які значення в потрібній колонці, чи є там NULL. Перевірка: ./check.sh 01 02 03 04 05.
З’єднання (06–11). Перед з’єднанням прикиньте, скільки рядків має вийти, і порівняйте з тим, що вийшло. Окремо подумайте, що має статися з рядками без пари. Перевірка: ./check.sh 06 07 08 09 10 11.
Агрегати і групи (12–16). Для кожного GROUP BY спитайте себе, чи бувають порожні групи й чи потрібні вони у відповіді, а чи є серед значень ключа NULL. Перевірка: ./check.sh 12 13 14 15 16.
Підзапити (17–22). Питання про «жодного» і «немає» напишіть двома способами й порівняйте кількість рядків. Якщо числа різні, один із запитів помиляється. Перевірка: ./check.sh 17 18 19 20 21 22.
CTE і рекурсія (23–25). Почніть із піддерева однієї категорії, переконайтеся, що обхід правильний, і лише тоді узагальнюйте на все дерево. Перевірка: ./check.sh 23 24 25.
Вікна і LATERAL (26–29). Випишіть на аркуші кілька рядків, які мають потрапити в результат, і визначте правило для рівних значень до того, як обирати функцію. Перевірка: ./check.sh 26 27 28 29.

Перевірка

cd labs/l01-sql-queries
./check.sh            # усі завдання
./check.sh 07 12      # лише вибрані

Перевірка нічого не змінює в базі. Вона запускається з каталогу лабораторної, підхоплює ../lib/check-lib.sh і звертається до контейнера postgres. Для кожного queries/NN.sql вона друкує ok або НІ з поясненням:

файл порожній чи тільки з коментарями: ще не написано;
запит не виконується: текст помилки PostgreSQL;
інша кількість рядків: очікували рядків: N, у вашому результаті: M і до трьох рядків, яких бракує чи які зайві;
ті самі рядки в іншому порядку (де порядок обов’язковий): окреме повідомлення про порядок.

Якщо результат розходиться, після пояснення друкується підказка, куди дивитися. У підсумку пройшло X, не пройшло Y. Якщо Docker не відповідає, скрипт так і скаже й завершиться, нічого не перевіривши.

Часті помилки

Запит виконується, але «очікували рядків: N». Порахуйте, скільки рядків у вас, і поставте поруч число, яке мало б вийти за здоровим глуздом: скільки покупців у Києві, скільки продавців у таблиці. Розбіжність майже завжди в рядках, які зникли на JOIN чи WHERE, або в рядках, які розмножилися.

Питання про «жодного» чи «немає». Перевірте запит на покупцеві, який має і підходящі, і непідходящі рядки: вони не повинні впливати один на одного. Знайдіть такого покупця у своєму результаті.

У файлі два запити чи команда psql. Перевірка загортає запит у COPY (...), тож \d, EXPLAIN чи кілька команд через ; її зламають. Залишайте один SELECT.

Не збігається лише порядок колонок. Перевірка порівнює колонки за позицією: якщо в переліку keep_id, drop_id, то перша колонка — keep_id.

Округлення й типи. round(avg(x), 2) дає numeric, avg(x)::float друкується інакше. Якщо всі рядки правильні, а перевірка каже «НІ», порівняйте, як psql друкує ваші числа й очікувані.

Час. Якщо «замовлень на цей день» на кілька штук менше чи більше, ніж мало б, згадайте, у якому часовому поясі зберігається placed_at і в якому живуть покупці.

Далі, якщо цікаво

Виконайте свої розв’язки на датасеті medium (як його завантажити, описано в setup/README.md) і знайдіть найповільніший. Це відправна точка для L4 і модуля 9. Вправа з одним питанням: напишіть завдання 07 трьома способами (NOT EXISTS, LEFT JOIN ... IS NULL, NOT IN з IS NOT NULL) і порівняйте плани через EXPLAIN.