50 SQL запросов для аналитиков

Все запросы, которые мы будем тут приводить проверены в MySQL и PostgreSQL, а после небольших модификаций будут выполняться и в других СУБД.

Начнем с простых SQL-запросов и по мере продвижения будем усложнять. Чтобы не было скучно результаты некоторых запросов мы преобразовали в графики.

1. Общий, годовой, квартальный и месячный доходы

Одна из таблиц, которые есть в базе данных - это таблица orders с платежами, пусть в ней будет 4 столбца: id, дата, id пользователя и сумма платежа. В базе она может выглядеть так:

Сперва посчитаем сколько денег мы заработали за всё время. Аналитической пользы мало, но всегда приятно смотреть на большие числа:

SELECT SUM(amount) as total_revenue 
FROM orders

Для удобства можно сделать разбивку суммы на классы с помощью функции FORMAT в MySQL или to_char в PostgreSQL:

-- MySQL
SELECT FORMAT(SUM(amount), 0, 'ru_RU') as total_revenue 
FROM orders

-- PostgreSQL
SELECT to_char(SUM(amount), '99 999 999') as total_revenue 
FROM orders

Получили 82 077 608 рублей - неплохо

Оба SQL-запроса довольно простые, давайте что-то сложнее. Получим доход с разбивкой по годам:

SELECT 
    EXTRACT(YEAR FROM date) as year,
    SUM(amount) as year_revenue 
FROM orders
GROUP BY EXTRACT(YEAR FROM date)
ORDER BY year

В данном случае мы воспользовались группировкой по году с помощью GROUP BY Уже интересней, можно сравнивать разные периоды. Давайте сразу построим график.

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

SELECT 
    YEAR(date) as year,
    QUARTER(date) as quarter,
    SUM(amount) as revenue 
FROM orders
GROUP BY year
ORDER BY year

А теперь еще более детально. По году и месяцу.

SELECT 
    YEAR(date) as year,
    MONTH(date) as month,
    SUM(amount) as month_revenue 
FROM orders
GROUP BY year, month
ORDER BY year, month

Отлично, теперь информации больше. Посмотрим на график.

Очевиден рост, однако невооруженным взглядом видно, что иногда бывают просадки, а в некоторые месяцы хороших рост. Давайте разбираться, что еще можно получить из этих данных.

2. Доходы нарастающим итогом, ежемесячный прирост

А что если мы хотим посмотреть не просто месячные данные, а получить данные нарастающим итогом. Тут уже требуются решения посерьезней, а именно оконные функции:

SELECT 
    YEAR(date) as year, 
    MONTH(date) as month,
    SUM(amount) OVER(order by YEAR(date), MONTH(date)) as revenue
FROM transactions
GROUP BY year, month

3. Сравнение периодов

А давайте глянем, как один месяц отличался от другого. То есть насколько новый месяц был лучше или хуже предыдущего.

SELECT 
    YEAR(date) as year, 
    MONTH(date) as month,
    IFNULL(SUM(amount) - LAG(amount, 1, 0) 
        OVER(order by YEAR(date), MONTH(date)), 0) as diff
FROM transactions
GROUP BY year, month

А теперь тоже самое, но в процентах. То есть сравниваем прирост по каждому месяцу в процентах.

SELECT 
    YEAR(date) as year, 
    MONTH(date) as month,
    SUM(amount) * 100 / LAG(amount, 1) 
        OVER(order by YEAR(date), MONTH(date)) as percent
FROM transactions
GROUP BY year, month

Отлично. А что если нам сравнивать не рядом стоящие месяцы, а месяц одного года с тем же месяцем годом ранее. Возьмем выборку за текущий и предыдущий год.

SELECT 
    YEAR(date) as year, 
    MONTH(date) as month,
    IFNULL(SUM(amount) - LAG(amount, 12) 
        OVER(order by YEAR(date), MONTH(date)), 0) as diff
FROM transactions
GROUP BY year, month

4. Прогноз продаж

В прошлый раз вы заметили, что для последних месяцев данных нет, так как они еще не наступили, но давайте сделаем простейший прогноз продаж.

Логика такая, смотрим насколько % прошлый месяц вырос относительно этого же месяца в прошлом году, а затем ...

Такой подход не совсем корректный, так как доходы от месяца к месяцу могут сильно колебаться, особенно это критично для высокого сезона. Чтобы учесть и это давайте смотреть на средний рост последних трёх месяцев относительно этих же трёх месяцев за предыдущий период, а также будем учитывать какая динамика была между двумя периодами в прошлом году.

Итак, для июля 2021 года мы смотрим насколько выросли апрель, май, июнь 2021 относительно апреля, мая, июню 2020, а также насколько июль 2020 отличается от июль 2020. Запрос получается примерно таким:

А результат таким в виде таблицы

и таким в виде графика:

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

Разумеется на практике логика может быть еще более сложной и учитывать многие факторы, но данный SQL уже является неплохой отправной точкой. Хотя сам по себе он довольно сложный, так как содержит группировку, окна и подзапросы.

Давайте немного вернемся назад и посмотрим какие еще задачи можно решать с помощью SQL.

5. Средний чек

Средний чек – это выручка, деленная на количество покупок (оплаченных заказов):

SELECT 
    SUM(amount) /
    COUNT(*) as avg_check
FROM orders

Данный запрос просто демонстрирует основную формулу расчета среднего чека. В реальности используют функцию AVG:

SELECT 
    AVG(amount) as avg_check
FROM orders

Результат такой же. Текущий запрос возвращает общий средний чек за все периоды, это малоинформативно, лучше смотреть в динамике, скажем по месяцам. Напишем уже знакомый нам SQL запрос, но SUM заменим на AVG

SELECT 
    YEAR(date) as year,
    MONTH(date) as month,
    AVG(amount) as avg_check
FROM transactions
GROUP BY year, month
ORDER BY year, month

Если посмотреть на графике, то видно, что средний чек растет, для бизнеса это хорошо.

А что на счет дневного среднего чека? Например мы вводим новый товар или услугу и нам нужно получить оперативный отчет как эта услуга влияет на дневной чек. Ограничим данные последними 30 днями и выведем информацию по каждому дню.

SELECT 
    MONTH(date) as month,
    DAY(date) as day,
    AVG(amount) as avg_check
FROM transactions
WHERE date >= DATE_SUB(NOW(), INTERVAL 30 day)
GROUP BY month, day
ORDER BY month, day

Сразу построим график. Видно что 10 марта мы активно растем, и это потому что стали дополнительно предлагать к комплексному обеду ….

Кстати, давайте посчитаем на сколько мы растем в деньгах и процентах.

15% - неплохо.

6. Скользящее среднее

7. ARPU, ARPPU, DAU, MAU

8. LTV