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, max(created_at) as last, min(created_at) as first, timestampdiff(hour, min(created_at), max(created_at)) from deals group by deal_id; SELECT deal_id, status_id, created_at, LAG(status_id) OVER (PARTITION BY deal_id ORDER BY created_at) AS prev_status_id, LEAD(status_id) OVER (PARTITION BY deal_id ORDER BY created_at) AS next_status_id, FIRST_VALUE(created_at) OVER (PARTITION BY deal_id ORDER BY created_at) AS first_created_at, LAST_VALUE(created_at) OVER (PARTITION BY deal_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_created_at FROM deals WHERE status_id IS NOT NULL