-- Subscription Churn Reason table for storing churn reason data from RDS
-- This table is populated by the Glue job rds_to_s3_billing_subscriptionchurnreason_hourly_upsert
-- which dumps data from RDS to S3 and then loads it into Snowflake
-- Clustered by partition date and hour for optimal query performance
CREATE TRANSIENT TABLE IF NOT EXISTS {subscription_churn_reason_table_name} CLUSTER BY (p_date, p_hour) (
    -- Primary identifier
    ID VARCHAR(255),                                    -- Unique churn reason identifier
    
    -- Timestamps
    CREATED_AT TIMESTAMP_TZ(9),                         -- When the churn reason was created
    UPDATED_AT TIMESTAMP_TZ(9),                         -- Last update timestamp
    
    -- Churn details
    REASON VARCHAR(255),                                -- Standard churn reason
    CUSTOM_REASON VARCHAR(500),                         -- Custom churn reason provided by user
    
    -- Subscription details at churn
    CHURNED_FROM_PERIOD VARCHAR(255),                   -- Billing period user churned from
    CHURNED_FROM_CURRENCY VARCHAR(255),                 -- Currency of the plan they churned from
    CHURNED_FROM_PLAN_ID VARCHAR(255),                  -- Plan ID user churned from
    
    -- References
    USER_ID NUMBER(38,0),                               -- User who churned
    
    -- Partitioning columns for optimization
    P_DATE DATE,                                        -- Partition date based on created_at
    P_HOUR NUMBER(38,0)                                 -- Partition hour (0-23) based on created_at
);

