Мир аналитика данных: “Задача с собеседования: Анализ заказов по годам и категориям 🍁 Призна…”

Задача с собеседования: Анализ заказов по годам и категориям

🍁 Признаюсь, начало сентября выдалось непростым. Двое детей-школьников, возвращение с летней дачи, бесконечный список дел... Кажется, я только сейчас начала приходить в себя и наконец-то выдыхаю!

Так как вам нравятся задачки с собесов, то нашла вам еще одну очень интересную задачку на SQL с собеседования. Давайте разберем ее по шагам!

❗️Задача: Вывести количество заказов за 2021 год в категории «Одежда» и за 2022 год в категории «Обувь» (итоговый вид: 2 строки. Поля: yr, order_num)

Есть две таблицы:

➖ orders — информация о заказах (дата, id пользователя, id заказа, id товара)
➖ products — информация о товарах (id товара, категория)

Сначала сгенерируем датафремы, для отработки кода в Юпитере:

import pandas as pd
data = {
    'order_date': [
        '2021-01-01 00:00:01',
        '2021-01-01 00:00:01',
        '2022-01-01 00:00:01',
        '2024-01-01 00:00:01',
        '2024-02-05 17:14:22',
        '2024-04-10 04:20:59',
        '2021-06-21 10:30:26'],
    'user_id': [1, 1, 1, 1, 1, 2, 2],
    'order_id': [1, 1, 1, 1, 2, 3, 4],
    'product_id': [1, 2, 3, 4, 7, 4, 2],
}
orders = pd.DataFrame(data)

data2 = {
    'product_id': [1, 2, 3, 4, 5, 6, 7],
    'cat_1': [
        'Одежда',
        'Одежда',
        'Обувь',
        'Товары для дома',
        'Обувь',
        'Аксессуары',
        'Товары для красоты']
}
products = pd.DataFrame(data2)

💻 Шаг 1: Анализ данных
Сначала смотрим, что у нас в данных:

🟢В заказах есть повторяющиеся order_id (например, order_id=1 встречается 4 раза) — это значит, что в одном заказе может быть несколько товаров
🟢Нам нужно считать уникальные заказы (DISTINCT order_id), а не просто строки
🟢Даты нужно преобразовать в год для фильтрации

🛠 Шаг 2: План решения
🟢Соединить таблицы orders и products по product_id
🟢Извлечь год из даты заказа
🟢Отфильтровать данные:
Для 2021 года: только категория «Одежда»
Для 2022 года: только категория «Обувь»
🟢Посчитать уникальные заказы для каждого случая
🟢Объединить результаты в одну таблицу

WITH t1 AS (
    SELECT 
        SUBSTRING(o.order_date, 1, 4) AS year,  -- Извлекаем год из даты
        o.order_id,
        p.cat_1
    FROM orders o
    JOIN products p ON p.product_id = o.product_id  -- Соединяем таблицы
)

-- Запрос для одежды за 2021 год
SELECT 
    year AS yr,
    COUNT(DISTINCT order_id) AS order_num
FROM t1
WHERE cat_1 = 'Одежда' AND year = '2021'
GROUP BY year

UNION ALL

-- Запрос для обуви за 2022 год  
SELECT 
    year AS yr,
    COUNT(DISTINCT order_id) AS order_num
FROM t1
WHERE cat_1 = 'Обувь' AND year = '2022'
GROUP BY year

Простыми словами как считать:
2021, Одежда: order_id 1 (2 товара одежды) + order_id 4 (1 товар одежды) = 2 уникальных заказа
2022, Обувь: order_id 1 (1 товар обуви) = 1 заказ

💡 Хитрость задачи:
Ключевая особенность этой задачи — специфичный формат вывода. Обратите внимание, что в результате нужны именно две отдельные строки (не группировка по годам и категориям), причем:

⭐️Первая строка: только 2021 год и только категория "Одежда"
⭐️Вторая строка: только 2022 год и только категория "Обувь"

Хотя так и хочется забить на формат и вывести просто нужную инфу:

select 
count(distinct o.order_id) filter (where p.cat_1 ='Одежда' and substring(o.order_date,1,4)='2021' ) as order_num1,
count(distinct o.order_id) filter (where p.cat_1 ='Обувь' and substring(o.order_date,1,4)='2024') as order_num2
from orders o
join products p on p.product_id  =o.product_id

🈁⏪Задача проверяет не только знание SQL, но и внимательность к формату результата! ⏩👌