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

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)
);

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;