Стажування в проєкті (PostgreSQL)

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

Набір даних, який використовувався, походить з Kaggle: Найбільший набір даних електронної комерції Пакистану, з деякими модифікаціями.

Використані набори даних:

1. order_detail
2. sku_detail
3. customer_detail
4. payment_detail

Опис наборів даних:

order_detail Таблиця:

1. id → Унікальний ідентифікатор замовлення.
2. customer_id → Унікальний ідентифікатор клієнта.
3. order_date → Дата транзакції.
4. sku_id → Унікальний ідентифікатор продукту (SKU = одиниця збереження запасів).
5. price → Ціна, зазначена на етикетці товару.
6. qty_ordered → Кількість товарів, яку придбав клієнт.
7. before_discount → Загальна ціна продукту (price * qty_ordered).
8. discount_amount → Загальна сума знижки для товару.
9. after_discount → Загальна ціна продукту після знижки.
10. is_gross → Вказує на те, що клієнт ще не оплатив замовлення.
11. is_valid → Вказує на те, що клієнт здійснив платіж.
12. is_net → Вказує на те, що транзакція завершена.
13. payment_id → Унікальний ідентифікатор способу оплати.

sku_detail Таблиця:

1. id → Унікальний ідентифікатор продукту (використовується як ключ для з'єднань).
2. sku_name → Назва продукту.
3. base_price → Ціна, зазначена на етикетці товару.
4. cogs → Собівартість проданого товару (вартість продажу однієї одиниці продукту).
5. category → Категорія продукту.

customer_detail Таблиця:

1. id → Унікальний ідентифікатор клієнта.
2. registered_date → Дата реєстрації клієнта як учасника.

payment_detail Таблиця:

1. id → Унікальний ідентифікатор способу оплати.
2. payment_method → Спосіб оплати.

Використовуючи вищезгадані набори даних, ми можемо виконати наступний аналіз:

Питання 1:

У 2021 році в який місяць загальна сума транзакцій (after_discount) була найвищою? Використовуйте is_valid = 1 для фільтрації даних транзакцій.

transaksi (afterdiscount) paling besar? Gunakan isvalid = 1 untuk memfilter data transaksi.

-- No. 1  
SELECT  
 TO_CHAR(order_date, 'Month') month_2021,   
 ROUND(SUM(after_discount)) total_sales  
FROM   
 order_detail  
WHERE   
 EXTRACT(YEAR FROM order_date) = 2021  
 AND is_valid = 1  
GROUP BY 1  
ORDER BY 2 DESC;

Пояснення:

• TO_CHAR(order_date, 'Month'): Витягує місяць з колонки order_date у вигляді рядка (імена місяців).
• ROUND(SUM(after_discount)): Обчислює загальні продажі після знижок для всіх транзакцій, округлюючи їх до найближчого цілого числа.
• WHERE EXTRACT(YEAR FROM order_date) = 2021 AND is_valid = 1: Фільтрує транзакції, що відбулися у 2021 році та є дійсними.
• GROUP BY 1: Групує дані за першим стовпцем (місяць).
• ORDER BY 2 DESC: Сортує результати за другим стовпцем (total_sales) у спадному порядку.

Висновок:
Місяць з найвищою загальною сумою транзакцій (after_discount) у 2021 році — це листопад.

Питання 2:

У 2021 році в який місяць були найвищими такі показники:

• Загальна кількість унікальних клієнтів,
• Загальна кількість унікальних замовлень,
• Загальна кількість проданих товарів?

Використовуйте is_valid = 1 для фільтрації даних транзакцій.

SQL Запит:

SELECT   
 TO_CHAR(order_date, 'Month') AS month_2021,  
 COUNT(DISTINCT customer_id) AS total_customers,  
 COUNT(DISTINCT id) AS total_orders,  
 SUM(qty_ordered) AS total_quantity  
FROM   
 order_detail  
WHERE   
 EXTRACT(YEAR FROM order_date) = 2021  
 AND is_valid = 1  
GROUP BY 1  
ORDER BY 2 DESC;

Пояснення:

1. SELECT TO_CHAR(order_date, 'Month') AS month_2021 використовується для вибору стовпців для отримання. У цьому випадку вибраний стовпець — місяць у вигляді повної назви місяця у 2021 році, і він отримує псевдонім month_2021.
   2.
   COUNT(DISTINCT customer_id) AS total_customers використовується для обчислення кількості унікальних клієнтів (без дублікатів), які здійснили транзакції в кожному місяці 2021 року. Кількість унікальних клієнтів зберігається в новому стовпці з псевдонімом total_customers.
2. COUNT(DISTINCT id) AS total_orders використовується для обчислення кількості унікальних замовлень, які відбулися в кожному місяці 2021 року. Кількість унікальних замовлень зберігається в новому стовпці з псевдонімом total_orders.
3. SUM(qty_ordered) AS total_quantity використовується для підсумовування кількості проданих товарів у кожному місяці 2021 року. Загальна кількість товарів зберігається в новому стовпці з псевдонімом total_quantity.
4. FROM order_detail використовується для вказівки таблиці, з якої будуть отримані дані, в цьому випадку це таблиця order_detail.
5. WHERE EXTRACT(YEAR FROM order_date) = 2021 AND is_valid = 1 використовується для фільтрації даних, щоб включити тільки транзакції, які відбулися в 2021 році і мають значення is_valid рівне 1.
6. GROUP BY 1 використовується для групування результатів за першим стовпцем, яким є стовпець month_2021.
7. ORDER BY 2 DESC використовується для сортування результатів за другим стовпцем (total_customers) у спадному порядку.

Отже, цей SQL-запит виведе інформацію про кількість клієнтів, кількість замовлень і загальну кількість проданих товарів для кожного місяця 2021 року з таблиці order_detail, відсортовану за місяцем з найбільшою кількістю клієнтів.

Висновок:
Місяцем з найбільшою кількістю унікальних клієнтів, унікальних замовлень і проданих товарів у 2021 році є листопад.

Питання 3:

У транзакціях, що відбулися в 2022 році, яка категорія генерувала найвищу суму транзакцій? Використовуйте is_valid = 1 для фільтрації даних транзакцій.

-- No. 3  
SELECT   
 sku.category,   
 ROUND(SUM(after_discount)) total_sales  
FROM   
 order_detail ord  
LEFT JOIN   
 sku_detail sku   
 ON sku.id = ord.sku_id  
WHERE   
 EXTRACT(YEAR FROM order_date) = 2022   
 AND is_valid = 1  
GROUP BY 1  
ORDER BY 2 DESC;

Пояснення:

1. SELECT category, ROUND(SUM(after_discount)) AS total_sales використовується для вибору стовпця category з таблиці та обчислення суми after_discount з таблиці order_detail. Результат округлюється та отримує псевдонім total_sales.
2. LEFT JOIN sku_detail sku ON sku.id = ord.sku_id використовується для з'єднання таблиць order_detail та sku_detail на основі співпадіння стовпця id.
3. WHERE EXTRACT(YEAR FROM order_date) = 2022 AND is_valid = 1 використовується для фільтрації транзакцій, що відбулися в 2022 році з is_valid, рівним 1 (дійсні транзакції).
4. GROUP BY 1 використовується для групування результатів за категорією з таблиці sku_detail.
5. ORDER BY 2 DESC використовується для сортування результатів у спадному порядку на основі значення total_sales.

Використовуючи цей SQL-запит, ми можемо визначити, яка категорія товарів генерувала найбільшу суму транзакцій у 2022 році при умові is_valid = 1.

Висновок:
Категорія Мобільні телефони та планшети має найвищу суму транзакцій порівняно з іншими категоріями.

Питання 4:

Порівняйте значення транзакцій кожної категорії в 2021 та 2022 роках. Визначте, які категорії зазнали збільшення, а які — зменшення суми транзакцій з 2021 по 2022 рік. Використовуйте is_valid = 1 для фільтрації даних транзакцій.

-- No.
4  
WITH tab_total AS ( WITH tab_sales AS (  
 SELECT   
 sku.category AS category_name,  
 ROUND(SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 2021 THEN after_discount ELSE 0 END)) AS total_sales_2021,  
 ROUND(SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 2022 THEN after_discount ELSE 0 END)) AS total_sales_2022  
 FROM   
 order_detail ord  
 LEFT JOIN  
 sku_detail sku   
 ON sku.id = ord.sku_id  
 WHERE   
 is_valid = 1  
 GROUP BY 1  
 ORDER BY 2 DESC )  
