Postgresql condition on subquery results
Either you run an additional UPDATE (best in one transaction):

UPDATE site_stats s
SET    total_users   = 0
     , monthly_users = 0
     , weekly_users  = 0
WHERE  NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id)


Or use this instead (I would do that):

UPDATE site_stats s
SET    total_users   = COALESCE(u.total, 0)
     , monthly_users = COALESCE(u.monthly, 0)
     , weekly_users  = COALESCE(u.weekly, 0)
FROM   site_stats s0
LEFT   JOIN (
   SELECT local_site 
        , count(*) AS total
        , count(last_visit &gt;= now()::date - 30 OR NULL) AS monthly
        , count(last_visit &gt;= now()::date - 7  OR NULL) AS weekly
   FROM   users
   GROUP  BY 1
   ) u ON u.local_site = s0.site_id
WHERE  s.site_id = s0.site_id;


Including some other minor simplifications.