PostgreSQL extending UPDATE query I have the following update query to populate a table storing user stats

users:

int (pk) user_id

date     last_visit

int (fk) local_site

sites:

int (pk) site_id

site_stats:

int (fk) site_id

int total_users

int monthly_users

int weekly_users



To populate the 'total_users' column I am using the following query...

UPDATE site_stats
SET
    total_users = totalUsers,
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site


The query above works ok, I would like to now extend it to update the monthly_users and weekly_users

Is it possible to do in the same query?

I was initially thinking of using a window function but after reading the docs I am not so sure its possible, eg.

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(u.*) OVER ( ** where u.last_visit &gt; now()::DATE - 30 ** ) AS monthlyUsers,
        count(u.*) OVER ( ** where u.last_visit &gt; now()::DATE - 7 ** ) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site


Or would a subquery be more appropriate?
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.