SELECT  
 tab_sales.*,  
 total_sales_2022 - total_sales_2021 increase_sales  
FROM   
 tab_sales  
ORDER BY increase_sales DESC )  
SELECT * FROM tab_total;

Пояснення:

1. WITH використовується для створення тимчасової таблиці (tab_total або tab_sales), яка містить загальні продажі для кожної категорії в 2021 році. Ця тимчасова таблиця потім використовується в основному запиті для відображення загальних продажів за 2021 та 2022 роки для кожної категорії.
2. ROUND(SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 2021 THEN after_discount ELSE 0 END)) AS total_sales_2021: Це обчислює загальні продажі за 2021 рік для відповідної категорії продукту. Функція SUM підсумовує значення after_discount для рядків, що відповідають транзакціям у 2021 році. Оператор CASE забезпечує, щоб враховувалися тільки транзакції з 2021 року, а інші транзакції рахуються як нуль (0). Результат потім округлюється за допомогою функції ROUND.
3. LEFT JOIN sku_detail sku ON sku.id = ord.sku_id: Використовуючи LEFT JOIN, таблиця sku_detail приєднується, забезпечуючи включення всіх записів з таблиці sku_detail, навіть якщо немає відповідних рядків у таблиці order_detail. Якщо sku_id в таблиці order_detail не збігається з жодним id в таблиці sku_detail, відповідні значення з таблиці order_detail будуть NULL.
4. WHERE is_valid = 1: Це фільтрує лише дійсні транзакції.
5. GROUP BY 1: Це групує результати за першою колонкою, якою є стовпець категорії.
6. ORDER BY 2 DESC: Це сортує результати в спадному порядку за значенням у стовпці total_sales_2021.
7. tab_sales.*, total_sales_2022 - total_sales_2021: Це вибирає всі стовпці з таблиці tab_sales та обчислює різницю між загальними продажами в 2022 та 2021 роках, зберігаючи результат у стовпці increase_sales.
8. ORDER BY 4 DESC: Це сортує результати в спадному порядку за стовпцем increase_sales.

