MySQL - finding no active users

Question

Maybe it sound simple but the problem is.

I've got users who can add steps every time they went for a walk. Every time they record steps it will add a new entry to the table. Entries table schema is:
id, user_id, steps, date_created
The idea is to get users which already started adding steps but for some reasons they haven't been active for certain days.
The process could look like (per day):
Day 1: User add steps (in the morning)
User add steps (in the evening)
Day 2: User not adding anything
Day 3: User add steps
Day 4: User not adding anything
. . .
Day 11: User not adding anything
System will trigger a reminder for that user
Day 12: User not adding anything
System will not trigger a reminder because it should be sent only once

My question is can I achieve this in just one query and without the flag that the email was sent out already?

My idea is to pull user_id's from entries table and check if they add anything in the last 7 days but how to get rid off those who hasn't been active for more than 7 days.

Pull all users:

SELECT 
  DISTINCT(user_id) 
FROM 
  entries

Pull active users in the last 7 days:

SELECT 
  DISTINCT(user_id) 
FROM    
  entries 
WHERE 
  date_created BETWEEN '2014-01-17' AND NOW() 
ORDER BY 
  date_created DESC

Any suggestions appreciated (maybe it's in my head for to long and I'm over complicating it).

Thanks.

Answer 1

This query should do it. Handy to keep in your 'bag of tricks'.

SELECT u.user_name,
  FROM users AS u
LEFT OUTER
  JOIN entries AS e
    ON e.user_id = u.user_id
   AND e.date_created >= CURRENT_DATE - INTERVAL 7 DAY
 WHERE e.user_id IS NULL

In the query, the left join gets the matches you don't want to find. Therefore the condition you are interested in (date range) goes into the ON clause. The WHERE clause restricts to unmatched rows (in this case, all unmatched rows will return null from the join.

Some references : When to use a left outer join?