-- Shared query used for syncing domain blocks to Stripe Radar lists,
-- bot model, etc
SELECT
    email_domain as entity_id,
    SUM(CASE WHEN most_recent_plan_level IS NULL THEN 1 ELSE 0 END) as free_users,
    SUM(CASE WHEN most_recent_plan_level IS NULL THEN 0 ELSE 1 END) as paid_users,
    COUNT(*) as total
FROM SUNO_PROD.PROD.BOT_HOURLY
WHERE TRUE
AND email_domain_in_block_list
AND email_domain NOT IN ( -- safeguard #1 - should never be true but protect from human error
    'outlook.com',
    'gmail.com',
    'yahoo.com',
    'hotmail.com'
)
GROUP BY email_domain
HAVING paid_users BETWEEN 10 AND 100 -- safeguard #2 - domains with large numbers of paying users will require a human in the loop
ORDER BY paid_users DESC