Використовуючи цей SQL-запит, ми можемо визначити, які категорії продуктів зазнали збільшення або зменшення суми транзакцій з 2021 по 2022 рік при умові is_valid = 1. Категорії, що зазнали збільшення у сумі транзакцій, будуть на вершині результатів запиту, а категорії з зменшенням — внизу.

Згідно з отриманими даними, категорії, які зазнали збільшення суми транзакцій з 2021 по 2022 рік:

1. Мобільні телефони та планшети
2. Жіноча мода
3. Розваги
4. Побутова техніка
5. Супермаркети
6. Обчислювальна техніка
7. Діти та малюки
8. Краса та догляд
9. Охорона здоров'я та спорт
10. Дім та інтер'єр
11. Школа та освіта

А категорії, що зазнали зменшення:

1. Чоловіча мода
2. Книги
3. Согхат
4. Інші

Питання 5:

Відобразіть топ-10 найменувань товарів (разом з їх категоріями) на основі суми транзакцій, що відбулися в 2022 році. Також відобразіть загальну кількість унікальних клієнтів, кількість унікальних замовлень та загальну кількість товарів. Використовуйте is_valid = 1 для фільтрації даних транзакцій.

-- No.
5  
SELECT   
 sku.sku_name,  
 sku.category,  
 ROUND(SUM(after_discount)) total_sales,  
 COUNT(DISTINCT ord.customer_id) AS total_customers,  
 COUNT(DISTINCT ord.id) AS total_orders,  
 SUM(qty_ordered) AS total_quantity  
