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; WITH cte AS ( 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 ), cte2 AS ( SELECT cte.deal_id, cte.status_id, cte.created_at, cte.first_created_at, cte.last_created_at, cte.prev_status_id, cte.next_status_id, calendar.date FROM cte JOIN calendar ON cte.created_at::date = calendar.date WHERE calendar.typeOfDay NOT IN ('hol') ) SELECT deal_id, status_id, MIN(created_at) AS start_time, MAX(created_at) AS end_time, SUM(DATE_PART('day', LEAST(created_at, last_created_at) - GREATEST(created_at, first_created_at)) * 24 + DATE_PART('hour', LEAST(created_at, last_created_at) - GREATEST(created_at, first_created_at)) + DATE_PART('minute', LEAST(created_at, last_created_at) - GREATEST(created_at, first_created_at)) / 60) AS actual_time_in_hours FROM cte2 GROUP BY deal_id, status_id;