# Получить общее количество участников по месяцам с помощью Data Explorer

**URL:** https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [08.Февраль.2021 14:29:43 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899 "2021-02-08T14:29:43Z")
**Posts on this page:** 7
**Page:** 1

<div class="post-metadata">

### Author: ![Konrad\_Sopala](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/konrad_sopala/32/167014_2.png) [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [08.Февраль.2021 14:29:43 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899/1 "2021-02-08T14:29:43Z")

</div>

Всем привет!

Кто-нибудь писал запрос для [Data Explorer](https://meta.discourse.org/t/32566?silent=true), чтобы получить общее количество участников по месяцам с результатом вроде этого?

 ![1](https://global.discourse-cdn.com/meta/original/3X/f/b/fb20c3ccced1e915ac02a10e9990d45a57090ede.png)

---

<div class="post-metadata">

### Author: ![Konrad\_Sopala](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/konrad_sopala/32/167014_2.png) [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [09.Февраль.2021 17:11:42 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899/2 "2021-02-09T17:11:42Z")

</div>

Есть ли здесь гении [Data Explorer](https://meta.discourse.org/t/32566?silent=true)? 😃

---

<div class="post-metadata">

### Author: ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)
#### Post date: [09.Февраль.2021 21:56:37 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899/4 "2021-02-09T21:56:37Z")

</div>

Полагаю, вы имеете в виду что-то вроде этого?

```sql
select date_part('year', created_at) as year, 
date_part('month', created_at) as month,
count(*) as "count"
from users
group by date_part('year', created_at), date_part('month', created_at)
order by date_part('year', created_at) asc,
 date_part('month', created_at)

```

---

<div class="post-metadata">

### Author: ![Konrad\_Sopala](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/konrad_sopala/32/167014_2.png) [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [10.Февраль.2021 11:35:50 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899/5 "2021-02-10T11:35:50Z")

</div>

Довольно близко, но сейчас выводится количество пользователей, созданных каждый месяц. Я имел в виду общее количество пользователей на нашей платформе за конкретный месяц. Например, если в марте их было 1000, а в апреле добавилось 20 новых, то для апреля итоговая цифра будет 1020.

---

<div class="post-metadata">

### Author: ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)
#### Post date: [10.Февраль.2021 14:29:54 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899/7 "2021-02-10T14:29:54Z")

</div>

Надеюсь, это поможет.

```sql
WITH data_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
)

SELECT
  year, 
  month, 
  new_users_month,
  SUM(new_users_month) over (ORDER BY year, month rows between unbounded preceding AND current row) AS total
FROM data_month ORDER BY year, month

```

| year | month | new\_users\_month | total |
| --- | --- | --- | --- |
| 2020 | 2 | 50 | 50 |
| 2020 | 3 | 100 | 150 |
| 2020 | 4 | 50 | 200 |

---

<div class="post-metadata">

### Author: ![Konrad\_Sopala](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/konrad_sopala/32/167014_2.png) [@Konrad\_Sopala](https://meta.discourse.org/u/Konrad_Sopala)
#### Post date: [10.Февраль.2021 15:00:05 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899/10 "2021-02-10T15:00:05Z")

</div>

Отлично, спасибо за помощь!

---

<div class="post-metadata">

### Author: ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)
#### Post date: [12.Март.2021 15:00:14 UTC](https://meta.discourse.org/t/get-member-count-overall-by-month-using-data-explorer/178899/11 "2021-03-12T15:00:14Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.