SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE deals ( deal_id INT, status_id INT NOT NULL, created_at TIMESTAMP NOT NULL); INSERT INTO deals (deal_id, status_id, created_at) VALUES (1, 101, '2021-04-01 10:00:00'), (1, 102, '2021-04-02 11:30:00'), (1, 101, '2021-04-05 09:45:00'), (2, 103, '2021-04-01 14:00:00'), (2, 101, '2021-04-03 08:30:00'), (2, 104, '2021-04-05 12:00:00'), (3, 102, '2021-04-02 15:15:00'), (3, 101, '2021-04-05 10:30:00'), (4, 103, '2021-04-01 16:00:00'), (4, 104, '2021-04-04 13:00:00'); select deal_id, max(created_at) as last, min(created_at) as first, timestampdiff(hour, min(created_at), max(created_at)) from deals group by deal_id; CREATE TABLE calendar ( dt DATE, type char(5)); INSERT INTO calendar (dt, type) VALUES ('2021-04-01', 'work'), ('2021-04-02', 'work'), ('2021-04-03','hol'), ('2021-04-04','hol'), ('2021-04-05', 'work'), ('2021-04-06', 'work'), ('2021-04-07', 'work'), ('2021-04-08', 'work'), ('2021-04-09', 'work'); SELECT deal_id, created_at, ROW_NUMBER () OVER (PARTITION BY deal_id ORDER BY created_at) AS rn, ROW_NUMBER () OVER (PARTITION BY deal_id ORDER BY created_at DESC) AS rn_desc FROM deals;