CODEWITHSUNDEEP
mysql
Simon
Simon

Reputation: 44

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

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

Upvotes: 0

Views: 117

Answers (2)

Sachin
Sachin

Reputation: 2775

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

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 0

Related Questions