How to sum effectively

Question

Before I start, I apologize for a bad title but I could not come up with one that describes my question in a satisfying way. If you come up with a better title, I will gladly switch.

Suppose I have an Account model and a Transaction model, and I would like to implement a Account#days_since_balance_was_atleast method, taking a sum as its sole parameter. What would be an effective way to do this?

Here comes some sample code:

class Transaction < ActiveRecord::Base
    validates_presence_of :account_id, :created_at, :amount
    belongs_to :account
    # Some logic to update account balance at creation...
end

class Account < ActiveRecord::Base
   validates_presence_of :balance 
   has_many :transactions

   def days_since_balance_was_atleast(sum)
       #How should I implement this?
   end
end

Should I use some smart SQL fragment, or perhaps ActiveRecord::Calculations? Loading all transactions and stepping backwards manually seems like a very bad idea (especially since there can be lots of transactions). And if I am stuck with the last approach, do you think it would be smart to retrieve the transactions in batches.

What it should produce (Updated):

# We have the following transactions 
# (time is unimportant for the result unless it is 00.00)
# Days ago  0   1   2   3   4  5   6   7
# Amount    -20 10 -60 -30 50 50 -100 100

account.balance == 0             # true
days_since_balance_was(100) == 3 # true

So, how would you solve this problem?

Answer 1

Daniel,

That is fairly tricky. Does the sum have to be dynamic (I'm assuming so). If not, you could capture the date that a threshold is hit and use that for some simple date logic.

Assuming you have to go dynamic though, I would load the entire transaction collection and iterate through it in reverse keeping track of the sum until you break your threshold. I'm more comfortable in a database world than I am in app development, and I'm not sure how you would solve that in the db layer without using syntax that is specific to a particular dbe - which means it's probably not something you would want to do via ActiveRecord.

Good luck - tough problem!

BB

Answer 2

Consider keeping the balance as a separate field. Then your problem shifts from a difficult summing problem to one of simply keeping the balance field consistent with the transactions

Answer 3

Shouldn't be that hard, using SQL join. Something along the lines of:

SELECT t1.time
FROM transactions t1
LEFT JOIN transactions t2 ON t2.time < t1.time
HAVING SUM(t2.amount) >= 100
GROUP BY t1.id, t1.time
ORDER BY t1.time
LIMIT 1

Either implement it using AR, or create a view in your database.

Answer 4

I managed to change Mladens code into this working snippet:

SELECT t1.time
FROM transactions t1
LEFT JOIN transactions t2 ON t2.time >= t1.time
WHERE t1.account_id=9 AND t2.account_id=9
GROUP BY t1.id, t1.time
HAVING SUM(-t2.amount) >= 100
ORDER BY t1.time DESC
LIMIT 1

I will accept Mladen's answer with a pointer to this one, as it was the one that led to the result, unless someone comes up with an even smarter method. I'm not sure whether this is the best method, but it avoids the heavy work of instantiating lots of ActiveRecord objects and lets the DB do the tough work.