FROM   
 order_detail ord  
LEFT JOIN   
 sku_detail sku   
 ON sku.id = ord.sku_id  
WHERE   
 EXTRACT(YEAR FROM order_date) = 2022   
 AND is_valid = 1  
GROUP BY   
 1,2  
ORDER BY 3 DESC  
LIMIT 10;

Пояснення:

1. SELECT sku.sku_name, sku.category: Цей запит вибирає стовпці з назвами продуктів і категоріями з таблиці sku_detail.
2. ROUND(SUM(afterdiscount)) AS totalsales: Обчислює загальні продажі для кожного товару, підсумовуючи значення стовпця after_discount з таблиці order_detail та округлюючи їх до найближчого цілого числа.
3. COUNT(DISTINCT ord.customerid) AS totalcustomers: Підраховує кількість унікальних клієнтів, використовуючи унікальні значення customer_id в таблиці order_detail.
4. COUNT(DISTINCT ord.id) AS total_orders: Підраховує кількість унікальних замовлень, використовуючи унікальні значення id в таблиці order_detail.
5. SUM(qtyordered) AS totalquantity: Підсумовує кількість замовлених товарів для кожного SKU, використовуючи значення стовпця qty_ordered в таблиці order_detail.
6. FROM orderdetail ord LEFT JOIN skudetail sku ON sku.id = ord.sku_id: Використовується LEFT JOIN для об'єднання таблиць order_detail та sku_detail на основі стовпця sku_id, щоб отримати деталі продукту.
7. WHERE EXTRACT(YEAR FROM orderdate) = 2022 AND isvalid = 1: Фільтрує дані, щоб включити лише транзакції, що відбулися в 2022 році і мають значення is_valid рівне 1 (дійсні транзакції).
8. GROUP BY 1,2: Групує результати за першими двома стовпцями, якими є sku_name та category.
9. ORDER BY 3 DESC: Сортує результати за третьою колонкою, якою є total_sales, у спадному порядку, щоб продукти з найбільшими продажами були на початку.
10. LIMIT 10: Обмежує результат до 10 рядків.

Отже, цей SQL-запит виводить топ-10 товарів за сумою транзакцій у 2022 році, разом із кількістю унікальних клієнтів, унікальних замовлень та загальною кількістю замовлених товарів для кожного SKU.

Висновок:
З таблиці можна визначити 10 продуктів з найбільшою кількістю транзакцій у 2022 році.

Питання 6:

Відобразіть топ-5 найбільш популярних способів оплати, що використовувалися в 2022 році (на основі кількості унікальних замовлень). Використовуйте is_valid = 1 для фільтрації даних транзакцій.

-- No. 6  
SELECT   
 pay.payment_method,  
 COUNT(DISTINCT ord.id) AS total_orders  
FROM   
 order_detail ord  
LEFT JOIN   
 payment_detail pay   
 ON pay.id = ord.payment_id  
WHERE   
 EXTRACT(YEAR FROM order_date) = 2022   
 AND is_valid = 1  
GROUP BY   
 1  
ORDER BY   
 2 DESC  
LIMIT 5;

Пояснення:

1. Спочатку ми вибираємо дані з таблиці order_detail, використовуючи умову WHERE з фільтром EXTRACT(YEAR FROM order_date) = 2022 для обмеження транзакціями, що відбулися в 2022 році, а також is_valid = 1 для забезпечення дійсності даних.
2. Потім ми групуємо дані за стовпцем payment_method за допомогою команди GROUP BY.
3. Далі ми сортуємо результати запиту за кількістю замовлень (total_orders) у спадному порядку за допомогою ORDER BY 2 DESC.
   Оскільки ми хочемо відобразити лише топ-5 результатів, ми використовуємо конструкцію LIMIT 5.

Отже, результати запиту покажуть топ-5 найбільш популярних способів оплати, що використовувалися в 2022 році, разом з кількістю унікальних замовлень для кожного способу оплати.

Питання 7:

