Метрики
маркетплейса:
GMV, NMV, take rate

SELECT
  DATE_TRUNC('month', order_date)        AS month,
  status,
  COUNT(*)                               AS orders,
  ROUND(SUM(amount), 0)               AS gmv,
  -- доля статуса в GMV месяца
  ROUND(100.0 * SUM(amount)
    / SUM(SUM(amount)) OVER (
        PARTITION BY DATE_TRUNC('month', order_date)
      ), 1)                               AS gmv_share_pct
FROM sandbox.orders
GROUP BY 1, 2
ORDER BY month, gmv DESC;

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount)                    AS gmv,
    SUM(CASE WHEN status = 'cancelled'
             THEN amount ELSE 0 END) AS cancelled_gmv,
    SUM(CASE WHEN status = 'refunded'
             THEN amount ELSE 0 END) AS refunded_gmv,
    SUM(CASE WHEN status = 'completed'
             THEN amount ELSE 0 END) AS nmv
  FROM sandbox.orders
  GROUP BY 1
)
SELECT
  month,
  ROUND(gmv, 0)                               AS gmv,
  ROUND(nmv, 0)                               AS nmv,
  ROUND(100.0 * cancelled_gmv / gmv, 1)      AS cancel_rate_pct,
  ROUND(100.0 * refunded_gmv / gmv, 1)       AS return_rate_pct,
  ROUND(100.0 * nmv / gmv, 1)               AS nmv_rate_pct  -- NMV / GMV
FROM monthly
ORDER BY month;

-- Считаем условный take rate: комиссия = фиксированный % от NMV по категории
-- В реальности take rate = сумма всех revenue-потоков / NMV
WITH category_nmv AS (
  SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price)          AS nmv,
    COUNT(DISTINCT o.order_id)              AS orders,
    COUNT(DISTINCT p.seller_id)             AS active_sellers,
    ROUND(AVG(oi.unit_price), 0)           AS avg_item_price
  FROM sandbox.orders o
  JOIN sandbox.order_items oi ON o.order_id = oi.order_id
  JOIN sandbox.products p    ON oi.product_id = p.product_id
  WHERE o.status = 'completed'
  GROUP BY p.category
)
SELECT
  category,
  ROUND(nmv / 1000, 0)                  AS nmv_k,
  orders,
  active_sellers,
  avg_item_price,
  -- Доля категории в общем NMV
  ROUND(100.0 * nmv / SUM(nmv) OVER(), 1) AS nmv_share_pct,
  -- NMV на одного продавца
  ROUND(nmv / active_sellers, 0)         AS nmv_per_seller
FROM category_nmv
ORDER BY nmv DESC;

-- Прокси match rate: конверсия из просмотра товара в покупку
-- view_item → purchase — по каждой категории
WITH views AS (
  SELECT
    e.user_id,
    p.category,
    DATE_TRUNC('month', e.event_time) AS month,
    MAX(CASE WHEN e.event_name = 'view_item'
             THEN 1 ELSE 0 END)        AS had_view,
    MAX(CASE WHEN e.event_name = 'purchase'
             THEN 1 ELSE 0 END)        AS had_purchase
  FROM sandbox.events e
  JOIN sandbox.orders o
    ON e.user_id = o.user_id
    AND DATE_TRUNC('day', e.event_time) = DATE_TRUNC('day', o.order_date)
    AND o.status = 'completed'
  JOIN sandbox.order_items oi ON o.order_id = oi.order_id
  JOIN sandbox.products p    ON oi.product_id = p.product_id
  GROUP BY 1, 2, 3
)
SELECT
  month,
  category,
  SUM(had_view)                              AS sessions_with_view,
  SUM(had_purchase)                          AS sessions_with_purchase,
  ROUND(100.0 * SUM(had_purchase)
    / NULLIF(SUM(had_view), 0), 1)         AS match_rate_pct
FROM views
GROUP BY 1, 2
ORDER BY month, match_rate_pct DESC;