Postgresql condition on subquery results

Question

This is a new question based on the solution to my previous question

I have the following tables:

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

And I am using this query to update the user_stats table

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 ;

This works fine, except that if there are 0 users linked to a site in the time frame then no update will occur, so the table col will keep the previous value rather than showing 0.

What would be the best way to add a condition so that any sites with 0 linked users is updated with 0?

My first guess was to use a CASE in the WHERE clause?

Answer 1

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 >= now()::date - 30 OR NULL) AS monthly
        , count(last_visit >= 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.