Відсортуйте ці 5 продуктів на основі їхньої вартості транзакцій:
a. Samsung
b. Apple
c. Sony
d. Huawei
e. Lenovo
Використовуйте is_valid = 1 для фільтрації даних транзакцій.

Пояснення:

Запит групує продажі продуктів на основі назв продуктів, що містять конкретні ключові слова, такі як "Samsung", "Apple", "Sony", "Huawei" або "Lenovo", використовуючи функцію CASE та LIKE. Потім запит обчислює загальні продажі для кожного продукту у 2022 році та вставляє їх у тимчасову таблицю під назвою tab_sales.
Після цього запит вибирає дані з tab_sales, фільтруючи рядки, де стовпець 'product' не є NULL. Це робиться для того, щоб усунути рядки без категорій продуктів (оскільки вони не відповідають заданим ключовим словам). Нарешті, запит сортує дані за загальними продажами у спадному порядку за допомогою конструкції ORDER BY.

Питання 8:

Створіть порівняння значень прибутку за 2021 та 2022 роки для кожної категорії. Потім обчисліть відсоткову різницю в прибутку між 2021 та 2022 роками.
(Прибуток = afterdiscount — (cogs * qtyordered))
Використовуйте is_valid = 1 для фільтрації даних транзакцій.

-- No. 8  
WITH profit_table AS (  
 SELECT  
 sku.category,  
 EXTRACT(YEAR FROM order_date) AS year_order,  
 SUM(after_discount - (cogs * qty_ordered)) AS profit  
 FROM  
 order_detail ord  
 LEFT JOIN  
 sku_detail sku   
 ON sku.id = ord.sku_id  
 WHERE  
 is_valid = 1   
 AND (EXTRACT(YEAR FROM order_date) = 2021   
 OR EXTRACT(YEAR FROM order_date) = 2022)  
 GROUP BY  
 sku.category, year_order  
 ORDER BY  
 profit  
),  
year_profit AS (  
SELECT  
 category,  
 ROUND(SUM(CASE   
 WHEN (year_order = 2021) THEN profit   
 ELSE NULL   
 END)) AS year_2021,  
 ROUND(SUM(CASE   
 WHEN (year_order = 2022) THEN profit   
 ELSE NULL   
 END)) AS year_2022  
FROM  
 profit_table  
GROUP BY  
 category  
)  
SELECT  
 *,  
 ROUND(((year_2022 / year_2021) - 1) * 100) AS increase_profit  
FROM  
 year_profit  
ORDER BY  
 increase_profit DESC;

Цей запит є виразом Common Table Expression (CTE), що створює таблицю profit_table, яка містить категорії продуктів, роки транзакцій та загальні прибутки від транзакцій у 2021 та 2022 роках. Потім CTE profit_table використовується в іншому CTE під назвою year_profit, який генерує таблицю з категоріями продуктів, загальним прибутком за 2021 рік та загальним прибутком за 2022 рік.

Після цього обчислюється відсоткова різниця в прибутку між 2021 та 2022 роками за формулою ((year_2022 / year_2021) - 1) * 100, і результат відображається у стовпці increase_profit.

Нарешті, результат з CTE year_profit відображається, відсортований за відсотковою різницею в прибутку від найбільшого до найменшого.

Питання 9:

Відобразіть топ-5 SKU з найбільшим внеском у прибуток у 2022 році на основі категорії з найбільшим зростанням прибутку з 2021 по 2022 рік (на основі результатів з пункту 8). Використовуйте is_valid = 1 для фільтрації даних транзакцій.

-- No.
9  
WITH profit_table AS (  
 SELECT  
 ord.id,  
 sku.sku_name,  
 ord.after_discount - (cogs * qty_ordered) AS profit  
 FROM  
 order_detail ord  
 LEFT JOIN  
 sku_detail sku   
 ON sku.id = ord.sku_id  
 WHERE  
 is_valid = 1   
 AND (EXTRACT(YEAR FROM order_date) = 2021   
 OR EXTRACT(YEAR FROM order_date) = 2022)  
 AND sku.category = 'Women Fashion'  
)  
SELECT   
 sku_name,  
 SUM(profit) AS total_profit  
