Monday, September 24, 2012

Gem Cuts: Profitability

In my last post, I set up a table (@uncut_gem) that had the integers 1 to 5000 in it and another table (@gem) that has the types of cuts, cost, and multiplier value that is applied to the gem when it is cut. Then, I selected from the @gem table, using a simple algebraic formula to find the cutoff value where the value of the cut gem was at least the value of the cut plus the value of the original, uncut gem. Now I want to find out, not just where the value is where I won't lose money, but at what value I'll start making money.

To do this, I'm going to use another CTE. First, I'm going to get the cartesian product of gems x cuts, so 50,000 records, and calculate the profitability. Here's how I'm going to do that:
  • The cut value is the uncut value * the multiplier
  • The profit is the cut value - cost of the cut
I'm not going to factor in the cost of the uncut gem here because it doesn't change as the multiplier changes, so it won't affect the outcome (if I'm trying to figure out what to do with a gem with an uncut value of 200, if I choose the Brilliant cut, it would yield a cut gem with a value of 700, minus the 250 Simolean cost of the cut is a profit of 450 Simoleans; if I choose the Crystal Ball cut I get a gem with a value of 460 minus the 100 cost of the cut is a profit of 360. Subtracting the original 200 from each of them does not change their profitability relative to each other, and that's all I care about here).

    Profitability(cut, cost, multiplier, original_value, cut_value, profit)
      AS (SELECT  cut, cost, multiplier, u.uncut_value, (u.uncut_value * multiplier) , (u.uncut_value * multiplier) - cost
        FROM    @uncut_gem u
        JOIN     @gem
            ON u.uncut_value * multiplier>= cost
So, for instance, here is the spread for a gem with an uncut value of 168:
Uncut ValueCutCostMultiplierCut ValueProfit
168Crystal Ball1002.60436336
168Sculptor's Egg1753.00504329
168Star Cut4004.00672272

Continuing our query, we add another CTE. Now we group by the original value and find the maximum profit value for each original value. We'll be able to use this max profit to join back to the Profitability CTE and select just the Cuts with the highest profitability rating. If more than one cut has the same profitability for the same gem, this will return all cuts with that top profitability rating.

    BestCut(original_value, profit)
      AS (SELECT  original_value, MAX(profit)
        FROM    Profitability
        WHERE    original_value < 700
        GROUP BY  original_value
SELECT  cut, cost, multiplier,
  MIN(p.original_value) LowerBound, MAX(p.original_value) UpperBound,
    CAST(MIN(p.original_value) * multiplier AS INT) Finished_Lower,
    CAST(MAX(p.original_value) * multiplier AS INT) Finished_Upper,
    CAST(MIN(p.profit) AS INT) Min_Profit,
    CAST(MAX(p.profit) AS INT) Max_Profit
FROM    Profitability p
JOIN     BestCut bc
    ON p.original_value = bc.original_value
       AND p.profit = bc.profit
GROUP BY  cut, cost, multiplier
CutCostMultiplierLower BoundUpper BoundFinished LowerFinished UpperMin ProfitMax Profit
Crystal Ball1002.6084167218434118334
Star Cut4004.00299600119624007962000

As you can see, there is some overlap, especially in the lower value cuts. I'm capping the original value at 700 for this because at 700, the Heart Cut returns a gem valued at 3500 Simoleans, which creates some interesting possibilities using another aspect of the game.

Next: Getting Transfiguration Figures

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

Monday, July 30, 2012

Micro Mojo

Now Reading: How Microsoft Lost Its Mojo by Kurt Eichenwald.  There are some problems, as I see are noted in the comments, but I did like this quote very much:
More employees seeking management slots led to more managers, more managers led to more meetings, more meetings led to more memos, and more red tape led to less innovation. Everything, one executive said, advanced at a snail’s pace.

Monday, July 23, 2012

Bon Voyage

Hi and welcome to Structured Query Blog.  I'm Jennifer Kenney.  I've been writing code since I was in 5th grade, but for the last 10 years, I've been writing it mostly in SQL.  These days, I find that I think a lot more about how to get software delivered than I do about how to get it written, and this blog is going to reflect both those obsessions.