CREATE TRANSIENT TABLE SUNO_PROD.PROD.AGG_USER_TO_USER_ENGAGEMENT_WEEKLY (
    user_id              NUMBER FOREIGN KEY REFERENCES suno_prod.prod.dim_user(user_id) COMMENT 'User who interacted with or followed another user',
    engage_user_id       NUMBER FOREIGN KEY REFERENCES suno_prod.prod.dim_user(user_id) COMMENT 'User who was engaged with (creator of clips or followed user)',
    total_score          NUMBER COMMENT 'Aggregated engagement score between users (includes interaction scores and follow-only scores)',
    relationship         VARCHAR(16) COMMENT 'Relationship type: mutual, following, followed, none',
    p_date               DATE COMMENT 'Partition date (UTC)'
) CLUSTER BY (p_date)
COMMENT = 'Aggregated weekly user-to-user engagement scores over 28-day window, including interaction scores and follow-only relationships.'; 