FROM   
 profit_table  
GROUP BY   
 sku_name  
ORDER BY   
 total_profit DESC  
LIMIT 5;

Цей запит має на меті відобразити топ-5 SKU з найбільшим внеском у прибуток у 2022 році на основі категорії з найбільшим зростанням прибутку з 2021 по 2022 рік, використовуючи фільтр is_valid = 1, щоб забезпечити обробку лише дійсних даних транзакцій.
Спочатку створюється вираз Common Table Expression (CTE) під назвою profit_table, що містить ID замовлення, назву SKU та прибуток, обчислений за формулою after_discount - (cogs * qty_ordered). Дані отримуються тільки за 2021 та 2022 роки, і обробляються лише SKU з категорії «Жіноча мода».
Далі виконується запит для вибору sku_name та total_profit, обчисленого за допомогою функції SUM на таблиці profit_table. Дані групуються за sku_name і сортуються за total_profit у спадному порядку. Використовується функція LIMIT для відображення лише перших 5 результатів.

Отже, цей запит генерує список з топ-5 SKU з найбільшим внеском у прибуток у 2022 році в категорії «Жіноча мода».

Питання 10:

Відобразіть кількість унікальних замовлень, використовуючи топ-5 способів оплати (з питання 6) за категоріями продуктів у 2022 році. Використовуйте is_valid = 1 для фільтрації даних транзакцій.

-- No. 10  
SELECT  
 sku.category,  
 COUNT(DISTINCT CASE WHEN pay.payment_method = 'cod' THEN ord.id END) AS cod,  
 COUNT(DISTINCT CASE WHEN pay.payment_method = 'Easypay' THEN ord.id END) AS easypay,  
 COUNT(DISTINCT CASE WHEN pay.payment_method = 'Payaxis' THEN ord.id END) AS payaxis,  
 COUNT(DISTINCT CASE WHEN pay.payment_method = 'customercredit' THEN ord.id END) AS customercredit,  
 COUNT(DISTINCT CASE WHEN pay.payment_method = 'jazzwallet' THEN ord.id END) AS jazzwallet  
FROM   
 order_detail AS ord  
LEFT JOIN   
 payment_detail pay   
 ON pay.id = ord.payment_id  
LEFT JOIN   
 sku_detail sku   
 ON sku.id = ord.sku_id  
WHERE   
 is_valid = 1   
 AND EXTRACT(YEAR FROM order_date) = 2022   
GROUP BY   
 sku.category  
ORDER BY   
 cod DESC;

Цей запит використовується для обчислення кількості унікальних замовлень, використовуючи топ-5 способів оплати (Готівка при доставці, Easypay, Payaxis, Customer Credit, Jazzwallet) за категоріями продуктів у 2022 році.

Спочатку виконується LEFT JOIN між таблицями order_detail, payment_detail і sku_detail, щоб забезпечити включення даних з таблиці order_detail, навіть якщо немає відповідних даних у таблицях payment_detail або sku_detail. Потім застосовується фільтр is_valid = 1 і вибираються лише дані з 2022 року.

Далі дані групуються (GROUP BY) за категорією продуктів з таблиці sku_detail, і обчислюється кількість унікальних замовлень (COUNT DISTINCT) для кожного способу оплати за допомогою конструкції CASE WHEN. Остаточні результати сортуються (ORDER BY) за кількістю унікальних замовлень для способу оплати «Готівка при доставці» в спадному порядку.

З таблиці ми можемо побачити загальну кількість замовлень, що використовували топ-5 способів оплати для кожної категорії продуктів. При сортуванні за способом оплати «Готівка при доставці», категорія «Чоловіча мода» має найбільшу кількість замовлень, а категорія «Книги» — найменшу.

===============================================================

Це мій аналіз, заснований на 10 питаннях щодо бізнесу електронної комерції, що надається. Дякую за прочитання, сподіваюся, що він буде корисним для всіх 🙂

Перекладено з: Project Intern (PostgreSQL)