--source ../include/have_columnstore.inc
--disable_warnings
DROP DATABASE IF EXISTS `mcol_5651`;
--enable_warnings
CREATE DATABASE `mcol_5651`;
USE `mcol_5651`;

CREATE TABLE mcs (id INTEGER NOT NULL, type VARCHAR(10) NOT NULL, sn VARCHAR(30), descr VARCHAR(100), sdate DATETIME) ENGINE=Columnstore;

INSERT INTO mcs VALUES
    (1,'a','1',NULL,'2024-01-11 18:36:01'),
    (2,'a','1',NULL,'2024-01-11 18:36:03'),
    (3,'a','1',NULL,'2024-01-11 18:36:04'),
    (4,'a','1',NULL,'2024-01-11 18:36:06'),
    (5,'a','1',NULL,'2024-01-11 18:36:07'),
    (6,'a','1',NULL,'2024-01-12 13:04:15'),
    (7,'a','1',NULL,'2024-01-12 13:04:17'),
    (8,'a','1',NULL,'2024-01-12 13:04:18'),
    (9,'a','1',NULL,'2024-01-12 13:04:20'),
    (10,'a','1',NULL,'2024-01-11 18:35:58'),
    (11,'a','1',NULL,'2024-01-11 18:30:00'),
    (12,'a','1',NULL,'2024-01-11 18:30:00'),
    (13,'a','1',NULL,'2024-01-11 18:30:03'),
    (14,'a','1',NULL,'2024-01-11 18:30:03'),
    (15,'a','1','a','2024-01-11 18:30:02'),
    (16,'a','1',NULL,'2024-01-11 18:30:03'),
    (17,'a','1',NULL,'2024-01-11 18:30:03'),
    (18,'a','1',NULL,'2024-01-11 18:30:03'),
    (19,'a','1',NULL,'2024-01-12 18:53:02'),
    (20,'a','1',NULL,'2024-01-12 18:53:02'),
    (21,'a','1',NULL,'2024-01-12 19:27:47'),
    (22,'a','1',NULL,'2024-01-12 19:27:48'),
    (23,'a','1',NULL,'2024-01-13 01:47:26'),
    (24,'a','1',NULL,'2024-01-13 01:47:26'),
    (25,'a','1',NULL,'2024-01-13 01:47:26'),
    (26,'a','1',NULL,'2024-01-13 01:47:26'),
    (27,'a','1',NULL,'2024-01-13 01:47:26'),
    (28,'a','1',NULL,'2024-01-13 01:47:26');

SELECT s1.id, count(*)
FROM mcs AS s1
WHERE s1.type = 'a'
    AND s1.sdate BETWEEN '2026-01-05 16:21:00' - INTERVAL 24 MONTH AND '2026-01-05 16:21:00'
    AND EXISTS (
            SELECT s.SN, s.sdate
            FROM mcs AS s
            WHERE s.type = 'a'
                AND s.sdate BETWEEN '2026-01-05 16:21:00' - INTERVAL 24 MONTH AND '2026-01-05 16:21:00'
                AND s.descr = 'a'
                AND s.SN = s1.sn
                AND s1.sdate BETWEEN s.sdate - INTERVAL 10 HOUR AND s.sdate + INTERVAL 10 HOUR
    )
GROUP BY 1 ORDER BY 2,1;

# cleanup
DROP DATABASE `mcol_5651`;