SQL: Расчет среднего размера корзины

Решение

Это задача на анализ параметров заказов (basket size) — критична для e-commerce аналитики и оптимизации среднего чека.

Логика решения:

1. Группируем товары по заказам — считаем количество товаров и сумму в каждом заказе
2. Рассчитываем общие метрики — среднее количество товаров и средний чек
3. Строим распределение — сколько заказов с 1 товаром, 2 товарами и т.д.

SQL запрос для общих метрик:

WITH order_stats AS (
  SELECT 
    order_id,
    SUM(quantity) as basket_size,
    ROUND(SUM(quantity * price), 2) as order_value
  FROM order_items
  GROUP BY order_id
)
SELECT 
  COUNT(*) as total_orders,
  ROUND(AVG(basket_size), 2) as avg_basket_size,
  ROUND(AVG(order_value), 2) as avg_order_value,
  MIN(basket_size) as min_basket_size,
  MAX(basket_size) as max_basket_size,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY basket_size), 2) as median_basket_size,
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY basket_size), 2) as p75_basket_size,
  ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY basket_size), 2) as p95_basket_size
FROM order_stats;

SQL запрос для распределения по размеру корзины:

WITH order_stats AS (
  SELECT 
    order_id,
    SUM(quantity) as basket_size,
    SUM(quantity * price) as order_value
  FROM order_items
  GROUP BY order_id
),
basket_buckets AS (
  SELECT 
    CASE 
      WHEN basket_size = 1 THEN '1_item'
      WHEN basket_size = 2 THEN '2_items'
      WHEN basket_size BETWEEN 3 AND 4 THEN '3_4_items'
      WHEN basket_size BETWEEN 5 AND 10 THEN '5_10_items'
      ELSE '11plus_items'
    END as basket_category,
    COUNT(*) as orders_count,
    ROUND(AVG(order_value), 2) as avg_order_value
  FROM order_stats
  GROUP BY basket_category
)
SELECT 
  basket_category,
  orders_count,
  ROUND(100.0 * orders_count / SUM(orders_count) OVER (), 2) as pct_of_orders,
  SUM(orders_count) OVER (ORDER BY basket_category) as cumulative_orders,
  avg_order_value
FROM basket_buckets
ORDER BY orders_count DESC;

Детальное распределение (по каждому размеру):

WITH order_stats AS (
  SELECT 
    order_id,
    SUM(quantity) as basket_size,
    SUM(quantity * price) as order_value
  FROM order_items
  GROUP BY order_id
)
SELECT 
  basket_size,
  COUNT(*) as orders_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct_of_orders,
  SUM(COUNT(*)) OVER (ORDER BY basket_size) as cumulative_orders,
  ROUND(SUM(COUNT(*)) OVER (ORDER BY basket_size) * 100.0 / SUM(COUNT(*)) OVER (), 2) as cumulative_pct,
  ROUND(AVG(order_value), 2) as avg_order_value,
  SUM(order_value) as total_revenue
FROM order_stats
GROUP BY basket_size
ORDER BY basket_size;

Анализ по отделам/категориям:

WITH order_stats AS (
  SELECT 
    oi.order_id,
    p.category,
    SUM(oi.quantity) as basket_size,
    SUM(oi.quantity * oi.price) as order_value,
    COUNT(DISTINCT oi.product_id) as unique_products
  FROM order_items oi
  LEFT JOIN products p ON oi.product_id = p.product_id
  GROUP BY oi.order_id, p.category
)
SELECT 
  category,
  COUNT(*) as orders_count,
  ROUND(AVG(basket_size), 2) as avg_basket_size,
  ROUND(AVG(order_value), 2) as avg_order_value,
  ROUND(AVG(unique_products), 2) as avg_unique_products,
  SUM(order_value) as total_revenue
FROM order_stats
WHERE category IS NOT NULL
GROUP BY category
ORDER BY total_revenue DESC;

Когортный анализ (по датам заказов):

WITH order_stats AS (
  SELECT 
    DATE_TRUNC('week', oi.order_date) as order_week,
    oi.order_id,
    SUM(oi.quantity) as basket_size,
    SUM(oi.quantity * oi.price) as order_value
  FROM order_items oi
  GROUP BY order_week, oi.order_id
)
SELECT 
  order_week,
  COUNT(*) as orders_count,
  ROUND(AVG(basket_size), 2) as avg_basket_size,
  ROUND(AVG(order_value), 2) as avg_order_value,
  SUM(order_value) as total_revenue
FROM order_stats
GROUP BY order_week
ORDER BY order_week DESC;

Ожидаемый результат (типичный пример):

ОБЩИЕ МЕТРИКИ:
Total Orders: 10,000
Avg Basket Size: 2.8 товаров
Avg Order Value: $45.50
Median Basket Size: 2 товара
P95 Basket Size: 6 товаров

РАСПРЕДЕЛЕНИЕ:
Basket Size | Orders | % | Avg Value
1 item      | 3,500  | 35% | $25.00
2 items     | 2,800  | 28% | $38.50
3-4 items   | 2,200  | 22% | $55.00
5-10 items  | 1,200  | 12% | $75.00
11+ items   | 300    | 3%  | $120.00

Ключевые метрики:

1. Average Basket Size — среднее количество товаров в заказе (цель: увеличивать cross-sell)
2. Average Order Value (AOV) — средний чек (главная метрика выручки)
3. Basket Size Distribution — как распределены заказы (важно для планирования логистики)

Применение в бизнесе:

• Cross-sell стратегия — если avg basket size = 1.5, нужны рекомендации "часто покупают вместе"
• Bundle предложения — упаковки из 2-3 товаров с скидкой (lift AOV на 20-30%)
• Free shipping порог — установить так, чтобы 60% заказов его достигали
• Прогноз логистики — зная распределение, можно планировать упаковки, доставку
• Тесты AOV — A/B тесты рекомендаций могут поднять AOV на 5-10%