For physical goods — shipping, cost, margin, returns all matter. More complex than SaaS. --- Тренируйте SQL — [откройте тренажёр](https://t.me/kariernik_bot/app?startapp=web_blog_sql-dlya-ecommerce-analitika) с 1500+ вопросами для собесов.

23 апреля 2026 г.

SQL для e-commerce аналитика

Карьерник — квиз-тренажёр в Telegram с 1500+ вопросами для собесов аналитика. SQL, Python, A/B, метрики. Бесплатно.

Зачем это знать

E-commerce — массивный sector analytics jobs. Wildberries, Ozon, Lamoda, Avito, Amazon — все нанимают. Специфические метрики: GMV, AOV, cart abandonment, repeat purchase.

На собесах в e-commerce SQL вопросы domain-oriented.

Ключевые таблицы

-- orders
(id, user_id, created_at, status, shipping_cost, discount)

-- order_items
(id, order_id, product_id, quantity, price)

-- products
(id, name, category, brand, cost)

-- users
(id, signup_at, email)

-- sessions
(id, user_id, started_at, device, utm_source)

GMV (Gross Merchandise Value)

Total value sold:

SELECT SUM(quantity * price) AS gmv
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
    AND o.created_at BETWEEN '2026-04-01' AND '2026-04-30';

Per day:

SELECT
    DATE(o.created_at) AS day,
    SUM(oi.quantity * oi.price) AS gmv
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY 1
ORDER BY 1;

AOV (Average Order Value)

SELECT
    AVG(order_total) AS aov
FROM (
    SELECT order_id, SUM(quantity * price) AS order_total
    FROM order_items
    GROUP BY order_id
) sub;

По сегментам:

SELECT
    device,
    AVG(order_total) AS aov,
    COUNT(DISTINCT order_id) AS orders
FROM (
    SELECT o.id AS order_id, o.device,
           SUM(oi.quantity * oi.price) AS order_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    GROUP BY o.id, o.device
) s
GROUP BY device;

Cart abandonment

WITH cart_events AS (
    SELECT user_id, session_id,
           MAX(CASE WHEN event = 'cart_add' THEN 1 ELSE 0 END) AS added_to_cart,
           MAX(CASE WHEN event = 'checkout_start' THEN 1 ELSE 0 END) AS started_checkout,
           MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM events
    WHERE created_at BETWEEN '2026-04-01' AND '2026-04-30'
    GROUP BY 1, 2
)
SELECT
    SUM(added_to_cart) AS carts_created,
    SUM(CASE WHEN added_to_cart AND NOT purchased THEN 1 ELSE 0 END) AS abandoned,
    SUM(CASE WHEN added_to_cart AND NOT purchased THEN 1 ELSE 0 END) * 100.0 /
        SUM(added_to_cart) AS abandonment_rate
FROM cart_events;

Repeat purchase rate

WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_buyers,
    COUNT(CASE WHEN order_count >= 2 THEN 1 END) AS repeat_buyers,
    COUNT(CASE WHEN order_count >= 2 THEN 1 END) * 100.0 / COUNT(*) AS repeat_rate
FROM user_orders;

Days between purchases

WITH ordered AS (
    SELECT
        user_id,
        created_at,
        LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order
    FROM orders
    WHERE status = 'completed'
)
SELECT
    AVG(EXTRACT(DAY FROM (created_at - prev_order))) AS avg_days_between
FROM ordered
WHERE prev_order IS NOT NULL;

Conversion rate

WITH session_outcomes AS (
    SELECT
        s.id AS session_id,
        s.user_id,
        MAX(CASE WHEN o.id IS NOT NULL THEN 1 ELSE 0 END) AS converted
    FROM sessions s
    LEFT JOIN orders o ON o.user_id = s.user_id
        AND o.created_at BETWEEN s.started_at AND s.started_at + INTERVAL '1 hour'
    WHERE s.started_at >= CURRENT_DATE - 30
    GROUP BY 1, 2
)
SELECT
    AVG(converted) * 100 AS session_cr
FROM session_outcomes;

Product metrics

Best-selling

SELECT
    p.name,
    p.category,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.price) AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'completed'
    AND o.created_at BETWEEN '2026-04-01' AND '2026-04-30'
GROUP BY p.id, p.name, p.category
ORDER BY revenue DESC
LIMIT 20;

Return rate per product

SELECT
    p.id, p.name,
    COUNT(DISTINCT CASE WHEN o.status = 'completed' THEN o.id END) AS orders,
    COUNT(DISTINCT CASE WHEN o.status = 'returned' THEN o.id END) AS returns,
    COUNT(DISTINCT CASE WHEN o.status = 'returned' THEN o.id END) * 100.0 /
        NULLIF(COUNT(DISTINCT o.id), 0) AS return_rate
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT o.id) > 50
ORDER BY return_rate DESC;

Product margin

SELECT
    p.name,
    AVG(oi.price) AS avg_selling_price,
    p.cost,
    AVG(oi.price) - p.cost AS margin_per_unit,
    (AVG(oi.price) - p.cost) / AVG(oi.price) * 100 AS margin_pct
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name, p.cost;

Customer LTV

WITH user_ltv AS (
    SELECT
        user_id,
        MIN(created_at) AS first_order,
        COUNT(*) AS total_orders,
        SUM(order_total) AS total_spent
    FROM orders_with_totals
    GROUP BY user_id
)
SELECT
    AVG(total_spent) AS ltv,
    AVG(total_orders) AS avg_orders_per_user
FROM user_ltv;

First-time vs repeat

WITH order_rank AS (
    SELECT
        id, user_id, order_total,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders
    WHERE status = 'completed'
)
SELECT
    CASE WHEN order_num = 1 THEN 'First-time' ELSE 'Repeat' END AS customer_type,
    COUNT(*) AS orders,
    AVG(order_total) AS aov,
    SUM(order_total) AS revenue
FROM order_rank
GROUP BY 1;

Categories analysis

SELECT
    p.category,
    SUM(oi.quantity * oi.price) AS revenue,
    COUNT(DISTINCT o.id) AS orders,
    COUNT(DISTINCT o.user_id) AS buyers,
    AVG(oi.quantity * oi.price) AS avg_line_value
FROM products p
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY revenue DESC;

Dashboard

E-commerce analyst typical dashboards:

GMV / revenue / orders
Conversion funnel (visit → cart → checkout → purchase)
AOV trend
Top products / categories
Customer segments (new vs returning)
Attribution per channel
Return rate
Inventory turnover (advanced)

На собесе

«Как бы measured e-commerce success?»

NSM often GMV или contribution margin.

Inputs: traffic, CR, AOV, repeat rate.

Drill: product mix, channels.

«Как investigate drop в sales?»

RCA: where drop? new vs repeat customers? specific categories? channels?

Связанные темы

FAQ

Gross vs Net GMV?

Gross — before returns. Net — after. Clarify definition.

AOV или ARPU?

AOV per order. ARPU per user. Related но different.

Unit economics?

For physical goods — shipping, cost, margin, returns all matter. More complex than SaaS.

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.

Тренироваться в Telegram