0

Okay, I've searched and can't find an answer to what is probably simple, but eluding me.

I have a table of virtual currency transactions. Some of them are purchasing virtual items and some are purchasing virtual currency (via real-money transactions). I would like to monitor the in-game transactions a player makes after making a real-money transaction.

Simple schema:

  • userId
  • timestamp
  • transactionType (real-money or in-game)
  • itemId (of the in-game item they bought)
  • value (positive for real-money transactions that add to the user's balance, negative for in-game purchases)
  • newBalance (the user's new currency balance)

It's easy enough to tally the purchases made within, say 10 minutes of purchase using a self-join. What I'd like to do is give each user a balance based on the value of the currency they purchased, then tally the purchases they made using that currency until that balance runs out.

I've got to think there's an easy way to do this using user-defined variables, but it's escaping me.

Thanks in advance!

DesolationRobot
  • 1
  • If I understand correctly, you need a "running total" per user? – Barranka Sep 06 '13 at 17:35
  • maybe this can help you: http://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql – Barranka Sep 06 '13 at 17:36
  • What you want to do is implement [double entry bookkeeping](http://en.wikipedia.org/wiki/Debits_and_credits) to make a proper ledger. – tadman Sep 06 '13 at 17:40
  • Bararnka, that's halfway to where I need to be. Then I need to compare the running total to the balance I've saved off for that user for how much currency they purchased. – DesolationRobot Sep 06 '13 at 17:57

0 Answers0