0

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.

  • Are you missing a where? Should be ...JOIN meetings m ON (u.id != m.user_id) WHERE m.created_at BETWEEN %s and %s AND .... – Andrew Brennan Aug 3 '16 at 15:15
1

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
1

You can write a CTE[1] to compute a date interval, take a look:

WITH last_month_minus_thisweek AS(
     SELECT GENERATE_SERIES(CURRENT_DATE - '1 Month'::interval, CURRENT_DATE - '1 week'::INTERVAL, '1 day') AS day
), date_list AS (
     SELECT MIN(day) AS start_date, MAX(day) as end_date FROM last_month_minus_thisweek
)

SELECT * FROM date_list;

To use with your query, put date_list on there FROM clause when adjust your filter, eg:

WITH last_month_minus_thisweek AS(
     SELECT GENERATE_SERIES(CURRENT_DATE - '1 Month'::interval, CURRENT_DATE - '1 week'::INTERVAL, '1 day') AS day
), date_list AS (
     SELECT MIN(day) AS start_date, MAX(day) as end_date FROM last_month_minus_thisweek
)

SELECT  u.id, u.given_name, u.family_name, u.email 
FROM users u, last_month_minus_thisweek
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 date_list.start_date and date_list.end_date)
GROUP BY u.id, u.given_name, u.family_name, u.email;

I Hope it helps.

[1] https://www.postgresql.org/docs/current/static/queries-with.html

  • Hey, thanks alot for your reply! is this the same as peach's answer? I got ProgrammingError: invalid reference to FROM-clause entry for table "u" LINE 9: JOIN tasks m ON (u.id != m.user_id) ^ HINT: There is an entry for table "u", but it cannot be referenced from this part of the query. – Wboy Aug 8 '16 at 3:49
  • Do you declare the u alias for the users table on the FROM clause? – Sebastian Webber Aug 9 '16 at 4:55

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.