Условие

Piwik трекает поведение пользователей внутреннего продукта. Поля:

page_url — URL события.
click_text — текст события.
user_holding_id — id внешнего клиента (если user_role != 'sr-admin').
user_role — роль (sr-admin или внешний).
unixtimedate — timestamp в Unix.
session_id — id сессии.
total_events — число событий.

Ответьте:

Q1: сколько у нас клиентов на основании этих данных?
Q2: сколько сессий в неделю в разбивке на internal (sr-admin) и external?
Q3: сколько кампаний создано успешно?

Решение

Подход

Разделяем «клиентов» (внешних, по user_holding_id), сессии группируем по WEEK(unixtimedate) и роли. «Успешно созданные кампании» — это user-action Action - Next Phase in Funnel/Funnel Completed (или подобный) на странице programmatic-campaigns. Точную семантику уточняем у PM, но код идентифицирует её через click_text.

Реализация

-- Q1: число внешних клиентов
SELECT COUNT(DISTINCT user_holding_id) AS clients
FROM piwik_events
WHERE user_role != 'sr-admin'
  AND user_holding_id IS NOT NULL;

-- Q2: сессии в неделю по типу пользователя
SELECT
    DATE_TRUNC('week', TO_TIMESTAMP(unixtimedate / 1000.0)) AS week_start,
    CASE WHEN user_role = 'sr-admin' THEN 'internal' ELSE 'external' END AS user_type,
    COUNT(DISTINCT session_id) AS sessions
FROM piwik_events
GROUP BY 1, 2
ORDER BY week_start, user_type;

-- Q3: успешно созданные кампании
-- Гипотеза: успешное создание = клик "Funnel Completed" на /campaigns/...
SELECT COUNT(*) AS campaigns_created
FROM piwik_events
WHERE click_text ILIKE '%Funnel Completed%'
  AND page_url ILIKE '%/programmatic-campaigns/%';

Анализ результата

В сырых данных unixtimedate представлен как float 1.68725E+12 — это миллисекунды. Делим на 1000 для TO_TIMESTAMP. На разных СУБД синтаксис отличается (FROM_UNIXTIME в MySQL/Hive, TO_TIMESTAMP_LTZ в Snowflake).
Если session_id тоже представлен в научной нотации (1.94024E+15), это уже теряет точность; перед агрегацией приводите к строке.
В Q3 «успешно создано» — гипотеза. Альтернативы: фильтр по click_text = 'Action-CampaignCreated' или по успешному переходу на /campaigns/{id}/active. На реальном собесе уточняйте определение успеха.

Дополнительные инсайты (Q4 кейса)

Воронка создания кампании: Navigation Campaigns → Filter → Next Phase → Funnel Completed. Конверсия каждого шага.
Внутренние пользователи (sr-admin) часто доминируют в логах — их надо отфильтровать для бизнес-метрик.
Top-N клиентов по числу сессий и созданных кампаний — для customer success команды.
Тренд WoW сессий по типам.

Подводные камни

Float precision. Большие id в формате 1.94e+15 теряют точность при импорте в Excel/Pandas. На уровне SQL это string — храните как text/bigint и обрабатывайте без округления.
TZ. unixtimedate в UTC; неделя может быть «понедельник» (ISO) или «воскресенье» в зависимости от диалекта.
DISTINCT по session_id. Без DISTINCT мы посчитаем события, не сессии.
NULL-значения user_holding_id. Для sr-admin они NULL — это нормально.
«Кампания создана». Семантика зависит от продукта. На собеседовании покажите, что это разные определения, и предложите спросить у PM.
Многократное создание. Если пользователь нажал «создать» дважды — это две кампании или одна? COUNT(DISTINCT campaign_id) если есть, иначе тщательно отбирать события.

Эталонный ответ

3 запроса (см. выше). Главное — отличать sr-admin (внутренних) от внешних клиентов; для последних использовать user_holding_id как идентификатор клиента. «Успешные кампании» — определять через event Funnel Completed на странице создания, уточнив семантику у владельца продукта.

Sportradar: SQL — сессии по неделям, internal vs external (Piwik)