I am working on a giveaway algorithm to select a customer based on what they purchased:
- If they purchase item 1, they gain 1 point.
- If they purchase item 2, they gain 2 points.
- If they purchase item 3, they gain 3 points.
Each purchase in the database has an
item_id, which I can use to figure out which item they have purchased.
I'm trying to make the minimum number of queries to the database to prevent memory usage from going up (e.g. I can't retrieve all records and calculate points all in memory), and I'm trying to put the minimum weight on MySQL. Here is what I've come up with:
Step 1: Get Total Points
I make three
count queries to the database, multiply the count by the score multiplier for each item, add the multiplied scores together, and put the results in an array. For example, if there are 10 purchases for item 1, 10 for item 2, and 10 for item 3, the
results array would look like this:
[ 1 => 10, 2 => 30, 3 => 60 ];
As you can probably tell, the first item is just itself (10), the second item is
(10 * 2) + 10 (
10 * 2 is the count of purchases multiplied by the score multiplier, and then added up to the previous item). Likewise, the third item in the array is
(10 * 3) + 30.
Step 2: Select a random number
At this stage, I simply choose a random number between 1 and 60 (1 and the value of the last item in the array).
Step 3: Retrieving the Winner
At this stage, I do almost the reverse of step 1. Imagine that the random number is 33. I find out what value is less than this random number (it is the item at index 2), then I deduct the value of that index from my random number (
33 - 30 = 3), then I divide the remainder by the score multiplier for
index + 1. In this case, the score multiplier for
2 + 1 (item number 3) is 3, so I divide 3 by 3, which gives me 1. Then I retrieve the very first purchase for item 3.
It took me a lot of time just to write this here. Now imagine someone being faced with a piece of code that adds and multiplies and subtracts and divides all the time! Is there a way to make this simpler?
I don't mind some performance loss if it gains me better readability; I prefer my code to be as readable as possible, rather than performing well but being hard to understand.