Sunday, September 23, 2012

Gem Cuts: Finding Complex Ranges

I play a time-devouring, absolutely maddening, terrifically addictive game called The Sims.  And by "play" I mean "alternate between an obsession where I can play the game for ten hours a day and wake up thinking about it, and going cold turkey."  And not only do I play the game, I write SQL queries to analyze aspects of the game and form strategies for it, because I am a nerd.

The Sims is a game where the player gets to create a "household" of up to eight members, called Sims.  These Sims eat and sleep and bathe and go to work and paint masterpieces and write novels and learn new skills and vacation, and basically have lives that are much more interesting than yours because they don't spend them staring at a computer screen playing games (actually, some of them do that too...)

One of the aspects of the game is that the sims collect precious gems which, in the Sim world, are just lying around on the ground pretty much all over the place (in Sim world the Star of Africa is just another diamond...)  The Sims can then cut these gems.  Each cut has a different cost and applies a different value multiplier to the base value of the gem:

Crystal Ball1002.60
Sculptor's Egg1753.00
Star Cut4004.00

The question then is: when the Sim finds a gem, how do we know what the most profitable cut would be? To find out, I started by creating a CTE to generate the integers 1-5000. I capped it at 5000 for two reasons: 1) I haven't seen any gems in the game with an uncut value higher than 5000 and 2) if there was one, I already know it would be worth it to use the highest value cut.

DECLARE @uncut_gem TABLE (uncut_value int)DECLARE @gem TABLE (cut VARCHAR(25), cost INT, multiplier DECIMAL (3,2))
;WITH  a(n) AS (SELECT 1 n UNION ALL SELECT 1),  --  2 
    b(n) AS (SELECT a.n FROM a JOIN a a1 ON 1 = 1),  --  4 
    c(n) AS (SELECT b.n FROM b JOIN b b1 ON 1 = 1),  --  16 
    d(n) AS (SELECT c.n FROM c JOIN c c1 ON 1 = 1)  --  256
        FROM d JOIN c ON 1 = 1 JOIN a ON 1=1

SELECT  'emerald', 10, 1.25
 UNION ALL SELECT  'oval', 20, 1.50
 UNION ALL SELECT  'Pear', 35, 1.75
 UNION ALL SELECT  'Plumbob', 50, 2.00
 UNION ALL SELECT  'Marquis', 75, 2.30
 UNION ALL SELECT  'Crystal Ball', 100, 2.60
 UNION ALL SELECT  'Sculptor''s Egg', 175, 3.00
 UNION ALL SELECT  'Brilliant', 250, 3.50
 UNION ALL SELECT  'Star Cut', 400, 4.00
 UNION ALL SELECT  'Heart-Shaped', 1000, 5.00

So now I have the base tables. Next, I'll run some numbers. First, I want to know where my break even points are. I want to know what value a gem has to be for the value of the cut gem to be at least the value of the cut plus the value of the original gemstone, so for instance, a plumbob cut costs 50 Simoleans (what other unit of currency would a Sim use?) and doubles the value of the gem. A rough gem with a 50 Simolean value would be worth 100 Simoleans with a plumbob cut, which would cover the cost of the cut plus the value of the original gem and leave a profit of 0. So I'm looking for (uncut * multiplier) - cost - uncut = 0, or to put it another way, uncut = cost/(multiplier - 1):

SELECT cut, cost, CAST(cost/(multiplier - 1) AS INT) Break_Even,
  CAST(cost * multiplier/(multiplier - 1)  AS INT) cut_value
FROM @gem

Here are the results:

CutCostUncut ValueCut Value
Crystal Ball10062162
Sculptor's Egg17587262
Star Cut400133533
Next: Maxing Profitability

No comments:

Post a Comment