How to loop an array and sum values until condition is met?

Question

I have a relational table where I need to loop through and sum amount values until a specified amount criteria is met. The data looks like this:

Company	Account	Amount
Coastal	A384	500
Coastal	C940	200
Coastal	Z934	200
American	U202	200
American	I034	300
Tester	P034	300

I want to have a function where I can loop through this data and specify an amount criteria that either returns 1 or more accounts once the threshold is met for each company. If the threshold is not met there should be some output that specifies the criteria was not met and the largest account value should be returned. I am thinking I would need to convert Account + Amount into an array to loop through. Pseudo code would look like:

var threshold = 500
var amount_total = 0
var array_result = {}
for each item in account_array
 array_result(account: amount)
 amount_total += amount
 if amount_total = threshold
 return array_result

My expected output from this would be:

Company	array_result	status
Coastal	{"A384": 500}	"Met Criteria"
American	{"U202": 200, "I034": 300}	"Met Criteria"
Tester	{"P034": 300}	"Did Not Meet Criteria"

Ideally, I would be able to have this as as a function to be able to use in a select statement:

Select Company, find_accounts_that_meet_threshold(account_array_field, threshold) from company_table

• Community: Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
• Erwin Brandstetter: Your version of Postgres? What defines sort order of rows? Get result for a given company or for whole table?

Answer 1

A set-based solution instead of looping:

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;

db<>fiddle here

I added two variants for your array to pick from.

In the subquery, form running sums per company with a window function. For lack of information, assuming an id column that defines sort order. Substitute with your actual order. Exclude the current row from the running sum, so we can conveniently filter rows WHERE running_sum >= 500 IS NOT TRUE in the outer query. That includes rows until the required sum is reached.
Also determine the row with biggest amount with row_number(). Using id to split ties if any. amount_rn = 1 is the winner - for the failed case.

The total sum indicates whether the criteria was met. If it was, return an array of contributing "account: amount". Else, just the one chosen by amount_rn.

• user234725: Nitpick: The specification states, "If the threshold is not met there should be some output that specifies the criteria was not met and the largest account value should be returned." Your query returns all the accounts for companies that don't meet the threshold. Add a couple of amount 50 accounts to Tester to see.
• Erwin Brandstetter: @dwhitemv: True. I adapted accordingly. In my defense: neither pseudocode nor test data nor desired result are clear about that. And the largest account value is ambiguous. Add a couple of amount 50 and remove the 300 from Tester, and you need a tiebreaker or an arbitrary pick ...
• user234725: Nicely done. I knew you were up for the challenge. :-)