0

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.