0

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
2
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Sep 22 '21 at 14:25
  • Your version of Postgres? What defines sort order of rows? Get result for a given company or for whole table? Sep 23 '21 at 4:40
2

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.

3
  • 1
    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.
    – dwhitemv
    Sep 23 '21 at 5:48
  • 1
    @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 ... Sep 23 '21 at 7:03
  • Nicely done. I knew you were up for the challenge. :-)
    – dwhitemv
    Sep 23 '21 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.