MySQL : SELECT all users with 5 unopened messages in there last 5 messages received in a messages table

Question

I'm using mysql.

I have a messages table with userid, message_id, opened (true or false), timestamp.

I want all users who did not open a message in there last 5 messages received

This is what I have right now:

SELECT mnc.userid
FROM  `messages` mnc
WHERE (select count(*) from messagesas m where m.userid = mnc.userid
        and m.message_sendtime_timestamp >= mnc.message_sendtime_timestamp 
    and m.opened = 'FALSE') >= 6

But, this give me users with more than 6 unopened messages not necessarily consecutive

Here are sample data

CREATE TABLE messages
    (`user_id` int, `timestamp` datetime, `opened` varchar(5))
;

INSERT INTO messages
    (`user_id`, `timestamp`, `opened`)
VALUES
    (1, '2016-01-01 00:00:00', 'false'),
    (1, '2016-02-01 00:00:00', 'false'),
    (1, '2016-03-01 00:00:00', 'false'),
    (1, '2016-04-01 00:00:00', 'false'),
    (1, '2016-05-01 00:00:00', 'false'),
    (1, '2016-06-01 00:00:00', 'false'),
    (2, '2016-01-01 00:00:00', 'false'),
    (2, '2016-02-01 00:00:00', 'false'),
    (2, '2016-03-01 00:00:00', 'false'),
    (3, '2015-01-01 00:00:00', 'false'),
    (3, '2016-01-01 00:00:00', 'false'),
    (3, '2016-02-01 00:00:00', 'false'),
    (3, '2016-03-01 00:00:00', 'false'),
    (3, '2016-04-01 00:00:00', 'false'),
    (3, '2016-05-01 00:00:00', 'true'),
    (3, '2016-06-01 00:00:00', 'false'),
    (4, '2015-01-01 00:00:00', 'true'),
    (4, '2015-02-01 00:00:00', 'true'),
    (4, '2016-01-01 00:00:00', 'false'),
    (4, '2016-02-01 00:00:00', 'false'),
    (4, '2016-03-01 00:00:00', 'false'),
    (4, '2016-04-01 00:00:00', 'false'),
    (4, '2016-05-01 00:00:00', 'false'),
    (4, '2016-06-01 00:00:00', 'false')

Expected result :

userid 
1 
4

Show us sample data and expected result. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, May 05 '16 at 15:45
Your title says you want to get the last 5 messages, the question says you want to get the users who didn't open any of the last 5 messages. Which is it? See http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group for how to do the first. You can then use that in a subquery to test whether any of them have been opened. — Barmar, May 05 '16 at 16:03
@Simon,plz change the title accordingly , if you really want the users number. — Sachin, May 05 '16 at 17:07
with your new edit. 4 can have the 5th row open or unopen. Not criterion to decide the order because has same timestamp. — Juan Carlos Oropeza, May 05 '16 at 21:05
@Simon , if the query in my answers is what you required , I can update my answer with more clarity . — Sachin, May 06 '16 at 05:33

Sachin · Answer 1 · 2016-05-06T05:38:52.747

0

SELECT 
MAX(CASE WHEN (t.ct = 5 and t.op=5) THEN t.user_id END) AS userid
FROM
(
SELECT
  user_id, 
  opened,timestamp ,
  @opened := opened,
  IF ( (@opened = 'false' && @prev = user_id) ,@o := @o + 1,@o := 1),
  IF(@opened='true',@o:=0,@o) op,

  IF (@prev = user_id ,@c := @c + 1,(@c := 1)) ct,
  @prev := user_id

FROM    (SELECT @prev := 0 ,@c := 1,@opened :='0',@o := 0) var,
messages
order by user_id asc,timestamp desc

) t
GROUP BY t.user_id

check herehttp://sqlfiddle.com/#!9/8447a3/1

edited May 06 '16 at 05:38

answered May 05 '16 at 16:09

Sachin

2,627
1
19
35

This, but for each originalid in table – Simon May 05 '16 at 16:35
I didn't get that part, what is the use of orginalid? – Sachin May 05 '16 at 16:37
do you mean the originalId as in the original placer of the message his id? a userId? – mitchken May 05 '16 at 16:39
@mitchken yes originalid is the userid linked to a messageid – Simon May 05 '16 at 16:49
Ok, my bad, originalid is not unique, its the userid so that might have been confusing – Simon May 05 '16 at 17:10

Juan Carlos Oropeza · Answer 2 · 2016-05-05T19:59:17.000

0

to answer this question

I want all users who did not open a message in last 5 messages received ?

First you need create a row_id for each user_id

SELECT @rowid := IF(@prev_value = user_id,  @rowid + 1,  1) as row_id,
       m.*
       @prev_value := user_id
FROM messages m,
     (SELECT @row_num := 1) x,
     (SELECT @prev_value := '') y
ORDER BY `timestamp` DESC

Then check how many open message you have on that subquery

SQL Fiddle Demo

SELECT user_id, COUNT(*), SUM(opened = 'false')
FROM (
       SELECT @rowid := IF(@prev_value = user_id,  @rowid + 1,  1) as row_id,
              m.*,
              @prev_value := user_id
       FROM messages m,
            (SELECT @row_num := 1) x,
            (SELECT @prev_value := '') y
       ORDER BY user_id, `timestamp` DESC
     ) T
WHERE row_id <= 5   -- only check last 5 or less messages
GROUP BY user_id
HAVING COUNT(*) = SUM(opened = 'false') -- Check all messages are NOT opened

edited May 05 '16 at 19:59

answered May 05 '16 at 19:30

Juan Carlos Oropeza

47,252
12
78
118

Looking great. I'm not sure about the NOT opened part. I can do that with my col with values VAR TRUE or FALSE? – Simon May 05 '16 at 19:39
what type is field OPENED? – Juan Carlos Oropeza May 05 '16 at 19:45
Its VAR. Should I use something else to improve the request? – Simon May 05 '16 at 19:48
And I only want to select people with more than 6 messages. Can it improve request also? – Simon May 05 '16 at 19:52
I update the answer with a working sample. id=1, 5 unoppened message, id= 2 3 unoppened messages, id=3, 6 messages but second one is opened so isnt selected – Juan Carlos Oropeza May 05 '16 at 20:00
with the current sample you could add another clausule to the `HAVING ... AND Count(*) = 5` if you dont want the case with less than 5. But if you want +6 is another story and have to describe your problem in a new question. – Juan Carlos Oropeza May 05 '16 at 20:03
thank you. I edited my original message with your data and my expected result. – Simon May 05 '16 at 20:53
By changing your question your make all the answer incorrect. That is why you should create a new question instead. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) You need to be clear on what you want, explain the sample and why produce the output. – Juan Carlos Oropeza May 05 '16 at 21:03

MySQL : SELECT all users with 5 unopened messages in there last 5 messages received in a messages table

2 Answers2