CODEWITHSUNDEEP
mysqlsql
Dimitar Arabadzhiyski
Dimitar Arabadzhiyski

Reputation: 282

Display the number of days since a user last commented

I have two tables with multiple rows. The first is called comments and has the following structure:

user_id   last_commented
........................
9239289   2017-11-06
4239245   2017-11-05
4239245   2017-11-03
6239223   2017-11-02
1123139   2017-11-04

The second one is called users and has the following structure:

user_id   user_name   user_status
.................................
9239289   First Name   0
4239245   First Name2  2
6239223   First Name3  1
1123139   First Name4  2

I need a query that displays the users who have not added comments for the last 3 days, have a user_status equals to 2 and display the number of days since they last commented.

This is my query so far:

select u.*
from users u
where not exists (
   select 1
   from comments c
   where c.user_id = u.user_id and last_commented > DATE(NOW()) - INTERVAL 3 DAY
) and user_status = 2

Which outputs correctly the users who haven't commented for the last 3 days. How can I modify it so it shows the number of days since they last commented?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If you need the number of days since the last comment, then you will need a join, of some sort:

select u.*, datediff(curdate(), last_commented)
from users u left join
     comments c
     on c.user_id = u.user_id
where u.status = 2
group by u.user_id
having max(last_commented) < curdate() - interval 3 day or
       max(last_commented) is null;

This version includes users who have not commented at all.

Upvotes: 3

Related Questions