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

No comments:

Post a Comment