KPI в SQL. 18 задач для продуктовых отчётов

Глеб Зайцев

KPI в SQL. 18 задач для продуктовых отчётов

Перед первой задачей

Почему KPI опасно считать на глаз

Метрика кажется простой, пока не приходится объяснять, какие строки попали в числитель и знаменатель.

Конверсия зависит от уровня пользователя, средний чек — от статуса заказа, LTV — от окна наблюдения, а маржа — от себестоимости.

Эта книга тренирует привычку сначала формулировать бизнес-правило, а уже потом писать SQL-запрос.

Как устроены задачи

Каждая задача показывает маленькую таблицу, рабочий вопрос, запрос и ожидаемый результат.

Данные специально короткие: так проще увидеть, где метрика ломается из-за дублей, NULL, периода, отмены или неверного уровня группировки.

SQLite выбран как переносимая учебная среда. В продакшене функции дат могут отличаться, но логика расчёта KPI остаётся той же.

Маршрут по задачам

1. Посчитать конверсию визитов в оплату по дням

2. Средний чек только по оплаченным заказам

3. ARPU среди активных пользователей

4. Доля повторных покупателей

5. Доля категории в выручке

6. ROAS и окупаемость канала

7. Доля возвратов среди завершённых продаж

8. Воронка: просмотр, корзина, оплата

9. D1 retention по дню установки

10. LTV за первые семь дней

11. Клиенты без возврата в течение 14 дней

12. Маржа по товарам после себестоимости

13. Дни без остатка при наличии спроса

14. SLA доставки цифрового товара

15. Когортная выручка по месяцу первой покупки

16. Доля поисков без результата

17. Сравнить план и факт по выручке

18. Финальная строка дневного KPI

Практические задачи

Задача 1. Посчитать конверсию визитов в оплату по дням

Рабочий вопрос

Нужно сравнить уникальных посетителей и уникальных оплативших пользователей по каждому дню без смешивания новых и оплаченных заказов.

Данные

CREATE TABLE visits(day TEXT, user_id INTEGER);

CREATE TABLE orders(day TEXT, user_id INTEGER, status TEXT, total INTEGER);

INSERT INTO visits VALUES

('2026-09-01',1),('2026-09-01',2),('2026-09-01',3),

('2026-09-02',2),('2026-09-02',4);

INSERT INTO orders VALUES

('2026-09-01',1,'paid',500),

('2026-09-01',2,'new',300),

('2026-09-02',2,'paid',700),

('2026-09-02',5,'paid',900);

Запрос

WITH visitors AS (

SELECT day, COUNT(DISTINCT user_id) AS visitors

FROM visits

GROUP BY day

),

buyers AS (

SELECT o.day, COUNT(DISTINCT o.user_id) AS paid_buyers

FROM orders o

JOIN visits v ON v.day = o.day

AND v.user_id = o.user_id

WHERE o.status = 'paid'

GROUP BY o.day

)

SELECT v.day,

v.visitors,

COALESCE(b.paid_buyers, 0) AS paid_buyers,

ROUND(COALESCE(b.paid_buyers, 0) * 100.0 / v.visitors, 2) AS conversion_pct

FROM visitors v

LEFT JOIN buyers b ON b.day = v.day

ORDER BY v.day;

Ожидаемый результат

[["2026-09-01", 3, 1, 33.33], ["2026-09-02", 2, 1, 50.0]]

Почему работает

Посетители считаются уникально внутри дня.

В buyers попадают только paid-заказы.

LEFT JOIN сохраняет день с визитами даже при нуле оплат.

Где легко ошибиться

Если считать все заказы без статуса paid, конверсия 1 сентября станет завышенной из-за нового неоплаченного заказа.

Самостоятельная проверка

Почему пользователь 5 не увеличивает число посетителей 2 сентября?

Ответ: В этой метрике знаменатель берётся из таблицы visits, а пользователя 5 там нет.

Задача 2. Средний чек только по оплаченным заказам

Рабочий вопрос

Нужно посчитать количество оплат, выручку и средний чек, исключив отменённые и новые заказы из денежной метрики.

Данные

CREATE TABLE orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES

(1,'paid',500),

(2,'paid',700),

(3,'cancelled',900),

(4,'paid',300),

(5,'new',1000);

Запрос

SELECT COUNT(*) AS paid_orders,

SUM(total) AS revenue,

ROUND(AVG(total), 2) AS average_order_value

FROM orders

WHERE status = 'paid';

Ожидаемый результат

[[3, 1500, 500.0]]

Почему работает

Фильтр status = paid оставляет три заказа.

Выручка равна 500 + 700 + 300.

Средний чек считается как 1500 / 3.

Где легко ошибиться

Отменённый заказ на 900 и новый заказ на 1000 сделали бы средний чек красивее, но не отражали бы реальные деньги.

Самостоятельная проверка

Почему здесь AVG(total) безопасен только после фильтра paid?

Ответ: Без фильтра среднее смешало бы оплаченные, новые и отменённые заказы.

Задача 3. ARPU среди активных пользователей

Рабочий вопрос

Нужно посчитать выручку на одного активного пользователя, учитывая только посетителей выбранного периода.

Данные

CREATE TABLE active_users(user_id INTEGER);

CREATE TABLE orders(user_id INTEGER, status TEXT, total INTEGER);

INSERT INTO active_users VALUES (1),(2),(3),(4);

INSERT INTO orders VALUES

(1,'paid',500),

(2,'paid',700),

(2,'paid',300),

(5,'paid',900),

(3,'new',400);

Запрос

WITH revenue AS (

SELECT SUM(o.total) AS paid_revenue

FROM active_users a

JOIN orders o ON o.user_id = a.user_id

WHERE o.status = 'paid'

)

SELECT COUNT(*) AS active_users,

COALESCE((SELECT paid_revenue FROM revenue), 0) AS paid_revenue,

ROUND(COALESCE((SELECT paid_revenue FROM revenue), 0) * 1.0 / COUNT(*), 2) AS arpu

FROM active_users;

Ожидаемый результат

[[4, 1500, 375.0]]

Почему работает

Активных пользователей четыре.

Оплаченная выручка активных пользователей равна 1500.

ARPU равен 1500 / 4.

Где легко ошибиться