Отчет по панели управления: Рост уровня доверия

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчета о росте уровня доверия.

Этот отчет подсчитывает количество пользователей, достигших уровней доверия 1, 2, 3 и 4 в каждый день в указанном диапазоне дат, и помогает администраторам отслеживать вовлеченность пользователей и их прогресс по уровням доверия в сообществе Discourse.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

Пояснение к SQL-запросу

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

Ниже приведена расшифровка запроса:

• Параметры даты: Запрос принимает два параметра, :start_date и :end_date, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
• date(created_at) AS date: Эта часть оператора SELECT извлекает дату из временной метки created_at и помечает её как «date» для каждой записи. Это будет использоваться для группировки результатов по дням.
• COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: Этот раздел подсчитывает количество пользователей, перешедших на определенный уровень доверия (Y) с более низкого уровня (меньше X). Ключевое слово FILTER используется для включения только строк, удовлетворяющих указанному условию. Приведение ::integer используется для преобразования previous_value в целое число для сравнения. Существует четыре таких столбца подсчета, по одному для каждого уровня доверия от 1 до 4.
• FROM user_histories: Указывает таблицу, из которой извлекаются данные.
• WHERE created_at >= :start_date AND created_at <= :end_date: Фильтрует записи, включая только те, которые находятся в указанном диапазоне дат.
• AND (action = 2 OR action = 15): Фильтрует записи, включая только те, где действие указывает на изменение уровня доверия. Конкретные действия, соответствующие изменениям уровня доверия, представлены числами 2 и 15.
• GROUP BY date(created_at): Группирует результаты по дате, чтобы вы получили подсчет изменений уровня доверия для каждого дня в указанном диапазоне.
• ORDER BY date(created_at): Сортирует результаты в хронологическом порядке по дате.

Пример результатов

date	tl1_reached	tl2_reached	tl3_reached	tl4_reached
2024-01-01	1	3	1	0
2024-01-02	5	1	0	1
2024-01-03	8	2	3	0
…	…	…	…	…"}