Closed1

データ分析・データ基盤へのGPT-4の活用の検証スレッド

shinokishinoki

月次の有料ユーザー数を算出することができないか?

❓プロンプト

下記のスキーマから企業ごとの月次の有料ユーザー数を算出してください

create or replace TABLE USERS (
	ID NUMBER(38,0),
	CREATED_AT TIMESTAMP_LTZ(9),
	UPDATED_AT TIMESTAMP_LTZ(9),
	START_DATE TIMESTAMP_LTZ(9),
	END_DATE TIMESTAMP_LTZ(9),
	FISCAL_YEAR NUMBER(38,0),
	COMPANY_ID NUMBER(38,0),
	DELETED_AT TIMESTAMP_LTZ(9)
);

💬 回答

このテーブルスキーマを使用して、企業ごとの月次有料ユーザー数を算出するためのSQLクエリは以下のようになります。ここでは、START_DATEを基準に月次の有料ユーザーをカウントし、END_DATEが空か、月の最終日より後であるユーザーを対象にします。また、DELETED_ATがNULLであることを条件に含めて、削除されていないユーザーだけを対象とします。

WITH monthly_dates AS (
    SELECT
        DATE_TRUNC('MONTH', START_DATE) AS month_start,
        LAST_DAY(DATE_TRUNC('MONTH', START_DATE)) AS month_end
    FROM USERS
    WHERE DELETED_AT IS NULL
    GROUP BY DATE_TRUNC('MONTH', START_DATE)
),
monthly_users AS (
    SELECT
        COMPANY_ID,
        month_start,
        month_end,
        COUNT(*) AS monthly_paid_users
    FROM USERS
    JOIN monthly_dates ON (
        USERS.START_DATE <= monthly_dates.month_end
        AND (USERS.END_DATE IS NULL OR USERS.END_DATE > monthly_dates.month_end)
    )
    WHERE USERS.DELETED_AT IS NULL
    GROUP BY COMPANY_ID, month_start, month_end
)
SELECT * FROM monthly_users ORDER BY COMPANY_ID, month_start;

このクエリでは、まずmonthly_datesという共通テーブル式(CTE)を作成し、月次の範囲を特定します。次に、monthly_usersという別のCTEを作成して、企業ごとの月次有料ユーザー数をカウントします。最後に、monthly_usersを選択して、結果を企業IDと月の開始日でソートします。

結果

  • Snowflakeを実行環境としたが、実行可能(シンタックスエラーなどはない)
    • いままでいくつかプロンプトをSnowflakeを実行環境とするようにしているため、プロンプトにいれなくてもSnowflakeで実行可能なSQLを発行してくれたのかもしれない。
  • ユーザーが一度削除されてしまうと、過去の有料ユーザーからも消される仕様になっているので、修正が必要
このスクラップは2023/09/14にクローズされました