PostgreSQL extending UPDATE query

Question

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 > now()::DATE - 30 ** ) AS monthlyUsers,
        count(u.*) OVER ( ** where u.last_visit > 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?

ypercubeᵀᴹ · Accepted Answer · 2014-04-11 18:46:13Z

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 >= now()::DATE - 30 THEN 1 END) AS monthlyUsers, 
        count(CASE WHEN u.last_visit >= 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 >= or > in the conditions. It's better (in my opinion) to have date and time checks as ">= start_date" and "< end_date", in other words working with closed-open datetime intervals.

Stack Exchange Network

PostgreSQL extending UPDATE query

users:

sites:

site_stats:

1 Answer 1

Linked

Hot Network Questions

PostgreSQL extending UPDATE query

users:

sites:

site_stats:

1 Answer 1

Linked

Related

Hot Network Questions