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

[JammyDodger]

これらのクエリは、ステージング済みおよび非アクティブなアカウントを除き、指定された期間内に作成された新しいユーザーアカウントの数を示します。合計は、週/月/年ごとのサインアップ数、選択した期間の累積合計、およびそれ以前のすべてのサインアップを含む累計合計で内訳されます。

注: ユーザーが削除されると、そのレコードはデータベースに表示されなくなり、クエリ結果に含まれなくなります。これにより、最近の実行結果を以前の結果と比較する際に変動が生じる可能性があります。

週次サインアップ

-- [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

月次サインアップ

-- [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

年次サインアップ

-- [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

[outofthebox (Carson)]

こんにちは、この仕組みについて非常に鈍感で申し訳ありませんが、日付が入力された場合、これらの例のいずれかはどのようになりますか？

[JammyDodger]

どういう意味か分かりません。お探しのものが結果の例であれば、それぞれの下に例がありますか？

[outofthebox (Carson)]

承知いたしました。

お尋ねになりたいことは次のとおりです。コードのどこに開始日と終了日を記述し、どのような形式で記述すればよいですか？

[Firepup650 (Firepup Sixfifty)]

@outofthebox

コードのどこに開始日と終了日を入力すればよいですか？

コード内にはありません。クエリを実行するボタンの上に、入力ボックスが追加されるはずです（表示されない場合は、保存後に更新してください）。

@outofthebox

どのような形式で入力すればよいですか？

日付ピッカーが表示されると思います。

[JammyDodger]

はい、承知いたしました。最初のページリフレッシュまでパラメータ入力ボックスが表示されないという既知の問題があります。

まだ日付ピッカーほどの洗練さはありませんが（( )）、フォーマットにはかなり寛容だと思います。私は自分で使用する際は英国の日付フォーマット（例：21/01/2024）を使用していますが、スクリーンショットの例を共有する際は、より普遍的な例（例：2024-01-21）に合わせるようにしています。

[outofthebox (Carson)]

わかりました。ページを更新する必要がありました。