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

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

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

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

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

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

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

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

SQLite выбран как переносимая учебная среда. В продакшене функции дат могут отличаться, но логика расчёта 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.