By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SELL (ProductID int,Description varchar(10),Timestamp_int int, SellCount int,Timestamp_datetime datetime);
INSERT INTO SELL VALUES(1,'Coke',1676430060,1,'2023-02-15 03:01:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430120,2,'2023-02-15 03:02:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430180,0,'2023-02-15 03:03:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430240,2,'2023-02-15 03:04:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430300,5,'2023-02-15 03:05:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430360,4,'2023-02-15 03:06:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430420,3,'2023-02-15 03:07:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430480,1,'2023-02-15 03:08:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430540,1,'2023-02-15 03:09:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430600,1,'2023-02-15 03:10:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430660,0,'2023-02-15 03:11:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430720,0,'2023-02-15 03:12:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430780,0,'2023-02-15 03:13:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430840,7,'2023-02-15 03:14:00.000');
INSERT INTO SELL VALUES(1,'Coke',1676430900,1,'2023-02-15 03:15:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430060,1,'2023-02-15 03:01:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430120,3,'2023-02-15 03:02:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430180,5,'2023-02-15 03:03:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430240,2,'2023-02-15 03:04:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430300,1,'2023-02-15 03:05:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430360,1,'2023-02-15 03:06:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430420,1,'2023-02-15 03:07:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430480,2,'2023-02-15 03:08:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430540,2,'2023-02-15 03:09:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430600,2,'2023-02-15 03:10:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430660,6,'2023-02-15 03:11:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430720,5,'2023-02-15 03:12:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430780,3,'2023-02-15 03:13:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430840,1,'2023-02-15 03:14:00.000');
INSERT INTO SELL VALUES(2,'7 Up',1676430900,2,'2023-02-15 03:15:00.000');
with cte as (
select s.*, floor(timestampdiff(minute, (select min(s1.Timestamp_datetime)
from SELL s1 where s1.ProductID = s.ProductID), s.Timestamp_datetime)/5) r_id
from SELL s
)
select t.ProductID, c.Description, t.av, t.mt, t.mdt from (
select c.ProductID, c.r_id, sum(c.SellCount)/count(*) av, max(c.Timestamp_int) mt,
max(c.Timestamp_datetime) mdt
from cte c group by c.ProductID, c.r_id) t
join cte c on c.ProductID = t.ProductID and c.r_id = t.r_id and c.Timestamp_datetime = t.mdt
| ProductID | Description | av | mt | mdt |
|---|---|---|---|---|
| 1 | Coke | 2.0000 | 1676430300 | 2023-02-15 03:05:00 |
| 1 | Coke | 2.0000 | 1676430600 | 2023-02-15 03:10:00 |
| 1 | Coke | 1.6000 | 1676430900 | 2023-02-15 03:15:00 |
| 2 | 7 Up | 2.4000 | 1676430300 | 2023-02-15 03:05:00 |
| 2 | 7 Up | 1.6000 | 1676430600 | 2023-02-15 03:10:00 |
| 2 | 7 Up | 3.4000 | 1676430900 | 2023-02-15 03:15:00 |