SELECT 
    u.user_id as entity_id,
    b.email,
    email_domain,
    SUM(COALESCE(clip_count, 0)) as clip_count,
    MAX(most_recent_activity_date) as most_recent_activity_date
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, -7, CURRENT_DATE) OR
      b.most_recent_activity_date > DATEADD(DAY, -7, CURRENT_DATE)
  )
  AND meets_bot_definition
  AND email_domain IN (
    'outlook.in',
    'outlook.de',
    'outlook.sg',
    'outlook.jp',
    'outlook.kr',
    'outlook.it'
  )
  AND clip_count > 10
  AND play_count = 0
  AND are_clip_metrics_healthy
GROUP BY 1,2,3
ORDER BY most_recent_activity_date DESC
