Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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); > > <pre> > ✓ > 8 rows affected > </pre> <!-- --> > 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; > > <pre> > company | json_array | plain_array | status > :------- | :------------------------------- | :-------------------------- | :----- > American | [{&quot;U202&quot; : 200}, {&quot;I034&quot; : 300}] | {&quot;(U202,200)&quot;,&quot;(I034,300)&quot;} | Met > Coastal | [{&quot;A384&quot; : 500}] | {&quot;(A384,500)&quot;} | Met > Tester | [{&quot;P034&quot; : 300}] | {&quot;(P034,300)&quot;} | Failed > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=ffe45109446e83790c7489de512a4401)*
back to fiddle