# 월별 사용자 수를 사후적으로 조회하는 방법

**URL:** https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522
**Category:** Data & reporting
**Created:** [11월 5, 2024, 1:40오전 UTC](https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522 "2024-11-05T01:40:38Z")
**Posts on this page:** 1
**Showing post:** 10

<div class="post-metadata">

### Author: ![Moin](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/moin/32/554653_2.png) [@Moin](https://meta.discourse.org/u/Moin)
#### Post date: [11월 6, 2024, 10:36오후 UTC](https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522/10 "2024-11-06T22:36:47Z")

</div>

흥미로운 질문이네요.

먼저 [여기](https://meta.discourse.org/t/using-date-trunc-for-data-aggregation/278095#cumulative-total-users-4)의 예시를 살펴봤습니다. 하지만 이 방법은 삭제된 사용자를 무시합니다. 해당 시점에 등록되어 현재까지 남아 있는 사용자의 수만 얻을 수 있을 뿐, 그 사이에 삭제된 사용자는 포함되지 않습니다.

따라서 제 아이디어는 해당 월에 마지막으로 등록한 사용자의 ID를 가져오는 것이었습니다. 이는 해당 시점의 최대 가능한 사용자 수입니다. 이후 삭제된 사용자의 수를 이 값에서 빼면 됩니다. 다만, 봇 계정(예: forum-helper)은 ID가 음수이지만 삭제 시에는 계산에 포함됩니다. (하지만 이는 아마도 사소한 차이일 것입니다.) 제 쿼리는 다음과 같습니다:

```sql
-- [params]
-- date :start_date
-- date :end_date

WITH month_dates AS (
    -- 시작일과 종료일 사이의 월 말 날짜 생성
    SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
    FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
    -- 각 월 말 날짜에 대해, 그 날짜 이전에 생성된 가장 최근 사용자 찾기
    SELECT md.month_end,
           (SELECT id 
            FROM users u 
            WHERE u.created_at < md.month_end 
            ORDER BY u.created_at DESC 
            LIMIT 1) AS user_max_id
    FROM month_dates md
),
cumulative_deletion_count AS (
    -- 각 월 말 날짜까지의 누적 삭제 수 계산
    SELECT md.month_end,
           (SELECT COUNT(*)
            FROM user_histories uh
            WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
    FROM month_dates md
)
SELECT 
    md.month_end,
    ru.user_max_id,
    cdc.deletions_count,
    ru.user_max_id - cdc.deletions_count AS number_of_users
FROM 
    month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end

```

하지만 이 쿼리는 user\_histories 테이블에도 저장되어 있는 (비)활성화 상태를 고려하지 않습니다. 그래도 시작점으로 도움이 될 수 있을 것입니다.

---

_[View the full topic](https://meta.discourse.org/t/retrospectively-pulling-number-of-users-each-calendar-month/334522)._