create or replace transient table {{ user_top_score_songs_table_name }} AS
SELECT 
    USER_ID,
    CLIP_ID,
    CREATED_AT,
    PLAY_CNT,
    PLAY_DURATION,
    LIKE_COUNT,
    LANGUAGE,
    STYLE_TAGS,
    SCORE,
    ROW_CNT,
    P_DATE
FROM (
    SELECT 
        b.user_id as USER_ID,
        b.created_at as CREATED_AT,
        a.song_id as CLIP_ID,
        a.sum_total_play_cnt_5 AS PLAY_CNT,
        a.sum_total_play_duration_5 AS PLAY_DURATION,
        c.like_count AS LIKE_COUNT,
        b.LYRICS_LANGUAGE AS LANGUAGE,
        b.style_tags AS STYLE_TAGS,
        (a.sum_total_play_cnt_5 * 1 + a.sum_total_play_duration_5 * 1 + c.like_count * 1) AS SCORE,
        ROW_NUMBER() OVER (PARTITION BY b.user_id ORDER BY (a.sum_total_play_cnt_5 + a.sum_total_play_duration_5 + c.like_count) DESC) AS ROW_CNT,
        '{{ partition_start_date }}' as P_DATE
    FROM ml_song_summary_info a 
    JOIN dim_clip b ON a.song_id = b.clip_id
    JOIN fact_clip_status c ON a.song_id = c.clip_id
    WHERE (a.sum_total_play_cnt_5 > 0 AND a.sum_total_play_duration_5 > 0 AND c.like_count > 0)
        AND a.p_date = (SELECT MAX(p_date) FROM ml_song_summary_info) AND a.p_hour = (SELECT MAX(p_hour) FROM ml_song_summary_info)
) ranked_results
WHERE ROW_CNT <= 100
ORDER BY USER_ID, ROW_CNT; 
