SELECT 
    u.user_id as entity_id,
    u.phone_number,
    LEFT(u.phone_number, 4) as phone_prefix,
    SUM(COALESCE(clip_count, 0)) as clip_count
  FROM SUNO_PROD.PROD.BOT_HOURLY b
  LEFT JOIN SUNO_PROD.PROD.DIM_USER u
    ON b.user_id = u.user_id
  WHERE 
    (
        b.user_joined_at > DATEADD(DAY, -1, CURRENT_DATE) OR
        b.most_recent_activity_date > DATEADD(DAY, -1, CURRENT_DATE)
    )
    AND u.phone_number IS NOT NULL
    AND phone_prefix = '+852' -- Hong Kong phone prefix
    AND meets_bot_definition
    AND clip_count > 10
    AND play_count = 0
    AND are_clip_metrics_healthy
    AND most_recent_plan IS NULL -- Free plan
  GROUP BY 1,2,3
