Еще шесть лайфхаков SQL

Время чтения 8 минут

Приветствую специалистов по данным!

Я Павел Беляев, тимлид группы обработки данных в дата-офисе сервиса eLama. Мы разрабатываем и поддерживаем витрины данных, используя в основном язык SQL. В предыдущей статье я уже приводил несколько приемчиков, которые мы используем в работе, а теперь решил дополнить их еще одной порцией. Новый набор лайфхаков может быть особенно полезен начинающим аналитикам.

Два последних месяца

Начинаем с простого. Положим, у нас есть таблица, содержащая временной ряд. Мы хотим вытащить строки, соответствующие двум последним месяцам. Если дата содержится в столбце date_field в формате ‘2025-01-01 00:00:00’, то в условии WHERE прописываем, что это поле должно быть больше, чем сегодняшняя дата минус два месяца.

ClickHouse

WHERE DATE(date_field) >= CURRENT_DATE() - toIntervalMonth(2)

BigQuery

WHERE DATE(date_ field) >= DATE_ADD(CURRENT_DATE(), INTERVAL -2 MONTH)

PostgreSQL

WHERE DATE(date_ field) >= date_add(CURRENT_DATE, '-2 months')

Количество дней в месяце

Это бывает нужно для расчета среднедневных метрик для каждого месяца в году. Отдельной функции для вычисления количества дней в месяце нет, но можно схитрить.

ClickHouse

SELECT toDayOfMonth(toLastDayOfMonth(CURRENT_DATE()))

BigQuery

SELECT EXTRACT(DAY FROM LAST_DAY(CURRENT_DATE()))

PostgreSQL

Здесь решение не так изящно, потому что нет функции определения последней даты месяца. Поэтому к началу месяца прибавим 1 месяц и вычтем 1 день.

SELECT DATE_PART('days',
DATE_TRUNC('month', CURRENT_DATE) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL)

Новые и старые пользователи

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


SELECT period,
    CASE
        WHEN period=first_period THEN 'new'
        WHEN period>first_period THEN 'existing'
        ELSE 'error'
    END AS user_category,
    COUNT(DISTINCT user_id) AS users
FROM
(
    SELECT user_id, period,
        MIN(period) OVER (PARTITION BY user_id) AS first_period
    FROM datamart.financial_activity
)
GROUP BY ALL
ORDER BY 1 DESC, 2

Расчет ARPPU

Раз уж заговорили о финансовой активности пользователей, давайте рассчитаем Average Revenue Per Paying User — среднюю прибыль от одного платящего пользователя за период.


SELECT period,
    SUM(revenue)/COUNT(DISTINCT user_id) AS arppu
FROM datamart.financial_activity
GROUP BY 1 
ORDER BY 1 DESC

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

Топовые юзеры

А теперь выясним, кто же главные герои, которые делают нам кассу. Если они уходят, мы получаем заметное проседание доходов. И наоборот, если доходы просели, имеет смысл выяснить, как дела у наших клиентов, не перестали ли пользоваться нашими услугами.


SELECT *
FROM
(
   SELECT d, w, user_id, revenue,
      ROW_NUMBER() OVER (PARTITION BY d ORDER BY revenue DESC) AS rn   
   FROM
   (
      SELECT DATE(date_paid) AS d, 
        dateName('weekday', date_payed) AS w,
        user_id, 
        SUM(revenue) AS revenue
      FROM datamart.financial_activity
      GROUP BY 1,2,3
   )
)
WHERE rn<=15 -- Количество топовых юзеров за каждый день
   AND w='Wednesday' -- День недели. Часто пользователи активничают ритмично, например по средам.
ORDER BY d DESC, revenue DESC

Отчет-воронка

Воронка — можно сказать, классический вариант отчета для анализа эффективности любого процесса, который можно разделить на этапы. Зачастую данные о разных этапах хранятся в разных таблицах, которые могут заполняться разными путями. Чтобы построить воронку, исследуемый объект, проходящий по ней, должен иметь общий ключ во всех этих таблицах. Например, это может быть идентификатор пользователя (user_id). А дальше дело за малым: обращаемся к таблице первого шага и приджойниваем таблицы остальных шагов.

Положим, в таблице datamart.registrations у нас хранятся зарегистрированные в сервисе eLama пользователи и UTM-метки, с которых они пришли на наш сайт. После регистрации пользователь должен создать в личном кабинете аккаунт системы, с которой планирует работать, это второй шаг, а все аккаунты хранятся в таблице accounts. Третьим — и самым интересным для бизнеса — шагом является начало финансовой активности, то есть появление пользователя в таблице financial_activity. Простейший отчет-воронка об эффективности рекламных кампаний может выглядеть так:


SELECT utm_source, utm_medium, utm_campaign,
    COUNT(r.user_id) AS regs,
    COUNT(a.user_id) AS add_accounts,
    COUNT(f.user_id) AS paid
FROM
(
    SELECT utm_source, utm_medium, utm_campaign, user_id
    FROM datamart.registrations
) AS r
 
LEFT JOIN 
(
    SELECT DISTINCT user_id
    FROM datamart.accounts
) AS a ON a.user_id=r.user_id
 
LEFT JOIN
(
    SELECT DISTINCT user_id
    FROM datamart. financial_activity
) AS f ON f.user_id=a.user_id
GROUP BY ALL
ORDER BY 1,2,3

Резюме

Мы рассмотрели несколько приемов и приемчиков SQL для аналитиков данных:

как вытащить из таблицы с временными метками строки за последние два месяца (или любой другой период);
как вычислить количество дней в произвольном месяце;
как разделить пользователей на старых и новых;
как рассчитать ARPPU;
как посмотреть активность топовых юзеров;
как построить простейший отчет-воронку.