Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

9 rows affected

SELECT
i.campaign AS campaign,
i.expected_inbound_date AS expected_inbound_date,
i.expected_inbound_quantity AS expected_inbound_quantity,
i.received_inbound_quantity AS received_inbound_quantity,

(SELECT 
   GREATEST(
     LEAST(i.expected_inbound_quantity, 
          (SELECT 
           SUM(i3.received_inbound_quantity) 
           FROM inbound i3 
           WHERE i.campaign = i3.campaign)  -
           
            (
                SELECT 
                t1.cumulated_value AS cumulated_value 
                FROM
                
                   (SELECT
                    i2.campaign, 
                    i2.expected_inbound_date, 
                    i2.expected_inbound_quantity, 
                    i2.received_inbound_quantity,
                    SUM(i2.expected_inbound_quantity) OVER (PARTITION BY i2.campaign ORDER BY i2.expected_inbound_date, i2.expected_inbound_quantity, i2.received_inbound_quantity ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cumulated_value
                    FROM inbound i2
                    GROUP BY 1,2,3,4) t1
                    
                WHERE (t1.campaign, t1.expected_inbound_date, t1.expected_inbound_quantity, t1.received_inbound_quantity) = (i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity)
            )
            
        ),
        0
   )
) AS split

FROM inbound i
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4

campaign	expected_inbound_date	expected_inbound_quantity	received_inbound_quantity	split
C001	2022-05-03	200	0	200
C001	2022-05-03	400	0	400
C001	2022-05-03	500	0	100
C001	2022-05-03	800	0	0
C001	null	0	700	0
C002	2022-08-20	2800	0	2800
C002	2022-08-20	3000	0	1200
C002	2022-08-20	5000	0	0
C002	null	0	4000	0