clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1135710 fiddles created (16650 in the last week).

CREATE TABLE event ( event_id int PRIMARY KEY , event_type_id int NOT NULL , period tstzrange NOT NULL , quantity int NOT NULL ); -- improved test data INSERT INTO event (event_id, event_type_id, period, quantity) VALUES (1, 1, '[2016-01-06 09:00:00+00,2016-01-08 17:00:00+00]', 1) -- range with 3 rows (q=3) , (2, 1, '[2016-01-07 09:00:00+00,2016-01-07 11:00:00+00]', 1) , (3, 1, '[2016-01-07 13:00:00+00,2016-01-07 17:00:00+00]', 1) , (4, 2, '[2016-01-07 12:00:00+00,2016-01-07 17:00:00+00]', 5) -- differeent type , (5, 1, '[2016-01-09 09:00:00+00,2016-01-09 11:00:00+00]', 4) -- range with 1 row (q=4) , (6, 1, '[2016-01-09 13:00:00+00,2016-01-09 17:00:00+00]', 2) -- range with 2 rows (q=5) , (7, 1, '[2016-01-09 14:00:00+00,2016-01-09 18:00:00+00]', 3);
7 rows affected
 hidden batch(es)


-- subquery variant, best performance SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity FROM ( SELECT lower(period) AS p_start ,(sum(quantity) OVER w)::int AS running_sum , lead(lower(period), 1, 'infinity') OVER w > max(upper(period)) OVER w AS range_end FROM event WHERE event_type_id = 1 AND period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange WINDOW w AS (ORDER BY lower(period)) ) sub WHERE range_end ORDER BY 1 DESC LIMIT 1;
sum_quantity
5
 hidden batch(es)


-- same, slower and more verbose WITH cte1 AS ( SELECT quantity , lower(period) AS p_start , upper(period) AS p_end FROM event WHERE event_type_id = 1 AND period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange ) , cte2 AS ( SELECT (sum(quantity) OVER w)::int AS running_sum , lead(p_start, 1, 'infinity') OVER w -- next start .. > max(p_end) OVER w AS range_end -- .. after last end , p_start, p_end FROM cte1 WINDOW w AS (ORDER BY p_start) ) SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity -- subtract the previous sum to get the sum of this range , p_end::text FROM cte2 WHERE range_end -- only rows at the end of each range ORDER BY 1 DESC -- biggest sum first LIMIT 1; -- only return the winner
sum_quantity p_end
5 2016-01-09 18:00:00+00
 hidden batch(es)