PostgreSQL extending UPDATE query
You don't need window functions:

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit &gt;= now()::DATE - 30 THEN 1 END) AS monthlyUsers, 
        count(CASE WHEN u.last_visit &gt;= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;


Please check if you want &gt;= or &gt; in the conditions. It's better (in my opinion) to have date and time checks as "&gt;= start_date" and "&lt; end_date", in other words working with closed-open datetime intervals.