Data Science. SQL hub: “🖥 Довольно сложная задача по SQL: Анализ продаж с использованием окон…”

🖥 Довольно сложная задача по SQL: Анализ продаж с использованием оконных функций и вложенных подзапросов

🌟 Допустим, у вас есть следующие таблицы с данными о продажах и товарах:

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  product_id INT,
  sale_date DATE,
  sale_amount INT
);

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  category VARCHAR(50),
  price DECIMAL(10, 2)
);

🌟 Таблица sales содержит информацию о продажах: ID продажи (sale_id), ID продукта (product_id), дата продажи (sale_date) и количество проданных единиц (sale_amount)
🌟 Таблица products хранит данные о продуктах: ID продукта (product_id), категория продукта (category) и цена (price)

❓ Нужно создать запрос, который выполнит следующие действия:

🌟 Найти самую популярную категорию товаров по количеству продаж в каждом месяце.
🌟 Вывести результаты по месяцам, начиная с самого первого месяца продаж.
🌟 В каждой строке указать:
- Месяц (sale_month)
- Название категории (category)
- Общее количество проданных товаров в этой категории за месяц (total_sales)
- Разницу (difference) между текущими продажами категории и продажами этой же категории в предыдущем месяце. Если предыдущего месяца нет, вывести NULL

❗️ Решение:

WITH MonthlySales AS (
  SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
    p.category,
    SUM(sale_amount) AS total_sales
  FROM sales s
  JOIN products p ON s.product_id = p.product_id
  GROUP BY DATE_FORMAT(sale_date, '%Y-%m'), p.category
),
RankedCategories AS (
  SELECT 
    sale_month,
    category,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY sale_month ORDER BY total_sales DESC) AS sales_rank
  FROM MonthlySales
),
PopularCategories AS (
  SELECT 
    sale_month,
    category,
    total_sales
  FROM RankedCategories
  WHERE sales_rank = 1
),
CategoryWithDifference AS (
  SELECT 
    sale_month,
    category,
    total_sales,
    LAG(total_sales, 1) OVER (PARTITION BY category ORDER BY sale_month) AS previous_sales
  FROM PopularCategories
)
SELECT 
  sale_month,
  category,
  total_sales,
  total_sales - previous_sales AS difference
FROM CategoryWithDifference
ORDER BY sale_month;

💡 Как это работает:

🌟 MonthlySales: Подзапрос агрегирует данные продаж по месяцам и категориям товаров, чтобы получить общее количество продаж (total_sales) в каждом месяце.
🌟 RankedCategories: Присваивает каждой категории её ранг (ROW_NUMBER()) в зависимости от количества продаж в месяц.
🌟 PopularCategories: Фильтрует только самую популярную категорию (с sales_rank = 1) для каждого месяца.
🌟 CategoryWithDifference: Использует оконную функцию LAG() для расчета разницы между продажами в текущем месяце и предыдущем

@sqlhub