CREATE OR REPLACE TASK SUNO_PROD.PROD.FEATURE_TOP_MONTHLY_CLIPS_BY_AVG_DURATION_INSERT
    WAREHOUSE = SUNO_PROD_OTHER_DAILY_X_SMALL
    SCHEDULE = 'USING CRON 0 6 * * * UTC'
AS DECLARE
        stage_path string;
        p_date date;
        p_hour string;
        query_sql string;
        row_count number;
        start_time TIMESTAMP;
    BEGIN
        start_time := CURRENT_TIMESTAMP();
        INSERT INTO PROD.FEATURE_TOP_MONTHLY_CLIPS_BY_AVG_DURATION (
            CLIP_ID,
            UNIQUE_USERS,
            avg_duration_per_user,
            P_DATE
        )
        WITH UserClipDuration AS (
            SELECT 
                user_id, 
                song_id AS clip_id,
                LEAST(SUM(play_duration), 720) AS total_play_duration  -- Cap at 1000 seconds
            FROM suno_prod.prod.web_audio_player_actions 
            WHERE 
                server_timestamp > DATEADD(DAY, -60, CURRENT_DATE)
            GROUP BY user_id, clip_id
        ),
        ClipAggregated AS (
            SELECT 
                clip_id,
                COUNT(DISTINCT user_id) AS unique_users,
                SUM(total_play_duration) / NULLIF(COUNT(DISTINCT user_id), 0) AS avg_duration_per_user
            FROM UserClipDuration
            GROUP BY clip_id
            HAVING COUNT(DISTINCT user_id) >= 46  -- Ensure at least 5 different users listened
        ),
        RankedClips AS (
            SELECT 
                clip_id,
                unique_users,
                avg_duration_per_user,
                RANK() OVER (ORDER BY avg_duration_per_user DESC) AS rank
            FROM ClipAggregated
        )
        SELECT 
            clip_id,
            unique_users,
            avg_duration_per_user,
            CURRENT_DATE AS p_date
        FROM RankedClips
        WHERE avg_duration_per_user > 35 -- Get the top 100 clips
        order by avg_duration_per_user desc;


        call TASK_MONITOR_INSERT_PROC('FEATURE_TOP_PLAY_CLIPS_WEEKLY_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'HOURLY');
    end;