Условие

Две таблицы:

Events (логи событий):

datetime — дата-время,
user_id (UInt32),
event — score_view, score_download, score_add_to_favorites, ...
score_id (UInt32) — идентификатор партитуры.

Score (партитуры пользователей):

id — id скора,
user_id — id автора скора.

Замечания:

Автор может сам просматривать свои скоры — это попадёт в Events.
Не все посетители — авторы.

Задачи (предпочтительно ClickHouse-диалект):

Уникальное число новичков, заходивших на прошлой неделе.
Топ-3 популярных автора скоров за прошедший месяц.
Число сессий на пользователя.

Решение

Подход

Все три задачи нетривиальные:

«Новичок» — нужно определить, кто пришёл «впервые». Это требует cross-join текущей недели и всей истории.
«Популярный автор» — определить через ивенты (просмотры/скачивания) и связать с автором партитуры через Score. Не путать с автором события.
«Сессии» — стандартная sessionization (30 мин неактивности).

Задача 1. Уникальные новички за прошлую неделю

«Новичок» = user_id, у которого первое событие на прошлой неделе.

SELECT
  uniqExact(user_id) AS new_users
FROM (
  SELECT user_id
  FROM events
  GROUP BY user_id
  HAVING toMonday(min(datetime)) = toMonday(now() - interval 1 week)
  -- т.е. первое событие — на прошлой неделе
);

Альтернативы:

uniq(user_id) (HyperLogLog) если объёмы огромны и точность не критична.
argMin(user_id, datetime) для первого события.

Подвох

«Прошлая неделя» — какая? Календарная (Mon-Sun) или последние 7 дней? Условие неоднозначное. В моём решении — календарная (через toMonday). Если нужны последние 7 дней:

HAVING min(datetime) >= now() - interval 7 day
   AND min(datetime) <  now()

Задача 2. Топ-3 популярных автора за месяц

«Автор» = создатель скора (из Score.user_id). «Популярность» = метрика типа суммарных просмотров его скоров за месяц.

SELECT
  s.user_id                             AS author_id,
  uniqExact(e.user_id)                  AS unique_readers,
  count()                               AS total_views
FROM events e
JOIN score s ON s.id = e.score_id
WHERE e.event = 'score_view'
  AND e.datetime >= toStartOfMonth(now() - interval 1 month)
  AND e.datetime <  toStartOfMonth(now())
  -- Не считаем просмотры автора своих скоров
  AND e.user_id != s.user_id
GROUP BY s.user_id
ORDER BY unique_readers DESC
LIMIT 3;

Тонкости:

Метрика популярности: уникальные читатели (uniqExact(e.user_id)) или всего просмотров (count())? Уникальные — лучше: один фанат с 1000 просмотров не заваливает рейтинг. Бизнес может предпочесть total_views — уточняйте.
Author own-views: автор может сам просматривать свои скоры — фильтр e.user_id != s.user_id.
Событие: считаем только score_view? Может быть, скачивания и фавориты тоже учитывать с весами.
«Месяц»: календарный (например, январь) или последние 30 дней? toStartOfMonth даёт календарный.

Задача 3. Число сессий на пользователя

Стандартная сессионизация: разрыв > 30 минут = новая сессия.

WITH events_lag AS (
  SELECT
    user_id,
    datetime,
    -- Время предыдущего события того же юзера
    neighbor(datetime, -1) OVER (PARTITION BY user_id ORDER BY datetime) AS prev_dt,
    -- Эквивалент LAG в ClickHouse
    -- Альтернативно: lagInFrame(datetime) OVER (...)
    if(
      isNull(neighbor(datetime, -1) OVER (PARTITION BY user_id ORDER BY datetime))
        OR datetime - neighbor(datetime, -1) OVER (PARTITION BY user_id ORDER BY datetime) > toIntervalMinute(30),
      1, 0
    ) AS is_new_session
  FROM events
)
SELECT
  user_id,
  sum(is_new_session) AS sessions
FROM events_lag
GROUP BY user_id;

ClickHouse имеет нюансы с window-functions (поддержка с ClickHouse 21.x). Альтернатива — через arrayJoin и массив-агрегации:

SELECT
  user_id,
  -- Собираем массив времён, сортируем, считаем разрывы
  arrayCount(
    (gap) -> gap > 30 * 60,
    arrayMap(
      (x, y) -> dateDiff('second', y, x),
      arraySort(groupArray(datetime)),
      arrayPushFront(arraySort(groupArray(datetime)), arraySort(groupArray(datetime))[1])
    )
  ) + 1 AS sessions  -- +1 потому что первая сессия не имеет «предыдущего разрыва»
FROM events
GROUP BY user_id;

Сложно читать, но это идиоматичный ClickHouse — с массивами быстрее, чем оконные функции.

Чистый подход через subquery

SELECT
  user_id,
  sum(is_new) AS sessions
FROM (
  SELECT
    user_id,
    datetime,
    if(datetime - lagInFrame(datetime) OVER (PARTITION BY user_id ORDER BY datetime) > 1800
       OR isNull(lagInFrame(datetime) OVER (PARTITION BY user_id ORDER BY datetime)),
       1, 0) AS is_new
  FROM events
)
GROUP BY user_id;

Анализ / интерпретация

Эти 3 запроса покрывают типичные KPI продукта:

Приток новичков — здоровье воронки регистрации.
Топ-авторы — кого продвигать, с кем выстраивать отношения.
Сессии на юзера — engagement.

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

«Новичок» определение. Может быть «зарегистрировался впервые», а может «впервые стал активным». Без таблицы users с регистрацией легко спутать.
Часовые пояса. ClickHouse хранит datetime в UTC; toMonday(now()) использует серверный tz. Уточняйте, какой нужен.
uniq vs uniqExact. Первый — приблизительный (HLL), второй — точный, но дороже.
Author own-views. Если не отфильтровать, активный автор может попасть в топ за счёт своих просмотров.
Метрика «популярность»: уникальные читатели или всего просмотров — разные победители. Уточните.
Сессионизация в CH: lagInFrame для оконного LAG, groupArray + arrayMap для массивного подхода. Не путайте с PG.
30 минут — бизнес-параметр, не догма. Для Musescore (просмотр партитур) разумный порог.
Разрыв через полночь. Если задача — «сессии прерываются на смене даты», нужен дополнительный условие; если нет — просто временной gap.

Альтернативы

runningDifference(datetime) в ClickHouse — даёт разницу с предыдущей строкой при ORDER BY. Если данные уже отсортированы — самое быстрое.
Materialized view для сессий — пересчитывать раз в день, не on-the-fly.

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

Задача 1: GROUP BY user_id HAVING min(datetime) попадает в прошлую неделю. uniqExact(user_id).

Задача 2: JOIN events WITH score, WHERE event='score_view' AND e.user_id != s.user_id AND date >= toStartOfMonth(now() - interval 1 month). GROUP BY s.user_id ORDER BY uniqExact(e.user_id) DESC LIMIT 3.

Задача 3: window-функция lagInFrame(datetime) OVER (PARTITION BY user_id ORDER BY datetime) → флаг новой сессии (gap > 30 мин) → SUM по юзеру. Альтернатива на массивах — быстрее в CH.

Musescore — ClickHouse SQL: новички, топ-авторы, число сессий на пользователя