# 週次/月次/年次サインアップ

**URL:** <https://meta.discourse.org/t/weekly-monthly-yearly-signups/278119>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2023 年 9 月 7 日午前 5:24 UTC](https://meta.discourse.org/t/weekly-monthly-yearly-signups/278119 "2023-09-07T05:24:18Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [2023 年 9 月 7 日午前 5:24 UTC](https://meta.discourse.org/t/weekly-monthly-yearly-signups/278119/1 "2023-09-07T05:24:18Z")

</div>

> 📝 これらのクエリは、ステージング済みおよび非アクティブなアカウントを除き、指定された期間内に作成された新しいユーザーアカウントの数を示します。合計は、週/月/年ごとのサインアップ数、選択した期間の累積合計、およびそれ以前のすべてのサインアップを含む累計合計で内訳されます。
> 
> **注:** ユーザーが削除されると、そのレコードはデータベースに表示されなくなり、クエリ結果に含まれなくなります。これにより、最近の実行結果を以前の結果と比較する際に変動が生じる可能性があります。

### 週次サインアップ

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

WITH weekly_signups AS (

SELECT
    to_char(date_trunc('week', created_at)::date,'YYYY-MM-DD') AS week,
    COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
  AND staged = false
  AND active = true
GROUP BY week
),

all_to_date AS (

SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
  AND staged = false
  AND active = true

)

SELECT
    week AS "週の開始日",
    signups AS "週次サインアップ数",
    SUM(signups::int) OVER (ORDER BY week) AS "累積合計",
    (SUM(signups::int) OVER (ORDER BY week) + previous::int) AS "総計"
FROM weekly_signups, all_to_date
ORDER BY week ASC

```

| 週の開始日 | 週次サインアップ数 | 累積合計 | 総計 |
| --- | --- | --- | --- |
| 2023-07-31 | 98 | 98 | 45418 |
| 2023-08-07 | 141 | 239 | 45559 |
| 2023-08-14 | 129 | 368 | 45688 |
| 2023-08-21 | 126 | 494 | 45814 |
| 2023-08-28 | 138 | 632 | 45952 |
| 2023-09-04 | 53 | 685 | 46005 |

  

### 月次サインアップ

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

WITH monthly_signups AS (
SELECT
    date_trunc('month', created_at)::date AS month,
    to_char(date_trunc('month', created_at)::date,'Month YYYY') AS month_display,
    COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
  AND staged = false
  AND active = true
GROUP BY month, month_display
),

all_to_date AS (
SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
  AND staged = false
  AND active = true
)

SELECT
    month_display AS "月",
    signups AS "月次サインアップ数",
    SUM(signups::int) OVER (ORDER BY month) AS "累積合計",
    (SUM(signups::int) OVER (ORDER BY month) + previous::int) AS "総計"
FROM monthly_signups, all_to_date
ORDER BY month

```

| 月 | 月次サインアップ数 | 累積合計 | 総計 |
| --- | --- | --- | --- |
| 2023年6月 | 596 | 596 | 45307 |
| 2023年7月 | 517 | 1113 | 45307 |
| 2023年8月 | 583 | 1696 | 45890 |
| 2023年9月 | 102 | 1798 | 46005 |

  

### 年次サインアップ

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

WITH yearly_signups AS (

SELECT
    to_char(date_trunc('year', created_at)::date,'YYYY') AS year,
    COUNT(*) AS signups
FROM users
WHERE created_at::date BETWEEN :start_date::date AND :end_date::date
  AND staged = false
  AND active = true
GROUP BY year
),

all_to_date AS (

SELECT COUNT(*) AS previous
FROM users
WHERE created_at::date < :start_date
  AND staged = false
  AND active = true

)

SELECT
    year AS "年",
    signups AS "年次サインアップ数",
    SUM(signups::int) OVER (ORDER BY year) AS "累積合計",
    (SUM(signups::int) OVER (ORDER BY year) + previous::int) AS "総計"
FROM yearly_signups, all_to_date
ORDER BY year ASC

```

| 年 | 年次サインアップ数 | 累積合計 | 総計 |
| --- | --- | --- | --- |
| 2019 | 3590 | 3590 | 23135 |
| 2020 | 4258 | 7848 | 27393 |
| 2021 | 5908 | 13756 | 33301 |
| 2022 | 7889 | 21645 | 41190 |
| 2023 | 4815 | 26460 | 46005 |

---

_[View the full topic](https://meta.discourse.org/t/weekly-monthly-yearly-signups/278119)._