count of last continuous inserted records based on date

I want to get the count of last continuous inserted records.

I have the table as

user_id | point | DateTime
1 | 10 | 18-08-2018 17:15
2 | 10 | 01-08-2018 17:15
1 | 10 | 21-08-2018 17:15
1 | 10 | 22-08-2018 17:15
2 | 10 | 26-08-2018 17:15
1 | 10 | 25-08-2018 17:15
2 | 10 | 27-08-2018 17:15
1 | 10 | 26-08-2018 17:15
1 | 10 | 27-08-2018 17:15


Now there are 6 records for user_id = 1

But I want to count for last continuous records of the previous date

So the expected output should be count

3

because

27-08-2018
26-08-2018
25-08-2018


above dates are consecutive and then there is a gap between 25-08-2018 to 22-08-2018

25-08-2018

22-08-2018

If another record is present for 24-08-2018 then output should be

24-08-2018

4

Here my SQLFiddle

2 Answers
2

first, you need to let DateTime column be datetime type

DateTime

datetime

It is a Gaps and Islands question, you can try to make row number by user_id and difference days number from each min(datetime) from each user_id.

user_id

min(datetime)

user_id

CREATE TABLE Table1
(`user_id` varchar(12), `point` varchar(9), `DateTime` datetime)
;

INSERT INTO Table1
(`user_id`, `point`, `DateTime`)
VALUES
('1', '10', '2018-08-18 17:15'),
('2', '10', '2018-08-01 17:15'),
('1', '10', '2018-08-21 17:15'),
('1', '10', '2018-08-22 17:15'),
('2', '10', '2018-08-26 17:15'),
('1', '10', '2018-08-25 17:15'),
('2', '10', '2018-08-27 17:15'),
('1', '10', '2018-08-26 17:15'),
('1', '10', '2018-08-27 17:15')

;


Query 1:

SELECT cnt
FROM (
SELECT rn,MAX(DateTime) mxDt,COUNT(*) cnt
FROM (
SELECT DateTime,datediff(DateTime,mDt) -(SELECT COUNT(*)
FROM Table1 tt
WHERE t2.DateTime >= tt.DateTime
and t2.user_id = tt.user_id
) rn
FROM (
SELECT user_id,MIN(DateTime) mDt
FROM Table1
GROUP BY user_id
) t1 INNER JOIN Table1 t2 on t1.user_id = t2.user_id
Where t1.user_id = 1 ##set your expect select user_id
) t1
GROUP BY rn
)t1
ORDER BY mxDt desc
LIMIT 1


Results:

| cnt |
|-----|
| 3 |


Consider the following:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,dt DATE NOT NULL
);

INSERT INTO my_table (dt) VALUES
('2018-08-18'),
('2018-08-01'),
('2018-08-21'),
('2018-08-22'),
('2018-08-26'),
('2018-08-25'),
('2018-08-27'),
('2018-08-26'),
('2018-08-27');

SELECT i
FROM
(
SELECT x.*
, CASE WHEN @prev = dt
THEN @i:=@i
WHEN @prev = dt-INTERVAL 1 DAY
THEN @i:=@i+1
ELSE @i:=1 END i
, @prev := dt prev
FROM my_table x
, (SELECT @prev:=null,@i:=0) vars
ORDER
BY dt
, id
) a
ORDER
BY dt DESC
, id DESC
LIMIT 1;

+------+
| i |
+------+
| 3 |
+------+


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.