clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601472 fiddles created (47972 in the last week).

CREATE TABLE tbl (id int, company text, account text, amount int); INSERT INTO tbl VALUES (1, 'Coastal', 'A384', 500), (2, 'Coastal', 'C940', 200), (3, 'Coastal', 'Z934', 200), (4, 'American', 'U202', 200), (5, 'American', 'I034', 300), (6, 'Tester', 'P034', 50), -- added some rows (7, 'Tester', 'P034', 300), (8, 'Tester', 'P034', 50);
8 rows affected
 hidden batch(es)


SELECT company , CASE WHEN sum(amount) >= 500 THEN json_agg(json_build_object(account, amount)) ELSE json_agg(json_build_object(account, amount)) FILTER (WHERE amount_rn = 1) END AS json_array , CASE WHEN sum(amount) >= 500 THEN array_agg((account, amount)) ELSE array_agg((account, amount)) FILTER (WHERE amount_rn = 1) END AS plain_array , CASE WHEN sum(amount) >= 500 THEN 'Met' ELSE 'Failed' END AS status FROM ( SELECT * , sum(amount) OVER (PARTITION BY company ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS running_sum , row_number() OVER (PARTITION BY company ORDER BY amount DESC NULLS LAST, id) AS amount_rn FROM tbl ORDER BY company, id ) sub WHERE running_sum >= 500 IS NOT TRUE GROUP BY 1;
company json_array plain_array status
American [{"U202" : 200}, {"I034" : 300}] {"(U202,200)","(I034,300)"} Met
Coastal [{"A384" : 500}] {"(A384,500)"} Met
Tester [{"P034" : 300}] {"(P034,300)"} Failed
 hidden batch(es)