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).

;WITH
    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 Value	Cut	Cost	Multiplier	Cut Value	Profit
168	Emerald	10	1.25	210	200
168	Oval	20	1.50	252	232
168	Pear	35	1.75	294	259
168	Plumbob	50	2.00	336	286
168	Marquis	75	2.30	386	311
168	Crystal Ball	100	2.60	436	336
168	Sculptor's Egg	175	3.00	504	329
168	Brilliant	250	3.50	588	338
168	Star Cut	400	4.00	672	272

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
ORDER BY cost ASC

Results:

Cut	Cost	Multiplier	Lower Bound	Upper Bound	Finished Lower	Finished Upper	Min Profit	Max Profit
Emerald	10	1.25	8	41	10	51	0	41
Oval	20	1.50	38	60	57	90	37	70
Pear	35	1.75	59	60	103	105	68	70
Plumbob	50	2.00	59	84	118	168	68	118
Marquis	75	2.30	84	84	193	193	118	118
Crystal Ball	100	2.60	84	167	218	434	118	334
Brilliant	250	3.50	166	300	581	1050	331	800
Star Cut	400	4.00	299	600	1196	2400	796	2000
Heart-Shaped	1000	5.00	600	699	3000	3495	2000	2495

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