# Отчет дашборда — новые участники

**URL:** https://meta.discourse.org/t/dashboard-report-new-contributors/291879
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [17.Январь.2024 00:07:44 UTC](https://meta.discourse.org/t/dashboard-report-new-contributors/291879 "2024-01-17T00:07:44Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [17.Январь.2024 00:07:44 UTC](https://meta.discourse.org/t/dashboard-report-new-contributors/291879/1 "2024-01-17T00:07:44Z")

</div>

Это SQL-версия отчета панели управления для новых участников.

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

```sql
-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors 
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

```

### Объяснение SQL-запроса

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

**Параметры** :

- Запрос принимает два параметра: `:start_date` и `:end_date`, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат даты `YYYY-MM-DD`.

### Вложенный запрос: Определение даты первого поста для каждого пользователя

Этот отчет содержит подзапрос, который выбирает два столбца из таблицы `posts`: `user_id` и самую раннюю временную метку `created_at` (алиас `first_post_date`). Самая ранняя временная метка `created_at` представляет собой первый пост пользователя. Этот подзапрос включает условие `WHERE`, которое учитывает только посты, которые не были удалены (`deleted_at IS NULL`). Наконец, результаты группируются по `user_id`, чтобы гарантировать, что мы рассматриваем только первый пост каждого пользователя.

### Основной запрос: Подсчет пользователей, сделавших свой первый пост

Основной запрос выполняет следующие операции:

- **JOIN** : Основная таблица `posts` (алиас `p`) соединяется с результатом подзапроса (алиас `fp`) по `user_id`, чтобы сопоставить каждый пост с первым постом соответствующего пользователя.
- **Фильтрация по дате** : Подраздел `WHERE` включает два условия — он сравнивает временную метку `created_at` каждого поста с `first_post_date` из подзапроса, чтобы убедиться, что мы имеем дело только с первыми постами, и проверяет, что временная метка `created_at` попадает в указанный диапазон дат, включая `end_date` (+1 день для полного включения последнего дня).
- **Агрегация** : Затем посты группируются по дате, усеченной до дня без временной компоненты (`date_trunc('day', p.created_at)::date`), что позволяет подсчитать уникальных пользователей, сделавших первый пост в каждый день.
- **Подсчет** : Используя `COUNT(DISTINCT p.user_id)`, мы получаем количество уникальных пользователей, сделавших свой первый пост в каждый соответствующий день.
- **Сортировка** : Результаты сортируются по дате в порядке возрастания (`ORDER BY day`), чтобы обеспечить хронологический обзор вовлеченности пользователей.

### Итоговый вывод

Итоговый отчет состоит из двух столбцов:

- `day`: Дата вовлеченности пользователей без временной компоненты.
- `new_contributors`: Количество уникальных пользователей, сделавших свой первый пост на форуме в каждый день.

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

| day | new\_contributors |
| --- | --- |
| 2023-12-15 | 16 |
| 2023-12-16 | 8 |
| 2023-12-17 | 7 |
| 2023-12-18 | 19 |
| 2023-12-19 | 15 |