Postgresql – Selecting a range of users active in the past month, but not in the past week

postgresql

I have two tables, users and meetings. I'm trying to select all the users who have NOT made a meeting in the past week, but have made a meeting in the past month.

SELECT  u.id, u.given_name, u.family_name, u.email 
    FROM users u 
    JOIN meetings m ON (u.id != m.user_id) 
    AND m.created_at BETWEEN %s and %s 
    AND u.state ='active' 
    AND u.id in (SELECT user_id 
                 from meetings 
               where created_at BETWEEN %s and %s)
    GROUP BY u.id, u.given_name, u.family_name, u.email

This is using the psycopg2 adapter, so %s will be replaced by a datetime object. The %s replacement is in the order of:

7 days ago, today, a month ago, 8 days ago.

Also, the reason i did u.id != m.useridis because the userid will not be in the meetings table if the user has not organized a meeting in the last week.

This doesn't seem to be working, as I am still getting people who have organized a meeting in the past week.

Best Answer

joining on u.id != m.user_id is going to make everyone join on everyone but themselves if their ID is in your subquery. try joining twice and having the week join be null. this will only show users who have never made a meeting in the past week. 

SELECT  u.id, u.given_name, u.family_name, u.email 
FROM users u 
INNER JOIN meetings m_month ON u.id = m_month.user_id 
and m_month.created_at > getdate() - interval '1 month'
LEFT JOIN meetings m_week on u.id = m_week.user_id    
and m_week.created_at > getdate() - interval '1 week'
where m_week.created_at is null 
GROUP BY u.id, u.given_name, u.family_name, u.email
Related Question